SQL Server Profiler and Extended Events: Capturing Slow Queries in Production

Reading one query’s execution plan (previous lesson) assumes you already know which query is slow. In production, that’s rarely true — you need to first discover what’s actually running and how long it takes, across potentially thousands of different queries hitting the server every minute. Two tools do this: one classic, one modern.

Setting Up a Basic Profiler Trace

1. Open SSMS → Tools → SQL Server Profiler
2. Connect, choose the TSQL_Duration template (or build a custom trace with RPC:Completed and SQL:BatchCompleted events)
3. Add a column filter on Duration (e.g. > 500ms) to cut noise
4. Run your workload, stop the trace, sort by Duration descending

Profiler is genuinely the most approachable way to see this for the first time — a live, scrolling grid of every statement hitting the server, which query text, how long it took, who ran it. That approachability comes at a real cost, covered below.

The Modern Equivalent: Extended Events

CREATE EVENT SESSION SlowQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (sqlserver.sql_text, sqlserver.database_name)
    WHERE duration > 500000 -- microseconds = 500ms
)
ADD TARGET package0.event_file (SET filename = N'SlowQueries');
GO
ALTER EVENT SESSION SlowQueries ON SERVER STATE = START;
-- ... let it run, then:
ALTER EVENT SESSION SlowQueries ON SERVER STATE = STOP;

Notice the filter (WHERE duration > 500000) is applied at the engine level, before the event is even fully captured — this is the key architectural difference from Profiler, whose filtering happens client-side after every single event has already been generated and sent across.

Why This Difference Actually Matters

Profiler’s client-side filtering means the server does the full work of generating every event regardless of whether you’ll actually look at it — on a busy production server, running Profiler can itself become a measurable performance problem, sometimes ironically worse than the slow queries you’re trying to diagnose. Extended Events’ server-side filtering means events that don’t match never get fully materialized at all, which is why Microsoft has deprecated Profiler in favor of XEvents for exactly this reason, and why production DBAs default to XEvents almost universally today.