Long-Running Queries and Extended Events

This has been something I’ve wanted to investigate for a while now. I’ve know you could use Profiler and set up server-side traces to capture long-running events, but was curious how to do the same with Extended Events. I then came across this post from Pinal Dave ( b | t ) that pointed me in the right direction. I followed along with the guidelines he was suggesting and was having trouble finding the “Duration” filter. Turns out I had a bit too much selected in my filtering options or perhaps the Wizard was giving me fits seeing it, but I eventually selected just the Batch Completed or RPC Completed events to see and set the Duration filter. The one change that I’d make from Dave’s script is to set the duration to 500,000 because Duration in SQL 2012 is in microseconds, not milliseconds. I also want queries longer than 5 seconds to start.

I’m including my script below for tracking queries taking longer than 5 seconds.

CREATE EVENT SESSION [Long-Running Queries] ON SERVER
ADD EVENT sqlserver.rpc_completed (
ACTION ( sqlserver.client_app_name, sqlserver.client_hostname,
sqlserver.database_id, sqlserver.database_name, sqlserver.nt_username,
sqlserver.query_hash, sqlserver.server_principal_name,
sqlserver.session_id, sqlserver.sql_text )
WHERE ( ( ( package0.greater_than_uint64(sqlserver.database_id, ( 4 )) )
AND ( package0.equal_boolean(sqlserver.is_system, ( 0 )) ) )
AND ( duration >= ( 500000 ) )
) ),
ADD EVENT sqlserver.sql_batch_completed (SET collect_batch_text = ( 1 )
ACTION ( sqlserver.client_app_name, sqlserver.database_id,
sqlserver.query_hash, sqlserver.session_id )
WHERE ( ( ( package0.greater_than_uint64(sqlserver.database_id, ( 4 )) )
AND ( package0.equal_boolean(sqlserver.is_system, ( 0 )) ) )
AND ( duration >= ( 500000 ) )
) )
ADD TARGET package0.event_file
(SET filename = N'C:\MyFolder\Long-Running Queries'
, max_file_size = ( 100 ) ),
ADD TARGET package0.ring_buffer
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 = ON
, STARTUP_STATE = ON );
GO
ALTER EVENT SESSION [Long-Running Queries] ON SERVER STATE=START;
GO

 

No Responses

Leave a Reply

Your email address will not be published. Required fields are marked *