SQL Server Profiler: what it does, why it's deprecated, and what to use instead

SQL Server Profiler is the graphical tool, shipped with SSMS, that shows the statements a SQL Server receives as they arrive: which application sent them, how long they took, how many reads they did. For two decades it was how developers answered “what is my application actually sending to the database?”.

It still works, but it’s deprecated for the database engine: Microsoft has said it will be removed in a future version, and it gets no new features. It’s built on SQL Trace, an older tracing engine that is much heavier than its replacement, Extended Events. Running a Profiler trace against a busy production server can slow it down noticeably.

This article shows how to do what you used Profiler for with Extended Events, in T-SQL you can keep in a file, and where a different tool fits better.

What people use Profiler for

• Seeing the SQL an application sends (including what an ORM generates).
• Finding slow statements: everything over one second, with duration and reads.
• Catching errors and deadlocks as they happen.
• Measuring the cost of a stored procedure call.

The first three are a job for Extended Events. The last is better done differently, as we’ll see.

The Extended Events replacement for a Profiler trace

This session records every completed RPC call (how applications call procedures and parameterized queries) and every completed batch that took 100 ms or more, in one database, to files on the server:

CREATE EVENT SESSION SlowQueries ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.client_app_name, sqlserver.database_name, sqlserver.username, sqlserver.sql_text)
    WHERE duration >= 100000                      -- microseconds: 100 ms
      AND sqlserver.database_name = N'SpDevDemo'),
ADD EVENT sqlserver.sql_batch_completed (
    ACTION (sqlserver.client_app_name, sqlserver.database_name, sqlserver.username)
    WHERE duration >= 100000
      AND sqlserver.database_name = N'SpDevDemo')
ADD TARGET package0.event_file (SET filename = N'SlowQueries', max_file_size = 50, max_rollover_files = 4)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = OFF);

ALTER EVENT SESSION SlowQueries ON SERVER STATE = START;

The filter runs inside SQL Server, before an event is recorded, so the session costs almost nothing for the statements it ignores. That’s the main difference from Profiler, which ships everything to the client and filters there.

Run something slow enough to be captured:

SELECT COUNT(*)
FROM dbo.Orders AS a
CROSS JOIN (SELECT TOP (20) 1 AS x FROM dbo.Customers) AS b
WHERE a.Total > 100;
GO
WAITFOR DELAY '00:00:06';   -- give the session time to write its buffer to the file

Then read what was recorded, with a query:

SELECT
    x.e.value('@name', 'sysname')                                          AS event_name,
    x.e.value('@timestamp', 'datetime2')                                   AS utc_time,
    x.e.value('(data[@name="duration"]/value)[1]', 'bigint') / 1000        AS duration_ms,
    x.e.value('(data[@name="cpu_time"]/value)[1]', 'bigint') / 1000        AS cpu_ms,
    x.e.value('(data[@name="logical_reads"]/value)[1]', 'bigint')          AS logical_reads,
    x.e.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(200)') AS app,
    COALESCE(x.e.value('(data[@name="batch_text"]/value)[1]', 'nvarchar(max)'),
             x.e.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)')) AS sql_text
FROM sys.fn_xe_file_target_read_file(N'SlowQueries*.xel', NULL, NULL, NULL) AS f
CROSS APPLY (SELECT CAST(f.event_data AS xml)) AS d(event_xml)
CROSS APPLY d.event_xml.nodes('/event') AS x(e)
ORDER BY utc_time DESC;

event_name           duration_ms  cpu_ms  logical_reads  app
-------------------  -----------  ------  -------------  ----------------------------------------
sql_batch_completed  277          721     4236           Core Microsoft SqlClient Data Provider

cpu_ms is higher than duration_ms because the query ran in parallel. Stop and remove the session when you’re done:

ALTER EVENT SESSION SlowQueries ON SERVER STATE = STOP;
DROP EVENT SESSION SlowQueries ON SERVER;

Dropping the session doesn’t delete its .xel files; they stay in the server’s log folder until you remove them.

If you prefer a grid that updates live, like Profiler’s, SSMS has one: in Object Explorer, under Management → Extended Events → Sessions, right-click a running session and choose Watch Live Data. The XEvent Profiler node there gives you two ready-made sessions with a Profiler-like view.

Profiler events and their Extended Events names

Profiler event	Extended Events
RPC:Completed	sqlserver.rpc_completed
SQL:BatchCompleted	sqlserver.sql_batch_completed
SP:StmtCompleted	sqlserver.sp_statement_completed
SQL:StmtCompleted	sqlserver.sql_statement_completed
Exception / User Error Message	sqlserver.error_reported
Deadlock graph	sqlserver.xml_deadlock_report (already captured by the default system_health session)
Showplan XML Statistics Profile	sqlserver.query_post_execution_showplan (expensive: use with a tight filter)

Profiling one stored procedure

The one job neither Profiler nor Extended Events does well is “tell me where the time goes inside this procedure”. sp_statement_completed gives you one event per statement, which is close, but for a procedure with a loop you get thousands of rows to aggregate by hand, with no link back to your source lines. Capturing the plans of every statement with query_post_execution_showplan is heavy enough that Microsoft warns against it on production servers.

That is the job SpDev Pro was built for. It’s a line-level profiler for stored procedures in VS Code:

• It runs the procedure once on your development database and measures every statement: time, CPU, reads, writes, rows and executions, summed per line even inside loops.
• It shows the cost at the end of each line of your .sql file, and tints the lines by their share of the run.
• It captures the actual plan of the expensive statements and points out missing indexes, implicit conversions, parameter sniffing, spills and the other usual problems, on the line that causes them.
• It keeps every run and compares the next one with it, statement by statement.

It needs no server-side trace and no sysadmin rights: it reads its own session’s counters, and plan capture needs only the SHOWPLAN permission.

Which tool when

Question	Tool
What is my application sending to the database?	Extended Events, rpc_completed and sql_batch_completed
Which queries on the server are slow?	Query Store, or an Extended Events session with a duration filter
What deadlocked last night?	system_health session
Which line of this procedure is slow, and why?	A line-level profiler such as SpDev Pro, on a dev database
Is my fix faster?	SET STATISTICS IO ON, or SpDev’s before-and-after comparison