Sql 2012中利用擴充套件事件(Xevents)捕捉高消耗SQL查詢

生產環境中有時需要使用者抓取一些特定的語句分析,如超超長查詢,或高IO查詢等.一般來說大家對跟蹤比較熟悉,主要因為其有完善的UI支援.由於擴充套件事件(Xevents)在sql2012才提供UI支援,所以雖然在08時就已經存在的更輕量級的擴充套件事件沒有被廣泛使用.這裡和大家分享一個擴充套件事件指令碼,用來捕捉高消耗的查詢語句.

注意:使用完畢後應停止擴充套件事件捕捉.

應根據自身實際需求設定過濾條件,如CPU,duration,Reads等

在sql2012中會提供更為豐富的內容(如query_hash等)

code 1 將擴充套件事件追蹤儲存到本地檔案中

CREATE EVENT SESSION L_QUERIES ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.username,sqlserver.client_hostname,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.plan_handle)
    WHERE (cpu>1000 AND duration>=5000000 and Reads>=10000 and sqlserver.database_id=17)) 
ADD TARGET package0.asynchronous_file_target
   (SET FILENAME = N'D:\L_QUERIES.xel', METADATAFILE = N'D:\L_QUERIES.xem')
WITH (max_dispatch_latency = 1 seconds);
--cpu 1s
--duration 5s
--reads io 10000
--database_id 17 if you do not need it ,cut out
GO

ALTER EVENT SESSION L_QUERIES ON SERVER STATE = START;------start the event
GO 

SELECT
   data.value (
      '(/event[@name=''sql_statement_completed'']/@timestamp)[1]', 'DATETIME') AS [Time],
   data.value (
      '(/event/data[@name=''cpu'']/value)[1]', 'INT') AS [CPU (ms)],
   data.value (
      '(/event/data[@name=''reads'']/value)[1]', 'BIGINT') AS [Reads],   
   data.value (
      '(/event/action[@name=''session_id'']/value)[1]', 'smallint') AS [session_id],   
   data.value (
      '(/event/action[@name=''database_id'']/value)[1]', 'smallint') AS [database_id],
   CONVERT (FLOAT, data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT')) / 1000000
      AS [Duration (s)],
   data.value (
        '(/event/action[@name=''username'']/value)[1]', 'VARCHAR(100)') AS [username],
   data.value  (   
        '(/event/action[@name=''client_hostname'']/value)[1]', 'VARCHAR(100)') AS [client_hostname],
   data.value  (      
        '(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [SQL Statement],
   SUBSTRING (data.value ('(/event/action[@name=''plan_handle'']/value)[1]', 'VARCHAR(100)'), 15, 50)
      AS [Plan Handle]
FROM
   (SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file
      ('D:\L_QUERIES*.xel', 'D:\L_QUERIES*.xem', null, null)
) entries
ORDER BY [Time] DESC;----------query the event info what you need

--ALTER EVENT SESSION L_QUERIES ON SERVER STATE = STOP;---stop the event

--DROP EVENT SESSION L_QUERIES ON SERVER;------------drop the event

code2 將擴充套件事件儲存到ring buffer中

CREATE EVENT SESSION L_QUERIES ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.username,sqlserver.client_hostname,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.plan_handle)
    WHERE (cpu>1000 AND duration>=5000000 and Reads>=10000 and sqlserver.database_id=17)) 
    ADD TARGET package0.ring_buffer
        (SET max_memory = 4000) -----not the default 4096kb
   WITH (max_dispatch_latency = 1 seconds) 
--cpu 1s
--duration 5s
--reads io 10000
--database_id 17 if you do not need it ,cut out
GO

ALTER EVENT SESSION L_QUERIES ON SERVER STATE = START;------start the event
GO 

DECLARE @target_data XML
SELECT  @target_data=CAST(target_data AS XML)
FROM sys.dm_xe_sessions AS s 
JOIN sys.dm_xe_session_targets AS t 
    ON t.event_session_address = s.address
WHERE s.name = 'L_QUERIES'
  AND t.target_name = 'ring_buffer'

SELECT
   t.value('(@timestamp)[1]', 'datetime2') AS [time],
   t.value ('(data[@name=''cpu'']/value)[1]', 'INT') AS [CPU (ms)],
   t.value ('(data[@name=''reads'']/value)[1]', 'BIGINT') AS [Reads],   
   t.value ('(action[@name=''session_id'']/value)[1]', 'smallint') AS [session_id],   
   t.value ('(action[@name=''database_id'']/value)[1]', 'smallint') AS [database_id],
   CONVERT (FLOAT, t.value ('(data[@name=''duration'']/value)[1]', 'BIGINT')) / 1000000
      AS [Duration (s)],
   t.value ('(action[@name=''username'']/value)[1]', 'VARCHAR(100)') AS [username],
   t.value ('(action[@name=''client_hostname'']/value)[1]', 'VARCHAR(100)') AS [client_hostname],
   t.value ('(action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [SQL Statement],
   SUBSTRING (t.value ('(action[@name=''plan_handle'']/value)[1]', 'VARCHAR(100)'), 15, 50)
      AS [Plan Handle]
    FROM @target_data.nodes('RingBufferTarget/event') AS q(t)
ORDER BY [Time] DESC;----------query the event info what you need

--ALTER EVENT SESSION L_QUERIES ON SERVER STATE = STOP;---stop the event

--DROP EVENT SESSION L_QUERIES ON SERVER-----------drop the event