-- 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
'Extended Events' 카테고리의 다른 글
| deadlock capture of XEvent (0) | 2013.04.30 |
|---|---|
| XEvent - SQL Trace와 동일한 결과 얻기 (0) | 2013.03.19 |
| XEvent 정리 (0) | 2013.03.12 |
