ExtendedEvents-use system_health

Extendevents-use system_health to track session monitoring deadlocks by default. extendevents is provided after SQLServer2008 to track system analysis and locate problems. The default system_health session is always running, which helps you locate problems faster. Run the following script.

Extended SQL Server Events-use system_health to track session monitoring deadlocks by default. since SQL Server 2008 and later, Extended Events are provided to track system analysis and locate problems. The default system_health session is always running, which helps you locate problems faster. Run the following script.

Extended SQL Server Events-use system_health to track session monitoring deadlocks by default

Since SQL Server 2008, Extended Events are provided to track system analysis and locate problems. The default system_health session is always running, which helps you locate problems faster.

Run the following script to view the system_health extended event session:

SELECT * FROM sys.dm_xe_sessions

Even if you haven't started any extended event sessions, this query will return a line of system_health sessions.

SQL Server versions earlier than 2012 do not provide GUI for managing Extended event sessions, you can download SQL Server 2008 Extended Events SSMS Addin plug-in: http://extendedeventmanager.codeplex.com/

After installation, you can find the extended event management interface as follows:

650) this. width = 650; "title =" clip_image001 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image001 "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215594023-0.jpg "border =" 0 "height =" 458 "/>

650) this. width = 650; "title =" clip_image002 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image002 "src =" http://www.68idc.cn/help/uploads/allimg/151111/121559E28-1.jpg "border =" 0 "height =" 241 "/>

In SQL Server 2012, you can find this interface:

650) this. width = 650; "title =" clip_image003 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image003 "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215591X6-2.jpg "border =" 0 "height =" 421 "/>

Right-click "system_health" and generate the script. the session content is as follows (SQL Server 2012 ):

CREATE EVENT SESSION [system_health] ON SERVERADD EVENT sqlclr.clr_allocation_failure(ACTION(package0.callstack,sqlserver.session_id)),ADD EVENT sqlclr.clr_virtual_alloc_failure(ACTION(package0.callstack,sqlserver.session_id)),ADD EVENT sqlos.memory_broker_ring_buffer_recorded,ADD EVENT sqlos.memory_node_oom_ring_buffer_recorded(ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)),ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded,ADD EVENT sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded,ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded,ADD EVENT sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded,ADD EVENT sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded,ADD EVENT sqlos.scheduler_monitor_system_health_ring_buffer_recorded,ADD EVENT sqlos.wait_info(ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)WHERE ([duration]>(15000) AND ([wait_type]>(31) AND ([wait_type]>(47) AND [wait_type]<(54) OR [wait_type]<(38) OR [wait_type]>(63) AND [wait_type]<(70) OR [wait_type]>(96) AND [wait_type]<(100) OR [wait_type]=(107) OR [wait_type]=(113) OR [wait_type]>(174) AND [wait_type]<(179) OR [wait_type]=(186) OR [wait_type]=(207) OR [wait_type]=(269) OR [wait_type]=(283) OR [wait_type]=(284)) OR [duration]>(30000) AND [wait_type]<(22)))),ADD EVENT sqlos.wait_info_external(ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)WHERE ([duration]>(5000) AND ([wait_type]>(365) AND [wait_type]<(372) OR [wait_type]>(372) AND [wait_type]<(377) OR [wait_type]>(377) AND [wait_type]<(383) OR [wait_type]>(420) AND [wait_type]<(424) OR [wait_type]>(426) AND [wait_type]<(432) OR [wait_type]>(432) AND [wait_type]<(435) OR [duration]>(45000) AND ([wait_type]>(382) AND [wait_type]<(386) OR [wait_type]>(423) AND [wait_type]<(427) OR [wait_type]>(434) AND [wait_type]<(437) OR [wait_type]>(442) AND [wait_type]<(451) OR [wait_type]>(451) AND [wait_type]<(473) OR [wait_type]>(484) AND [wait_type]<(499) OR [wait_type]=(365) OR [wait_type]=(372) OR [wait_type]=(377) OR [wait_type]=(387) OR [wait_type]=(432) OR [wait_type]=(502))))),ADD EVENT sqlserver.connectivity_ring_buffer_recorded(SET collect_call_stack=(1)),ADD EVENT sqlserver.error_reported(ACTION(package0.callstack,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)WHERE ([severity]>=(20) OR ([error_number]=(17803) OR [error_number]=(701) OR [error_number]=(802) OR [error_number]=(8645) OR [error_number]=(8651) OR [error_number]=(8657) OR [error_number]=(8902)))),ADD EVENT sqlserver.security_error_ring_buffer_recorded(SET collect_call_stack=(1)),ADD EVENT sqlserver.sp_server_diagnostics_component_result(SET collect_data=(1)WHERE ([sqlserver].[is_system]=(1) AND [component]<>(4))),ADD EVENT sqlserver.xml_deadlock_reportADD TARGET package0.event_file(SET filename=N'system_health.xel',max_file_size=(5),max_rollover_files=(4)),ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)GO

You can also go to the SQL Server installation directory: C: \ Program Files \ Microsoft SQL Server \ MSSQL11. \ MSSQL \ Install

Find the script u_tables. SQL file.

From the definition, we can see that the session output includes callstack, sessionID, TSQL, and TSQL Call Stack.

When the security level is greater than 20 or the error code is 17803. They are related to memory pressure, Non-yielding schedding problems, deadlocks, and some types of waiting.

Session output is captured to ring_buffer following the FIFO rule. ring_buffer is a memory user who stores captured data in binary format. When an event session is enabled, data can be captured. When the session is stopped, the memory allocated to ring_buffer is released and the data disappears. Note: Before SQL Server 2012, system_health only targets ring_buffer. event_file output is added starting from SQL Server 2012.

You can view the contents of ring_buffer or event_file by associating sys. dm_xe_session_targets and sys. dm_xe_sessions views, and convert binary data to XML format.

SELECT name, target_name, CAST(target_data AS XML) target_dataFROM sys.dm_xe_sessions sINNER JOIN sys.dm_xe_session_targets tON s.address = t.event_session_addressWHERE s.name = 'system_health'GO

Note: the output of event_file is the file storage path, while the output of ring_buffer is the captured data.

In ring_buffer, each event element has a data subset and an action subset. These actions are defined in the session. The data element contains all values in the data type column of each event. These columns can be output in the sys. dm_xe_object_columns view. Let's parse the XML format and view the content in table format. Because each event returns a different set of data columns. The following is an example of an error_reported event.

DECLARE @x XML =(SELECT CAST(target_data AS XML)FROM sys.dm_xe_sessions sINNER JOIN sys.dm_xe_session_targets tON s.address = t.event_session_addressWHERE s.name = 'system_health' and t.target_name = 'ring_buffer')SELECT t.e.value('@name', 'varchar(50)') AS EventName,t.e.value('@timestamp', 'datetime') AS DateAndTime,t.e.value('(data[@name="error"]/value)[1]', 'int') AS ErrNo,t.e.value('(data[@name="severity"]/value)[1]', 'int') AS Severity,t.e.value('(data[@name="message"]/value)[1]', 'varchar(max)') AS ErrMsg,t.e.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_textFROM @x.nodes('//RingBufferTarget/event') AS t(e)WHERE t.e.value('@name', 'varchar(50)') = 'error_reported'

650) this. width = 650; "title =" clip_image004 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image004 "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215593200-3.jpg "border =" 0 "height =" 110 "/>

One of the most helpful uses for system_health is to track deadlocks. For the target ringbuffer, the amount of data stored depends on the capacity of the target on the monitored machine and the maximum number of settings generated. these are defined in each session. You can find the past deadlock records in the output of the system_health session.

All queries are output in system_health. you can run the following code to obtain a deadlock report.

-- SQL Server 2008 R2WITH SystemHealthAS (SELECT CAST(target_data as xml) AS TargetDataFROM sys.dm_xe_session_targets stJOIN sys.dm_xe_sessions sON s.address = st.event_session_addressWHERE name = 'system_health'AND st.target_name = 'ring_buffer')SELECT XEventData.XEvent.value('@timestamp','datetime')as Creation_Date,CAST(XEventData.XEvent.value('(data/value)[1]','VARCHAR(MAX)') AS XML) AS DeadLockGraphFROM SystemHealthCROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'ORDER BY Creation_Date DESC

650) this. width = 650; "title =" clip_image005 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image005 "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215593619-4.jpg "border =" 0 "height =" 220 "/>

 
  
   
  
  
   
    
    
     --Window 2USE AdventureWorks2012BEGIN TRANUPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 25WAITFOR DELAY '0:0:30'SELECT * FROM Person.Address WHERE AddressID = 20 
    
   
   
    
    
     --Window1USE AdventureWorks2012BEGIN TRANUPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 20WAITFOR DELAY '0:0:30'SELECT * FROM Person.Address WHERE AddressID = 25
    
   
  
  
   
    
     
    
    
     
    
   
   
    
     
    
    
     
    
   
  
 

View the inputbuf sub-element of process-list. the code snippet that causes the deadlock is displayed. process-list displays the process IDs of all deadlock participants. The process element includes the spid, Database id, login name, isolation level, and client application name. The Resource-list element contains resources in a deadlock. View the owner-list and waiter-list elements to see how the two processes are blocked.

Try to save the XML output as an XDL document and use SSMS to open the exception. There are currently two options to open the deadlock chart graphically: SQL Sentry Plan Explorer Pro and SQL Server 2012 Management Studio, see: https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/