April 24, 2011 at 10:31 pm
[font="Verdana"]I have a busy table with clustered index and at least 10 non-clustered indexes on it. It may contain round 1K rows at a time (assume a Work Flow Table).
Some services are pinging this table after specific 2 seconds interval and in the meantime some inserts and Status updates are also in progress... and consequently deadlocks...!
What generic steps should be adopted to troubleshoot.
Thanks!
[/font]
April 25, 2011 at 3:06 am
Generic steps: Tune the queries, tune the indexes.
That's about all the general advice there is. If you want something specific, switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here along with table and index definitions.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2011 at 3:23 am
GilaMonster (4/25/2011)
Generic steps: Tune the queries, tune the indexes.That's about all the general advice there is. If you want something specific, switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here along with table and index definitions.
DBCC TRACEON(1222,-1)
[font="Verdana"]
Great,
But whats the difference between 1204 and 1222.
Secondly, tweaks like introducing exception handling in inserts/updates, Locktime out, sp_getapplock or to obtain xlocks at first and then try for requested operation?
Sound as lot, but some trivial for you to answer!!!
[/font]
April 25, 2011 at 4:02 am
1204 is the older traceflag. Less information, harder to read.
You should always have exception handling, nothing new here. The app (or the stored proc) should be able to handle deadlocks and retry.
As for the rest, no. Those ignore the problem and hide it. The vast majority of deadlocks are a result of sub-optimal queries, insufficient (or too many) indexes or both.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2011 at 4:44 am
[font="Verdana"]Thank you Gila![/font]
April 25, 2011 at 5:08 am
In addition to everything else already mentioned, make sure you have good statistics maintenance running on the table. They will impact the plans chosen and because of that, the time the queries take. It's not the most important thing, but it should be a part of your overall tuning strategy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply