拡張イベント(Xevents)による高消費クエリーのキャプチャ
23912 ワード
生産環境において、使用者が超超長クエリや高IOクエリなどの特定の文の分析をつかむ必要がある場合がある.一般的には、UIのサポートが完備しているため、追跡に詳しい.拡張イベントはsql 2012においてUIのサポートを提供するため、08時に既に存在するより軽量レベルの拡張イベントは広く使用されていない.ここでは、高消費のクエリー文をキャプチャするための拡張イベントスクリプトを共有します.
注意:使用後は拡張イベントのキャプチャを停止する.
CPU、duration、Readsなど、実際のニーズに合わせてフィルタ条件を設定する必要があります.
sql 2012では、より豊富なコンテンツ(query_hashなど)が提供される.
code 1拡張イベントトラッキングをローカルファイルに保存
code 2拡張イベントをring bufferに保存
注意:使用後は拡張イベントのキャプチャを停止する.
CPU、duration、Readsなど、実際のニーズに合わせてフィルタ条件を設定する必要があります.
sql 2012では、より豊富なコンテンツ(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
code 2拡張イベントを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