"QQ Group" uses SQL Profiler for performance impact

Problem Description:

How to capture and record deadlocks, do you know how much SQL Profiler affects performance?

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;border-bottom:0px;border-left:0px; "alt = "image" src= "http://s3.51cto.com/wyfs02/M01/54/32/wKioL1R76eyhH2ThAADyU1q3jpE767.jpg" height= "border=" 0 "/ >

Solution:

We know that you can use the Profiler tool that comes with SQL Server to track the deadlock information. But this way there is a big one, that is, the consumption is very large. According to a major foreign God test, Profiler can even account for the total server bandwidth of 35%, so in a busy system, using Profiler is obviously not a good idea, below I introduce two ways to consume less. The second is the least expensive and can be used in the busiest system. The first is the most flexible and can meet a wide range of applications.

method One: Leverage SQL Server Agent (alert+job)

The steps are as follows:

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

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




Description
3605 outputs the results of the DBCC to the error log.
1204 returns the resource and type of the lock that participates in the deadlock, and the current command that is affected.
1222 returns the resources and types of locks that participate in deadlocks, and the affected current commands that use XML formats that do not conform to any XSD schema (more than 1204, SQL 2005 and more are available).
1 opens the specified trace flag in a global fashion.

The trace flag scope above is global, which will continue to work during SQL Server operation until SQL Server restarts.
If you want to ensure that SQL Server automatically turns on these flags after a restart, you can use the/T startup option in the SQL Server service startup option to specify that the trace flag is set to on during startup. (In SQL Server Configuration Manager->sql Server service->sql server-> properties, advanced startup parameters)
After running the above statement, when a deadlock occurs in SQL Server, you can already see it in the error log, but it is not intuitive (mixed with other information). (SQL Server log, management---SQL Server instance, SSMS)

2. Build the table and store the deadlock record

Use [Cole]--cole is my sample database, you can modify it according to the actual situation. GO CREATE TABLE deadlocklog (id int IDENTITY (1, 1) not NULL, Logdate DATETIME, ProcessInfo VARCHAR (Ten), ErrorText VAR CHAR (MAX)) GO

3. Create a job

Create a new job (assuming the name is Deadlockjob), create a new step in step, write a step name, and the database is "Cole" (see 2. Build table), enter the following statement in the command field:

--New 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 (ten), C varchar (MAX))--Insert the current log record into the temporary table insert into #ErrorLog EXEC Master.dbo.sp_readerr Orlog--Insert deadlock information into user table insert Deadlocklog Select a, B, C from #ErrorLog where ID >= (select MAX (ID) from #ErrorLog WH ERE c like '%deadlock encountered% ') DROP TABLE #ErrorLog

4. Create a new alert

In the new alert form, on the General tab, make the following settings:
Name: Can be according to the actual self-naming, here I use Deadlockalert
Type: Select "SQL Server performance condition alert"
Object: Sqlserver:locks
Counter: Number of Deadlocks/sec
Example: _total
The counter triggers an alert when the following conditions are met: above value: 0

After Setup is complete, it should look like the following:

650) this.width=650; "Title=" Click to view the original size picture "src=" Http://hi.csdn.net/attachment/201012/20/0_1292822406YK57.gif "/>

In the Response tab, select Execute Job and select the job we created in step 3 (that is, deadlockjob)
So far, we've done all the steps, so you can check the Deadlocklog table at any time to show the deadlock information.

method Two: Take advantage of server-side tracking.

The specific implementation steps are as follows:

1. Write the following script and execute

--  defining Parameters   declare  @rc  int  declare  @TraceID  int  declare   @maxfilesize  bigint  set  @maxfilesize  = 5  --  initialization tracking    exec  @rc  = sp_trace_create  @TraceID  output, 0, n ' e:\DbLog\ Deadlockdetect ',  @maxfilesize,  null   --here the E:\dblog\deadlockdetect is the file name (which can be modified by itself), SQL automatically adds the. trc extension   if  (@rc  != 0)  goto error --  set trace events    declare  @on  bit  set  @on  = 1  --148 in the following statement refers to Locks:deadlock  graph event (see sys.trace_events), 12 refers to 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 tracking   exec sp_ trace_setstatus  @TraceID, 1 --  record the Tracking ID for later use   select traceid = @ traceid  goto finish error:   select [email protected]  Finish:   go

Once the above statement is run, a record is automatically inserted into the file e:\DbLog\deadlockdetect.trc whenever a deadlock event occurs in SQL Server.

2. Pausing and stopping server-side tracking

To pause the server-side trace above, run the following statement:

EXEC sp_trace_setstatus 1, 0--The first parameter represents Traceid, which is the output parameter in step 1. The second parameter indicates that the state is changed to 0, which is a pause

To stop the above server-side trace, run the following statement:

EXEC sp_trace_setstatus 1, 2--the first parameter represents Traceid, which is the output parameter in step 1. The second parameter indicates that the state is changed to 2, that is, stop

3. View the contents of the trace file

There are two ways to view the trace file (E:\DBLOG\DEADLOCKDETECT.TRC) generated above:
1). Execute T-SQL command

SELECT * from fn_trace_gettable (' E:\DbLog\deadlockdetect.trc ', 1)

The TextData column in the result returns the details of the deadlock in the form of XML.

2). Open in SQL Server Profiler.
Go to Profiler, open trace file, select E:\DbLog\deadlockdetect.trc, and you can see the deadlock information in graphical form.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1584969

"QQ Group" uses SQL Profiler for performance impact