SQL Server Profiler traces deadlocks

An important application scenario for SQL Server Profiler is to monitor the analysis deadlock.

Here's an example to learn how profiler monitors deadlocks.

1. Create test data to simulate deadlocks, create tables table_a and table_b , and insert test data.

CREATE TABLEtable_a (IDint, Numint)CREATE TABLETable_b (IDint, Numint)INSERT  intoTable_aVALUES(1, -)INSERT  intoTable_aVALUES(2, -)INSERT  intoTable_bVALUES(3, at)INSERT  intoTable_bVALUES(4, +)

2. Open the SQL Server Profiler, create a new trace to monitor the deadlock, select the events to monitor in the event selection, run the trace:

3. Create a new two query window and follow the steps to execute the statement.

 --  query window 1   transaction  -- step 1  update  table_a set  num Span style= "color: #808080;" >=  55  where  ID =  1  -- step 1  select  *  from  table_b -- step 3  commit  transaction  

 

 --  query window 2   transaction  -- step 2  update  table_b set  num Span style= "color: #808080;" >=  66  where  ID =  4  -- step 2  select  *  from  table_a -- step 4  commit  transaction  

4. After you execute step 4, a deadlock occurs, but there is only one error message that is not too detailed.

5. Back to the profiler interface, select Deadlock graph, you can see more detailed deadlock reasons, the mouse on the ellipse can also display the deadlock statement, through which we can quickly and accurately find the cause of the deadlock.

SQL Server Profiler traces deadlocks