SQL Server Extended Events使用1： 查询运行时间久的SQL语句

SQL Server Extended Events是SQL Server 2008里新加的事件处理系统，用来取代SQL Server原先的SQL Trace的跟踪机制。事件处理系统对一个复杂服务器系统的排错，调试是极为关键的。和SQL Server原来的事件处理系统相比较，XEvent具有下列的优势：

• 消耗更少的系统资源，更适用于在产品服务器上的排错和调试。并且每收集一个系统事件所消耗的资源都是可预测的。
• 不仅仅能收集事件数据，还能收集在这事件触发点的系统动态运行信息，例如内存，T-SQL Stack等等。
• 可配置性，能够根据系统负载的需求配置所需收集的事件信息。

下面是使用SQL Server Extended Events追踪运行时间久的SQL：

 

--如果已经存在Event Session删除

IF EXISTS(SELECT*FROM sys.server_event_sessionsWHEREname='MonitorLongQuery')

DROP EVENT SESSIONMonitorLongQuery ON SERVER

GO

 

--创建Extended Event session

CREATE EVENT SESSIONMonitorLongQuery ON SERVER

--增加Event(SQL完成事件)

ADD EVENT sqlserver.sql_statement_completed

(

--指定收集的Event信息

ACTION

(

sqlserver.database_id,

sqlserver.session_id,

sqlserver.username,

sqlserver.client_hostname,

sqlserver.sql_text,

sqlserver.tsql_stack

)

--Filter信息（CPU超过或者整个运行时间超过）

WHERE sqlserver.sql_statement_completed.cpu> 1000

OR sqlserver.sql_statement_completed.duration> 10000

)

--指定收集的Event信息储存位置（可以存储到内存也可以到文件）

ADD TARGET package0.asynchronous_file_target

(

SET FILENAME = N'D:\MSSQL\LogQuery.xet',

METADATAFILE = 'D:\MSSQL\LongQuery.xem'

)

GO

 

--查询创建的Event详细信息包括事件，动作和目标(异步数据保存)

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

--启动Event Session捕获数据

ALTER EVENT SESSIONMonitorLongQuery

ON SERVER STATE = START

GO

 

--运行测试数据

SELECT * FROM AdventureWorks.Sales.SalesOrderHeaderH

INNER JOIN AdventureWorks.Sales.SalesOrderDetail DONH.SalesOrderID=D.SalesOrderID

GO

 

--下面的语句可以查询捕获的Event信息

SELECT CAST(event_dataASXML) event_data,*

FROM sys.fn_xe_file_target_read_file

('D:\MSSQL\LogQuery_0_129920634913770000.xet',

 'D:\MSSQL\LongQuery_0_129920634913780000.xem',NULL,NULL)

GO

 

可以看到：

 

--停掉Event Session

ALTER EVENT SESSIONMonitorLongQuery

ON SERVER STATE = STOP

GO

 

--删除Event Session

IF EXISTS(SELECT*FROM sys.server_event_sessionsWHEREname='MonitorLongQuery')

DROP EVENT SESSIONMonitorLongQuery ON SERVER

GO