Tuesday, January 13, 2015

Resolve frequent deadlocks and blocks in sql server if the problem is due to lock escalation

This is a long investigation ( though i mentioned it in a few lines )and should be done carefully.

you get very frequent deadlocks in your system (you would know by enabled 1204 or 1222 trace flags) or by adding 'deadlock related events' in your extended events or by tracking 'deadlock information' using 'typeperf' performance counters.
Also you see frequent blocks happening and most of these point to a few tables.

And if you think all these are happening because of lock escalation then:

1) trace 'lock escalation' performance counter to check how frequently lock escalation is happening, at the same time you can add 'lock escalation' related event (eventid=60) to your extended events and track the actual objects where lock escalation is happening

once you get the culprits, then its easy
if it is because of lock escalation then disable it
alter table <table name> set (lock_escalation=disable)
if you know the off/on peak loads for your application, then play with disable/enable depending on your performance