利用扩展事件(Xevents)捕捉高消耗查询

生产环境中有时需要使用者抓取一些特定的语句分析,如超超长查询,或高IO查询等.一般来说大家对跟踪比较熟悉,主要因为其有完善的UI支持.由于扩展事件在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