SQL Server Deadlock Graph from SQL Profiler

The first place to start troubleshooting deadlocks similar to the one above is to look at the execution plans for both of the contributing processes. In this case, the plan of interest is for the Selecter process as shown below:

However, before attempting to implement some sort of fix, try running each of the queries involved in the deadlock through SQL Server’s Database Engine Tuning Advisor, as the Tuning Advisor might recommend some new indexes for the queries. A lot of deadlocks can be prevented by adding indexes, as the indexes will make queries faster and reduce the amount of time a resource is locked.
To tune a query:
1.Open SQL Server Management Studio and paste one of the deadlocked queries into a new query window.
2.Analyze the query in Database Engine Tuning Advisor by right-clicking and selecting ‘Analyze Query in Database Engine Tuning Advisor’.
3.For the ‘Database for workload analysis’ field, select the database that you want to tune with, and select the same database in the ‘Select databases and tables to tune’ section.
4.Press the Start Analysis button in the top toolbar.
5.If the Tuning Advisor suggests any indexes, they will appear in the ‘Recommendations’ tab in the ‘Index Recommendations’ section. To create the indexes, you can either select Actions->Apply Recommendations… to create the indexes immediately, or you can select Actions->Save Recommendations (or hit Ctrl+S) to script the indexes to a file that you can run later.
- Full Post