SQL Server extended events use 1: Query SQL statements that have been running for a long time

SQL Server extended events is a newly added event processing system in SQL Server 2008. It is used to replace the original SQL trace tracking mechanism of SQL Server. Debugging is critical to troubleshooting a complex server system. Compared with the original event processing system of SQL Server, xevent has the following advantages:

• It consumes less system resources and is more suitable for troubleshooting and debugging on the product server. The resources consumed by collecting a system event are predictable.
• Not only can event data be collected, but also the system dynamic running information at the event triggering point, such as memory, T-SQL stack and so on.
• Configurability: configures the event information to be collected based on the requirements of the system load.

The following is an SQL statement that uses SQL Server extended events to track the running time:

 

-- Delete an event session if it already exists

If exists (select * from
SYS. server_event_sessionswherename = 'monitorlongquery ')

Drop event
Sessionmonitorlongquery on
Server

Go

 

-- Create extended Event session

Create event
Sessionmonitorlongquery on
Server

-- Add event (SQL completion event)

Add event sqlserver. SQL _statement_completed

(

-- Specify the event information collected

Action

(

Sqlserver. database_id,

Sqlserver. session_id,

Sqlserver. username,

Sqlserver. client_hostname,

Sqlserver. SQL _text,

Sqlserver. tsql_stack

)

-- Filter information (CPU exceeds or the entire running time exceeds)

Where sqlserver. SQL _statement_completed.cpu> 1000

Or sqlserver. SQL _statement_completed.duration & gt; 10000

)

-- Specify the storage location of collected event information (stored in memory or in files)

Add target package0.asynchronous _ file_target

(

Set filename
= N'd: \ MSSQL \ logquery. xet ',

Metadatafile = 'd: \ MSSQL \ longquery. xem'

)

Go

 

-- Query the detailed information of the created event, including the event, action, and target (asynchronous data storage)

Select sessions. nameas sessionname, sevents. packageaspackagename,

Sevents. Name as eventname,

Sevents. predicate, sactions. nameas actionname, stargets. nameas
Targetname

From SYS. server_event_sessionssessions

Inner join
SYS. server_event_session_eventssevents

On sessions. event_session_id = sevents. event_session_id

Inner join
SYS. server_event_session_actionssactions

On sessions. event_session_id = sactions. event_session_id

Inner join
SYS. server_event_session_targetsstargets

On sessions. event_session_id = stargets. event_session_id

Where sessions. Name = 'monitorlongquery'

 

Go

-- Start Event session to capture data

Alter event
Sessionmonitorlongquery

On Server
State = start

Go

 

-- Run test data

Select *
From adventureworks. Sales. salesorderheaderh

Inner join adventureworks. Sales. salesorderdetail donh. salesorderid = D. salesorderid

Go

 

-- The following statement can query the captured event information

Select cast (event_dataasxml) event_data ,*

From SYS. fn_xe_file_target_read_file

('D: \ MSSQL \ logquery_0_129920634913772.16.xet ',

'D: \ MSSQL \ longquery_0_12992063491378316.xem ', null, null)

Go

 

You can see:

 

-- Stop Event session

Alter event
Sessionmonitorlongquery

On Server
State = stop

Go

 

-- Delete Event session

If exists (select * from
SYS. server_event_sessionswherename = 'monitorlongquery ')

Drop event
Sessionmonitorlongquery on
Server

Go