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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
About Author
paschott
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others. I currently work for a Health-related non-profit based in Boise as a Database Architect.