Xevent Query Trace Filtering

-- Query.1 ) 쿼리창을 열어 아래의 쿼리를 실행 준비를 한다.

USE DBA

gO

   

SELECT *

FROM dbo.T_DiskInfo

go

SELECT *

FROM dbo.Hat

go

update Hat

set

Thing2 = Thing2 + 2

go

   

--DROP EVENT SESSION [Query_AUDIT] ON SERVER

--GO

   

-- Query.1 의 세션 ID를 확인해서 아래의 상수값 59를 세션 ID값으로 변경한다.

-- filename=N'D:\DB\XEVENT\Query_AUDIT.xel' 경로 변경

CREATE EVENT SESSION [Query_AUDIT] ON SERVER

ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1)

WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(59)) AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%SELECT%') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%Hat%'))),

ADD EVENT sqlserver.sql_statement_completed( ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)

WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(59)) AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%SELECT%') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%Hat%')))

ADD TARGET package0.event_file(SET filename=N'D:\DB\XEVENT\Query_AUDIT.xel')

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

   

-- Query 1번을 4~5회 수행

-- 대략 30초 쉬고 아래의 쿼리를 수행한다. 여기도 위의 경로로 변경 한다.

   

use master

go

   

SELECT

XEvent.value('(event/@name)[1]', 'varchar(50)') AS event_name,

DATEADD(hh,

DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),

XEvent.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],        

XEvent.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],

XEvent.value('(event/action[@name="database_name"]/value)[1]', 'varchar(50)') AS [database_name],

CASE

WHEN CHARINDEX ( 'SELECT', XEvent.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(1000)')) > 0 THEN 'SELECT'

ELSE 'ETC'

END AS [statement_type],                

XEvent.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(100)') AS [client_hostname],

XEvent.value('(event/action[@name="username"]/value)[1]', 'varchar(100)') AS [username],

XEvent.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(1000)') AS [sql_text],        

XEvent

FROM (

SELECT CAST(event_data AS XML) AS XEvent

FROM sys.fn_xe_file_target_read_file('D:\DB\XEVENT\Query_AUDIT*.xel', null, null, null)) as src

ORDER BY

DATEADD(hh,

DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),

XEvent.value('(event/@timestamp)[1]', 'datetime2')),

CAST(SUBSTRING(XEvent.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 1, 36) AS uniqueidentifier),

CAST(SUBSTRING(XEvent.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 38, 10) AS int)

GO