Friday, October 7, 2011

How to analyze and fix deadlocks in SQL Server?

1.Monitor and get detail on each deadlock
Analyzing Deadlocks with SQL Server Profiler
http://msdn.microsoft.com/en-us/library/ms188246.aspx


2. Wrap all all of insert/update/delete into transaction with rollback and high deadlock priority
SET DEADLOCK_PRIORITY HIGH;
http://msdn.microsoft.com/en-us/library/ms186736.aspx

SET XACT_ABORT ON;
http://msdn.microsoft.com/en-us/library/ms188792.aspx

3. Improve all of slow SQL query that got from xdl file