Deadlock troubleshooting in Sql Server 2008

Question

My website doesn't seem to handle a high number of visitors, I believe it's because the server is too simple.

2 hours ago my website was getting a lot of hits and I noticed that 3 deadlock errors occurred, the error is:

System.Data.SqlClient.SqlException : Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I'm not sure why this happened... Looking at the stack trace, I could see that this happened with a select query.

Anyone knows what may be the cause of this error?

The server is running Windows 2008 and Sql Server 2008.

Answer 1

SQL Server 2008 has multiple ways to identify processes and queries involved in deadlock.

1. If deadlocks are easy to reproduce,frequency is higher and you can profile SQL server (you have the access and performance cost on server when profiler is enabled) using SQL Profiler will give you nice graphical view of deadlock. This page has all the information you need to use deadlock graphs http://sqlmag.com/database-performance-tuning/gathering-deadlock-information-deadlock-graph

2. Most of the times reproducing deadlocks is hard, or they happen in production environment where we dont want to attach Profiler to it and affect performance.

I would use this query to get deadlocks happened:

SELECT
  xed.value('@timestamp', 'datetime') as Creation_Date,
  xed.query('.') AS Extend_Event
FROM
(
  SELECT CAST([target_data] AS XML) AS Target_Data
  FROM sys.dm_xe_session_targets AS xt
  INNER JOIN sys.dm_xe_sessions AS xs
  ON xs.address = xt.event_session_address
  WHERE xs.name = N'system_health'
  AND xt.target_name = N'ring_buffer'
) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY Creation_Date DESC

I would NOT go in the direction of using (NOLOCK) to fix deadlocks. That is slippery slope and hiding the original problem.

Answer 2

Writes will block reads on SQL Server, unless you have row versioning enabled. You should use the sp_who2 stored procedure and a SQL Profiler trace. sp_who2 will tell you which processes are blocking which, and the profiler will tell you what the last statement was for the blocking process.