Post (how to monitor SQL Server deadlocks)

Someone asked me how to capture and record the deadlock information on the Forum. Here, I will share some of my experiences with you, and I would like to ask you to correct them.

We know that we can use the profiler tool provided by SQL Server to track deadlock information. However, this method has a big flaw, that is, it consumes a lot. According to tests by some foreign experts, profiler can even account for 35% of the total server bandwidth. Therefore, using profiler in a busy system is obviously not a good idea, the following describes two methods that consume less. The second type consumes the least, which can also be used in the busiest system. The first type is the most flexible and can meet the needs of a variety of applications.

Method 1: Use the SQL Server proxy (Alert + job)

The procedure is as follows:

1. First, use the following command to enable the trace flag.

SQL code

DBCC TRACEON (3605,1204,1222,-1)  

Note:
3605 output the result of DBCC to the error log.
1204 return the resources and types of the locks involved in the deadlock and the affected current commands.
1222 return the resources and types of the locks involved in the deadlock, and the affected current commands using XML formats that do not conform to any XSD architecture (more advanced than 1204, SQL 2005 and above are available ).
-1 open the specified trace tag globally.

The scope of the above trace flag is global, that is, it will always play a role during SQL server running until SQL server restarts.

If you want to make sure that the tracing flag is automatically enabled after the SQL Server is restarted, you can use the/T startup option in the SQL Server service startup option to specify that the tracing flag is enabled during startup. (In SQL Server Configuration Manager> SQL Server service> SQL Server> Properties> advanced> startup parameters)

After running the preceding statement, you can see it in the error log when the SQL Server publishes a life-and-death lock, but it is not intuitive enough (mixed with other information ). (SSMs-> SQL server instance-> Management-> SQL server log)

2. Create a table to store deadlock records

SQL code

Use [Cole] -- Cole is my sample database. You can modify it as needed. Go create table deadlocklog (ID int identity (1, 1) not null, logdate datetime, processinfo varchar (10), errortext varchar (max) Go

 

3. Create a job

Create a new job (assuming the name is deadlockjob), create a new step in "Step", and enter a step name casually. The database is "Cole" (see Figure 2. in the "command" column, enter the following statement:

SQL code

-- Create a temporary table if object_id ('tempdb. DBO. # errorlog') is not null drop table # errorlog create table # errorlog (ID int identity (1, 1) not null, A datetime, B varchar (10 ), c varchar (max) -- inserts the current log into the temporary table insert into # errorlog exec master. DBO. sp_readerrorlog -- Insert the deadlock information into the user table insert deadlocklog select a, B, c from # errorlog where ID> = (select max (ID) from # errorlog where C like '% deadlock encountered %') Drop table # errorlog

4. Create an alarm

On the "General" tab of the "Create alarm" form, perform the following settings:

Name: you can name it by yourself. Here we use deadlockalert
Type: Select "SQL server performance condition alarm"
Object: sqlserver: locks
Counter: Number of deadlocks/sec
Instance: _ total
An alarm is triggered when the counter meets the following conditions: higher
Value: 0

After the configuration is complete, it should be shown in:

On the "response" tab, select "execute job" and select the new job (deadlockjob) in step 3)

So far, we have completed all the steps. Later, you can query the deadlocklog table at any time to display the deadlock information.

Method 2: Use server-side tracking.

The specific implementation steps are as follows:

1. Write the following script and execute

SQL code

-- Define the parameter declare @ RC int declare @ traceid int declare @ maxfilesize bigint set @ maxfilesize = 5 -- initialize the trace exec @ rc = sp_trace_create @ traceid output, 0, n'e: /dblog/deadlockdetect ', @ maxfilesize, null -- here E:/dblog/deadlockdetect is the file name (which can be modified by yourself). The SQL statement is automatically appended to it. TRC extension if (@ RC! = 0) goto error -- set the trail event declare @ on bit set @ on = 1 -- 148 in the following statement indicates the locks: deadlock graph event (see sys. trace_events), 12 indicates the spid column (see sys. trace_columns) exec sp_trace_setevent @ traceid, 148, 12, @ on exec sp_trace_setevent @ traceid, 148, 11, @ on exec sp_trace_setevent @ traceid, 148, 4, @ on exec sp_trace_setevent @ traceid, 148, 14, @ on exec sp_trace_setevent @ traceid, 148, 26, @ on exec sp_trace_setevent @ traceid, 148, 64, @ on exec sp_trace_setevent @ traceid, 148, 1, @ on -- start the trace exec sp_trace_setstatus @ traceid, 1 -- Record the trace ID for later use of select traceid = @ traceid goto finish error: Select errorcode = @ RC finish: Go

After the preceding statement is run, a record is automatically inserted into file E:/dblog/deadlockdetect. TRC every time a lifecycle lock event occurs in SQL Server.

2. Pause and stop server tracking

If you want to pause the above server Trace, you can run the following statement:

SQL code

Exec sp_trace_setstatus 1, 0 -- the first parameter indicates traceid, that is, the output parameter in step 1. The second parameter indicates changing the status to 0, that is, suspending

If you want to stop the above server trace, run the following statement:

SQL code

Exec sp_trace_setstatus 1, 2 -- the first parameter indicates traceid, that is, the output parameter in step 1. The second parameter indicates changing the status to 2, that is, stopping

3. view the tracking File Content

The preceding trace file (E:/dblog/deadlockdetect. TRC) can be viewed in two ways:

1). Execute the T-SQL command

SQL code

select * from fn_trace_gettable('e:/DbLog/deadlockdetect.trc',1)  

 
The textdata column in The result returns detailed information about the deadlock in XML format.

2) open it in SQL Server Profiler.

Choose profiler> open Tracing file> E:/dblog/deadlockdetect. TRC to view the deadlock information displayed in graphs.