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