Extended Event Data Query로 Select

아주 괜챃은 방법이다.

Extended Event Data 조회

http://www.sqldoubleg.com/2016/05/31/extended-events-file-target-reader/


CREATE EVENT SESSION [ALL_SQL_SAVE] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([object_name]<>N'sp_reset_connection')),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'F:\temp\ALL_QUERY.xel',max_file_size=(500),max_rollover_files=(30))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO



IF OBJECT_ID('tempdb..#ExEvent') IS NOT NULL DROP TABLE #ExEvent
SELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data
INTO #ExEvent
from sys.fn_xe_file_target_read_file ( 'f:\Temp\AAA.xel',null, null, null ) ;




(39266개 행이 영향을 받음)
SELECT ISNULL(t_action.RowId, t_data.RowId) AS RowId
, ISNULL(t_action.event_name, t_data.event_name) AS event_name
, t_action.[client_hostname], t_action.[collect_system_time], t_action.[client_app_name], t_action.[session_id]
, t_data.[cpu_time], t_data.[duration], t_data.[logical_reads], t_data.[object_name], t_data.[physical_reads], t_data.[result], t_data.[row_count], t_data.[statement], t_data.[writes]
FROM (
SELECT RowId, event_name, [client_hostname], [collect_system_time], [client_app_name], [session_id]
FROM (
SELECT RowId
, event_name
, T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')AS att_name
, T2.Loc.query('.').value('(/action/value)[1]', 'varchar(max)')AS att_value
FROM #ExEvent
CROSS APPLY event_data.nodes('/event/action') as T2(Loc)
WHERE T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')
IN ('client_hostname', 'collect_system_time', 'client_app_name', 'session_id')
) AS SourceTable
PIVOT(
MAX(att_value)
FOR att_name IN ([client_hostname], [collect_system_time], [client_app_name], [session_id])
) AS PivotTable
) AS t_action

-- Full outer because it might be no events selected only the payload
FULL OUTER JOIN (
SELECT RowId, event_name, [cpu_time], [duration], [logical_reads], [object_name], [physical_reads], [result], [row_count], [statement], [writes]
FROM (
SELECT RowId
, event_name
, T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)') AS att_name
, T3.Loc.query('.').value('(/data/value)[1]', 'varchar(max)') AS att_value

FROM #ExEvent
CROSS APPLY event_data.nodes('/event/data') as T3(Loc)
WHERE T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)')
IN ( 'cpu_time', 'duration', 'logical_reads', 'object_name', 'physical_reads', 'result', 'row_count', 'statement', 'writes')
) AS SourceTable
PIVOT (
MAX(att_value)
FOR att_name IN ([cpu_time], [duration], [logical_reads], [object_name], [physical_reads], [result], [row_count], [statement], [writes])
) AS PivotTable

) AS t_data
ON t_data.RowId = t_action.RowId