SQL Server › SQL Server Profiler & Extended Events

How do you create an Extended Events session to capture specific long running queries on a production server?

Intermediate

Answer

You define an event session specifying which event to capture, such as sql_statement_completed, add a predicate filtering for only the specific conditions you care about, like a minimum duration, choose a target such as an event file to store the captured data, and then start the session to begin capturing matching events going forward.

CREATE EVENT SESSION LongRunningQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(ACTION (sqlserver.sql_text, sqlserver.client_app_name)
WHERE duration > 5000000)
ADD TARGET package0.event_file (SET filename = 'LongQueries.xel')
WITH (STARTUP_STATE = ON);

ALTER EVENT SESSION LongRunningQueries ON SERVER STATE = START;

Real-world example A team troubleshooting intermittent slow performance sets up an Extended Events session capturing any query running longer than five seconds, along with the exact query text and originating application, running continuously in the background with minimal overhead.

Common follow-ups: What does the STARTUP_STATE option control?;How do you view the captured data once the session has been running for a while?

Query Optimization & Plans;Isolation & Locking