Express Edition是否支持扩展事件?如果是这样,它将以类似于Profiler的方式捕获语句并sp完成事件。
编辑:
我已将其更改为使用内存目标,而不是文件目标。理想情况下,取消注释这些
WHERe部分,并用适当的用户名替换以仅捕获感兴趣的事件,或者您可以使用spid进行筛选
WHERe(([sqlserver].[session_id]=(56))),例如。
IF EXISTS(SELECt * FROM sys.server_event_sessions WHERe name='test_trace') DROp EVENT SESSION [test_trace] ON SERVER;CREATE EVENT SESSION [test_trace]ON SERVERADD EVENT sqlserver.sp_statement_completed( ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text) -- WHERe (([sqlserver].[username]='DomainUsername')) ),ADD EVENT sqlserver.sql_statement_completed( ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text) --WHERe (([sqlserver].[username]='DomainUsername')) )ADD TARGET package0.ring_bufferWITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 1 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF)ALTER EVENT SESSION [test_trace] ON SERVER STATE = START
并查看结果(使用Adam
Machanic的XE代码生成器生成的查询)
DECLARE @session_name VARCHAr(200) = 'test_trace'SELECT pivoted_data.* FROM ( SELECt MIN(event_name) AS event_name, MIN(event_timestamp) AS event_timestamp, unique_event_id, ConVERT ( BIGINT, MIN ( CASE WHEN d_name = 'cpu' AND d_package IS NULL THEN d_value END ) ) AS [cpu], ConVERT ( BIGINT, MIN ( CASE WHEN d_name = 'duration' AND d_package IS NULL THEN d_value END ) ) AS [duration], ConVERT ( BIGINT, MIN ( CASE WHEN d_name = 'object_id' AND d_package IS NULL THEN d_value END ) ) AS [object_id], ConVERT ( INT, MIN ( CASE WHEN d_name = 'object_type' AND d_package IS NULL THEN d_value END ) ) AS [object_type], ConVERT ( DECIMAL(28,0), MIN ( CASE WHEN d_name = 'reads' AND d_package IS NULL THEN d_value END ) ) AS [reads], ConVERT ( VARCHAr(MAX), MIN ( CASE WHEN d_name = 'session_id' AND d_package IS NOT NULL THEN d_value END ) ) AS [session_id], ConVERT ( INT, MIN ( CASE WHEN d_name = 'source_database_id' AND d_package IS NULL THEN d_value END ) ) AS [source_database_id], CAST((SELECT ConVERT ( VARCHAr(MAX), MIN ( CASE WHEN d_name = 'sql_text' AND d_package IS NOT NULL THEN d_value END ) ) AS [processing-instruction(x)] FOR XML PATH('') ) AS XML) AS [sql_text], ConVERT ( DECIMAL(28,0), MIN ( CASE WHEN d_name = 'writes' AND d_package IS NULL THEN d_value END ) ) AS [writes] FROM ( SELECt *, ConVERT(VARCHAr(400), NULL) AS attach_activity_id FROM ( SELECt event.value('(@name)[1]', 'VARCHAr(400)') as event_name, event.value('(@timestamp)[1]', 'DATETIME') as event_timestamp, DENSE_RANK() OVER (ORDER BY event) AS unique_event_id, n.value('(@name)[1]', 'VARCHAr(400)') AS d_name, n.value('(@package)[1]', 'VARCHAr(400)') AS d_package, n.value('((value)[1]/text())[1]', 'VARCHAr(MAX)') AS d_value, n.value('((text)[1]/text())[1]', 'VARCHAr(MAX)') AS d_text FROM ( SELECt ( SELECT ConVERT(xml, target_data) FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERe s.name = @session_name AND st.target_name = 'ring_buffer' ) AS [x] FOR XML PATH(''), TYPE ) AS the_xml(x) CROSS APPLY x.nodes('//event') e (event) CROSS APPLY event.nodes('*') AS q (n) ) AS data_data ) AS activity_data GROUP BY unique_event_id ) AS pivoted_data;
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)