Real-time monitoring of deadlocks with extended events and Alerts-command mode

--This article has some mistakes, to be elegant, only for your reference One: The background of the experiment

In daily work, some deadlock phenomena often occur and affect system performance.

Therefore, we need a way to monitor the deadlock in real time, once there is a deadlock, email us, and then the relevant people to solve.

Extended event is a lightweight tuning tool that takes up less resources, is highly configurable, and has high scalability.

So we chose to monitor the deadlock with extended events, and then we have to notify us in real time via alerts.

Note: This experiment is done in its own local database instance. Two: Test steps 1: Building an event session

CREATE Eventsessionmonitoring_deadlock Onserver

ADD Eventsqlserver.xml_deadlock_report

(

ACTION (Sqlos.worker_address,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid, Sqlserver.database_id,sqlserver.database_name,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.session _nt_username,sqlserver.sql_text,sqlserver.username)

)

ADD Targetpackage0.ring_buffer

With (max_dispatch_latency= 30SECONDS);

--Start the event session

ALTER Eventsessionmonitoring_deadlockonserverstate=start;

Go

--Set ' Start event session on server startup '

(If the server reboots, the event session does not start by default, so the session target information cannot be viewed, so this setting is done).

Expand the ' Object Explorer '-> Management-> session-> Right-click the event session and click ' Properties ', as shown in figure:

Tick ' Start event session on server Startup ', as shown in figure:

--NOTE: If the server reboots, the deadlock information that happened before will not be seen.

--Delete Conversation

--drop EVENT session Monitoring_deadlock on SERVER 2: Building Alerts

In ' Object Explorer ', expand ' SQL Server Agent ', right-click ' Alert '-> ' new Alert ', as shown in figure:

2.1: Set the ' General ' tab

-When the number of deadlocks is greater than 0 (that is, a deadlock occurs), an alert is issued. 2.2: Set the ' Response ' tab

Select ' operator ' (if there is no ready-made ' operator ', then create a new one, here has been built an operator V-baidd),

2.3: Set the ' Options ' page

Customize information in ' additional notification information to send ', such as:

3: Set up SQL Server Agent

If the ' database configuration file ' for alert management has been set before, this step is ignored.

The first time you build an alert, you need to set up a SQL Server Agent.

Right-click ' SQL Server Agent '-' properties ',

At the ' Alerts ' tab, check ' Enable mail profile ', select the ' mail profile ' to use (Profile1 selected here)

Restart SQL Server Agent service 4: Simulate a deadlock

--Enter and execute in one of the windows:

BEGIN Tran--rollback

Use TEST;

UPDATE EMP2

SET ename= ' Jiaojiao '

WHERE empno=2

WAITFOR DELAY ' 0:0:25 ';

UPDATE EMP2

SET ename= ' Songsong '

WHERE empno=4

--select @ @SPID 57

--Enter and execute in another window:

BEGIN Tran--rollback

Use TEST;

UPDATE EMP2

SET ename= ' Songsong '

WHERE empno=4

WAITFOR DELAY ' 0:0:25 ';

UPDATE EMP2

SET ename= ' Jiaojiao '

WHERE empno=2

--select @ @SPID 60

Would have reported such a mistake:

(1 rows affected)

Message 1205, Level 13, State 51, line 8th

The transaction (process ID 60) and another process are deadlocked on the lock resource and have been selected as the deadlock victim. Please run the transaction again. 5: Receive alerts to send messages

As shown in figure:

6: Query event session target information 6.1 View XML format information

SELECT CAST (Target_dataasxml)

From SYS. Dm_xe_sessionsa

Innerjoin sys.dm_xe_session_targetssona.address=s.event_session_address

WHERE a.name= ' Monitoring_deadlock '

Click the results of the query, you can see the database, transactions, processes, sessions, SQL statements and other information.

The contents of the XML document are as follows:

<ringbuffertargettruncated= "0" processingtime= "0" totaleventsprocessed= "2" eventcount= "2" droppedCount= "0" memoryused= "16226" >

<eventname= "Xml_deadlock_report" package= "SQL Server" timestamp= "2014-05-24t04:09:47.636z" >

<dataname= "Xml_report" >

<typename= "xml" package= "Package0"/>

<value>

<deadlock>

<victim-list>

<victimprocessid= "process363404188"/>

</victim-list>

<process-list>

<processid= "process363404188" taskpriority= "0" logused= "144" waitresource= "key:7:72057594050510848" ( 9D6BF8154A2A) "Waittime=" 4064 "ownerid=" 1841133 "transactionname=" user_transaction "lasttranstarted=" 2014-05-24T12 : 09:18.567 "xdes=" 0x36a4dcd28 "lockmode=" X "schedulerid=" 4 "kpid=" 10220 "status=" suspended "spid=" "sbid=" 0 "ecid=" 0 "priority=" 0 "trancount=" 2 "lastbatchstarted=" 2014-05-24t12:09:18.567 "lastbatchcompleted=" 2014-05-24T12:0 9:13.383 "lastattention=" 1900-01-01t00:00:00.383 "clientapp=" Microsoft SQL servermanagement Studio-Query "Hostname=" ZB-BAIDD-PC "hostpid=" 4632 "loginname=" Sinooceanland\v-baidd "isolationlevel=" Read Committed (2) "Xactid=" 1841133 " currentdb= "7" locktimeout= "4294967295" clientoption1= "671090784" clientoption2= "390200" >

<executionStack>

<frameprocname= "Adhoc" line= "8" stmtstart= "sqlhandle=" 0x02000000d9418b10cbc871e6336b29994a63088a4ca33f5e0000000000000000000000000000000000000000 ">

UPDATE [EMP2] set [ename] = @1 WHERE [empno]=@2 </frame>

<frameprocname= "Adhoc" line= "8" stmtstart= "224" 0x02000000b8a0b81a9a3ca4a2a3b0ea4046ed4858dfe471100000000000000000000000000000000000000000 ">

UPDATE EMP2

SET ename= ' Jiaojiao '

WHERE empno=2

--select @ @SPID </frame>

</executionStack>

<inputbuf>

BEGIN TRAN--rollback

Use TEST;

UPDATE EMP2

SET ename= ' Songsong '

WHERE empno=4

WAITFOR DELAY ' 0:0:25 ';

UPDATE EMP2

SET ename= ' Jiaojiao '

WHERE empno=2

--select @ @SPID 60

</inputbuf>

</process>

<processid= "process37567e558" taskpriority= "0" logused= "332" waitresource= "key:7:72057594050510848" ( 1a39e6095155) "Waittime=" 5553 "ownerid=" 1841125 "transactionname=" user_transaction "lasttranstarted=" 2014-05-24T12 : 09:17.077 "xdes=" 0x366901048 "lockmode=" X "schedulerid=" 4 "kpid=" 11312 "status=" suspended "spid=" sbid= "0" ecid= "0 "priority=" 0 "trancount=" 2 "lastbatchstarted=" 2014-05-24t12:09:17.077 "lastbatchcompleted=" 2014-05-24T12:0 9:15.673 "lastattention=" 1900-01-01t00:00:00.673 "clientapp=" Microsoft SQL servermanagement Studio-Query "Hostname=" ZB-BAIDD-PC "hostpid=" 4632 "loginname=" Sinooceanland\v-baidd "isolationlevel=" Read Committed (2) "Xactid=" 1841125 " currentdb= "7" locktimeout= "4294967295" clientoption1= "671090784" clientoption2= "390200" >

<executionStack>

<frameprocname= "Adhoc" line= "8" stmtstart= "sqlhandle=" 0x02000000d9418b10cbc871e6336b29994a63088a4ca33f5e0000000000000000000000000000000000000000 ">

UPDATE [EMP2] set [ename] = @1 WHERE [empno]=@2 </frame>

<frameprocname= "Adhoc" line= "8" stmtstart= "254" 0x02000000f5add204ea99c109e6338086d7a147bf252cb5560000000000000000000000000000000000000000 ">

UPDATE EMP2

SET ename= ' Songsong '

WHERE empno=4

--select @ @SPID </frame>

</executionStack>

<inputbuf>

--Enter and execute in one of the windows:

BEGIN TRAN--rollback

Use TEST;

UPDATE EMP2

SET ename= ' Jiaojiao '

WHERE empno=2

WAITFOR DELAY ' 0:0:25 ';

UPDATE EMP2

SET ename= ' Songsong '

WHERE empno=4

--select @ @SPID 57

</inputbuf>

</process>

</process-list>

<resource-list>

            <keylockhobtid= "72057594050510848" dbid ="