When using the queries from the 2nd one, it works the way you described (deadlock with non-clustered index). No, I've used the queries from the first example. Profiler can be used to see the entire sequence of acquiring and releasing UPDATE locks during a scan while searching for the rows to update. I used sys.dm_tran_locks to look at the locks. There are other better solutions in the vast majority of cases. I don't recommend using them to solve problems. Please note that I only used an index hint in this test example. Then the nonclustered index seek is used without an index hint and there is no need to change which columns are used for the clustered index.Īlso, if col3 is updated in the 2nd example instead of col2, then the nonclustered index seek is automatically used with the 1000 row table because a table spool isn't needed. With a larger table this will not be the case. The table spool made the estimated cost of the plan using the nonclustered index seek more expensive than the clustered index scan plan. Then I saw that a table spool was needed when the nonclustered index seek was used because the column being updated is the same column used to select the row. UPDATE t SET col2=1 from dbo.TABLE1 t with(index(ix_table1)) where t.col2=1 I was wondering why the 2nd example wasn't using the nonclustered index. Thanks dear, you have save my job, i tried since 10 days, in my table there is insert and update from different 5 sql jobs, so index every time disturb, i remove cluster index with primary key and add primary key with non cluster index. Identify columns in your tables that are a good candidate for clustered index.Check out the SQL Server Tables without a Clustered Indextip for useful queries to diagnose the issue in your SQL Server databases. Ensure all your tables have a clustered index.Use query plans and profilerto provide more insight as to why you may be encountering deadlocks.Testing with your application/database should be done to ensure that you are getting the best performance possible. In my experience I have found columns that are heavily used in WHERE clauses are usually a good candidate, but there are many factors (table size, heavily skewed data, other indexes, etc.) that can affect the query plan and locks that the SQL Server optimizer decides to use during execution. That is why we saw a deadlock in the previous scenario.Ĭareful consideration needs to be taken when laying out the indexes on your SQL Server tables and even more attention placed when deciding on which column(s) the clustered index should be put on as this can have a dramatic impact on locking in your database. This means that no other processes can update any of these records until these shared locks are released. If we were to look at the query plan for the previous scenario we can see that in that case the optimizer chose to do an index scan hence it acquired shared locks on all the records it scanned. For the scenario with the clustered unique index we see the optimizer chose to do an index seek hence it only locked the records that it was updating allowing other processes to update other records in the table concurrently. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.In addition to using sp_lock and sp_who2 we can use the query plan for one of the statements from our test scenario, UPDATE dbo.TABLE1 SET col2=1 where col2=1, to highlight why we see the locking that we are seeing. I’m offering a 75% discount to my blog readers if you click from here. If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Hopefully if you’re hitting the same problems, you’ll find them useful. In practice, the clients I’ve had do this have had their Replication Deadlocks resolved. The official line from Microsoft Support is that you can usually fix the deadlocks by running these commands: EXEC = N'false' You may also see deadlocks on things like sp_replupdatechema coming from mssqlsystemresource. That’s the Id of the Resource Database, which you can’t really get at without the DAC, or copying and attaching the files for it as a user database. If you see deadlocks that involved Database Id 32767, and a negative object ID like -993696157, it’s going to be some weird replication stuff. You may also see weird looking ones like this on sp_addsubscription. You’ll see deadlocks on things like LockMatchID, sys.sp_MSrepl_changestatus, and sp_MSrepl_addsubscription. If you use Plan Explorer to look at deadlocks, which you should because SSMS sucks at it, you’ll see stuff that looks like this: i am deadlock I have had a few clients now that have run into problems with deadlocks arising from it doing Replication-y things. Replication is one of my least favorite things, and I go out of my way not to deal with it.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |