SQL Server Deadlock and Trace Flag 1204 and 1222- a big question every time
I have seen few users out there thinks that Deadlocks in SQL Server is a bug which has not be corrected by Microsoft since its release! Even few times they get think that deadlocks and blocking are similar reactions in SQL Server, a typical situation of deadlock will be like a circular blocking chain that involves 2 or multiple connections modifying the tables in different order and few times there will be 1 table involved. Within SQL Server engine when deadlock monitor thread detects blocking chain, it selects one of the participant as victim and cancels that SPID batch which involves a rollback of such transactions to make sure other connections (SPIDs) are allowed to continue.
As a DBA you must be aware of deadlocks scenario and anytime when I was involved on such situations the first action will be to check the SQL Server error log to ensure the information is published. SQL Server engine team has done tremendous job in introducing trace flag 1222 which is introduced in SQL Server 2005 version, you can enable the flag by uisng DBCC TRACEON (1222, -1) or even adding '-T1222' as a startup parameter for yoru SQL Server instance. The enhancement in this flag is a improved version of good old T1204 flag and best practices recommends that to use 1222 instead of 1204. But I have seen in some situations such as multi-instance installation of SQL Server 7 and/or 2000 then you have no other choice than using T1204 and to obtain further information alike T1222 then you could take help of SQL Profiler using 'Deadlock Graph' trace event.
Ok, now we have the process to obtain the information on Deadlocks and how to interpret such information to ensure that root cause of such problem can be resolved. I must recommend Bart Duncan's excellent job here on interpreting T1204 output and strongly refer every one to refer the link to understand the exact scenario that can enable you to get gist of the issue.
A typical deadlock error you will see is:
Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
So once I get to see the SQL error log output I will find out the query/stored procedure that is being executed and shown as victim in deadlock situation by opening up SQL Server Management Studio by changing the database context to relevant database, then right-click the relevant query text and select 'Analyze Query in DTA'. DTA - Database Tuning Advisor, you might be thinking why on earch you need a tool to get further in this critical situation. IN the past when I was involved with CSS I have been referred by esteemd Microsoft professional to ensure that relevant indexes are supported with that query or even use index hints to help the execution better, so don't skip this important task to identify further. As per the DTA tool configuration and analysis it may recommend indexes with a note of 'Estimated Improvement: <value>%'; then make sure to choose that recommendation by selecting 'Apply Recommendation' option on the drop-down menu that will help to create index immediately and monitor the query execution to see if deadlock persists. There is a note of recommendation here, for the first time or if you are unsure about that database significance in the Application then do not attempt this DTA step in production environment. Rather take a backup of existing database that is having deadlock issues and restore onto your like-to-like production environment for further testing. Just in case creating that index may bring down the SQL Server by grinding halt until it completes the creation, on top of existing connection & resource issue on that SQL instance, better be safe if you are unsure.
You may think size doesn't matter in such situation, even its a small database and so in that it is critical to the application high availability which can jeopardise the existence. To ensure you haven't done anything wrong it is better that you can do this with SQL Server tools without having the deep knowledge of a seasoned DBA. DTA (Index Tuning Wizard in SQL 2000 days) is a helpful tool that has saved my work when time is important and either blocking or simply performance was driving my instances down. The basic target of this tool is to recommend statistics and indexes that will improve the performance of a query you pass through it for starters. What more you want from a tool to understand the situation further and even you can use the tuning advisor for much more common use of creating indexes and statistics off queries prior to putting them in production.
Another wise plan is to ensure you tune each query that has been causing issues on the SQL instance and sometimes such blocking and deadlocking can lead to parallelism deadlock that can be identified by a CXPacket waittype that is listed in original deadlock output. Again Bart has done excellent job in explaining Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks" the scenario.
In such cases when you see a deadlock issue then I normally sense this is caused due to UPDATE LOCKS on the database which is by-design configuration which leads to bookmark or key lookups scenario. So you need to dig further to see any issues posed due to pagination. You can use DBCC SHOWCONTIG to see what kind of locking pointers are encountered. Further you can refer to Deadlock troubleshooting methods by Bart Duncan that I always follow in every instance of deadlocking resolution scenario.