asked
Table Performance Issue
Hi, I have a table on which DML operation like Insert/Update/Delete is happening. In the same time users are trying to fetch data from that table(using Select). But it leads to performance issue and it get hang. Note: • It hangs even though 'No Wait/No Lock' is used. • DML operations are continuous process and cannot be stooped and at the same it should be available for users Environment- SQL Server 2008 R2 Please advise. Anupam
Hi Sule, I have used No Wait in my code even it was hanging. I was aware of ReadPast but saw No Wait getting used more frequently use. Users always want to see the latest data. ReadPast may let them see old data. And more to that i have hunch that ReadPast may work for time being but will lead to some other trouble. Please share your idea. Anupam
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
You would need to test & confirm, but implementing one of the flavors of snapshot isolation might solve your issue. This would help prevent the writers from blocking the readers. Kendra Little has a good post about it:
http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/ Depending on your requirements & needs, you could also look at technologies such as replication, log shipping, or mirroring. All of them (within reason) offer up a readable secondary. Each has it's own gotchas so I would thoroughly test to ensure that they meet your needs. Mirroring:
https://msdn.microsoft.com/en-us/library/ms175511.aspx Replication:
https://msdn.microsoft.com/en-us/library/ms152531.aspx Log Shipping:
https://msdn.microsoft.com/en-us/library/ms187103.aspx Hope that helps!
Hi John, Thanks for sharing new Ideas. i have a feeling that i will run into trouble again even if i do some manipulation in query/Index or any change in the logic how the data is getting processed. It may stop happening as of now but may occur anytime in future again. Because at sometime Table will be locked during DML and there are chances that some Users also want to see some Data from it. I don't have much expertise in Mirroring/Replication/Log Shipping.But i am looking forward to it. There are some Replication environment where data get Replicate with delay of 5-10 seconds. Is that something i can opt for? I also need some dependency like When the Replication/Mirroring is happening my ETL should wait. Some kind of Indicator File or Flag to giving Go Ahead signal for Replication or Mirroring. How costly in terms of Hardware/Time these solution will be. can you tell me how much time it will take to replicate 10000 records. Will Replication or Mirroring of huge amount of data will block the table. Please suggest. Anupam
Before going done the path of mirroring/replication/log shipping (since those usually require additional hardware, I'd look at the snapshot isolation. Essentially, the version of the row will be stored within the version store in tempdb. When the read query needs to read the row, it will instead read the version of it from tempdb rather than the source database. By reading it from tempdb, the SELECT query won't block the DML statement. Keep in mind there will be some overhead from tempdb but in my experience that is usually minimal but your mileage may vary. Depending on which flavor you choose to use, this could be a simple switch with very little code modification.
In regards to your other questions: In regards to delaying replication, from what I know (never tried this myself) you can't delay individual transactions but you can delay the batch by adjusting the polling interval.
http://technet.microsoft.com/en-us/library/ms147328.aspx Regarding the dependency, those mechanisms happen behind the scenes for the most part. I'm not sure how you would setup a flag to notify the ETL process that a transaction is being replicated and/or mirrored. I'd have to research this one more. There is a hardware cost involved with these technologies. Usually the secondary is stored on another piece of hardware, however you can (not that I'd recommend this) utilize the existing hardware. I would very thoroughly test this before putting it into production as that will cause some additional overhead. The time to replicate 10K records will depend on several factors. Such as, how large are the records, how far do they need to replicate, speed of the network, speed of the disks, etc. There are a lot of factors to evaluate. I would suspect that it won't take long though. Again, testing would be critical.
Hi John, Snapshot Isolation- Currently i don't have much idea about it. I will look in to it and update you. As you have mentions that it will use tempdb. tempdb has been used extensively here. There is a entire Application which read data at different level. Many queries used #table or table variable @@table within it. Data load process on an average work on 1,00,000 records in one iteration. And that also uses tempdb. It will impact other process If Snapshot hold a big chunk of tempdb. Readpast table hint- this as well i feel may become victim of something as No Wait. For Replication anyhow i have set it up in the same instance because there are some other database query will be looking into. I know it is not a good idea but i have manage with this. Can you please help me in this and it will be really helpful if you can provide some help with my questions. Thanks, Anupam
Could do with seeing the execution plans of the statements in order to suggest something really. You could take a look at the indexing that's used for both the update and select statements and see if they can be tweaked/added/removed depending on how they are used. Incidentally, NOWAIT causes SQL to return an error to the application when a lock is present, it's equivalent to setting SET LOCK_TIMEOUT 0. [Pinal Dave - NOWAIT][1] I don't see how this will help with your locking issue. The best advice is to try to fix the code rather than using query hints, there are times where they can be used but only if you can fully validate that there isn't a better way first :) Do you have the query plans available to upload? [1]:
http://blog.sqlauthority.com/2013/01/25/sql-server-basic-explanation-of-query-hint-nowait-how-to-not-wait-on-locked-query/
Hi Mart, yes there are Index used while reading/Updating the data. All the index has been carefully designed after analysing the performance and Execution Plan. Altering any Index will have adverse impact to the Users. No Wait- Thanks for letting me know about this. Now i know why there was lock even after using No Wait. I have looked into the code. Let me know if you need any information specifically. Any help would me much appreciated. Anupam
The aim of either tuning the query, and/or supporting it with appropriate indexes, is to keep the time locks are held to a minimum. This is what you need to achieve in order to reduce the affect of locking and blocking. Without seeing the plans/queries it's a little tricky to offer much more I'm afraid anupamk although one thing you could look at is the frequency in which the query is executed, if you could run it slightly less frequently this could ease the contention too.
Hi Mart, There are 100 of tables for which data load is running. Sorry currently do not have the execution plan but i will try to provide one. Data load frequency is in every 30 minute once. Users does not want downtime of even for 10 seconds. There are more than hundred of users accessing it. Any downtime will be loss of work. Please let me know these are the answers you were looking for. Thanks, Anupam
18 People are following this question.
