What should I do if I find a dead end on SQL Server 2012? Is there a set of rules / steps I can follow to get out of it
I identified a couple of Update statements in a highly split table as Deadlock victims from extended events. How do I fix this?
There is no index on this table. We use UniqueIdentifier (RefGuid) in the offer section of both of these update statements. SQL server. The execution plan suggests creating a nonclustered index on this column. Will this prevent / eliminate deadlocks?
Thanks in advance.
Solution that made Deadlock easier in my case. Created a Non-clustered index on a column that has a where value. The column used in the Partition function was not used in the where where and therefore was not useful.
source to share
If deadlocks are just something that both update and select statements are displayed in the same table at the same time, you first need to focus on ensuring that both the update and select statements touch as many pages as possible. An easy way to get started is with "set statistics io on" and see the number of logical reads (or records, which are usually much less). The lower you get I / O, the less likely a deadlock will occur.
If there are multiple different columns in the where clause, you should try to focus on the columns (s) that have the highest selectivity, namely those that contain the fewest rows per value. It might be helpful to have more than one field in the index, but that of course depends on your data and SQL statements. Considering the actual execution plan is also a good idea, but remember that percentages are only estimates.
Since your table is partitioned, make sure to always include the key used for partitioning in the where clause whenever possible. As always, it is also important that the datatypes are matched (based on the "column" and "search" criteria). You can see the number of sections available in the actual plan (object properties).
If your process is more complex and there are many updates to different tables within transactions, if possible, try to always update tables in the same order.
source to share