SQL Profiler is deprecated. Use Extended Events instead

Summary

SQL Trace and SQL Server Profiler are deprecated. They still open, but Microsoft invests in Extended Events. Extended Events gives lower overhead, more coverage across the engine, richer filtering, and server-side capture that you can script and automate. For daily performance work, auditing, and triage, Extended Events is the right tool.

Bottom line: Move common Profiler traces to Extended Events sessions that write to event files. Read those files into views or a history table when you need reporting.

What is Extended Events

Extended Events is SQL Server’s event system. You subscribe to engine events, add useful context like client host or SQL text, filter them, and send them to a target such as a ring buffer in memory or an event file on disk.

Why it replaces Profiler

Lower overhead with server-side capture
More events across engine, HADR, memory, I/O, query processor, and security
Strong filtering and useful actions like client_hostname, client_app_name, client_ip, sql_text
Scripts that work in production without a desktop session
Durable files that survive restarts and can be archived

Targets: file vs ring buffer vs tables or views

File target (event_file)
Best for production. Durable and low overhead. You read .xel files with sys.fn_xe_file_target_read_file and can load to tables later.

Ring buffer (ring_buffer)
In memory only. Good for a quick spot check. Limited size. Empties on restart.

Tables or views
XE does not write straight to user tables. Use a simple pattern: capture to file, then read and insert into a table, or expose the files through a view.

Recommendation: Use file for anything longer than a short burst. Use ring buffer only for short, low-volume checks. Add a loader if you want history.

Capture options: file vs view-only vs table

1) Event file (recommended for production)

When to use: Most captures. Lowest overhead, durable across restarts, easy to archive and share.
How it works: XE writes .xel files. You read them with sys.fn_xe_file_target_read_file.

Pros

Safe and fast under load
Can roll over files to avoid disk bloat
Simple to move off the server

Cons

Requires a folder and NTFS write permission for the SQL Server service account

Create

DECLARE @xe_path nvarchar(260) = N'D:\XE\';

CREATE EVENT SESSION [Long_Running_Queries] ON SERVER
ADD EVENT sqlserver.rpc_completed(
  ACTION(sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.sql_text, sqlserver.database_id)
  WHERE (duration >= 5000000)  -- 5 sec
),
ADD EVENT sqlserver.sql_batch_completed(
  ACTION(sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.sql_text, sqlserver.database_id)
  WHERE (duration >= 5000000)
)
ADD TARGET package0.event_file(
  SET filename = @xe_path + N'LongRunning.xel',
      max_file_size = 500,
      max_rollover_files = 20
)
WITH (MAX_MEMORY = 64 MB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, STARTUP_STATE = OFF);

ALTER EVENT SESSION [Long_Running_Queries] ON SERVER STATE = START;

Read

SELECT *
FROM sys.fn_xe_file_target_read_file('D:\XE\LongRunning*.xel', NULL, NULL, NULL);

2) View-only (no persistence) via ring buffer

When to use: Quick spot checks during a short window.
How it works: Keep events in memory. Inspect them with a query. They vanish on restart or when the session stops.

Pros

No disk writes
Fast to set up for troubleshooting

Cons

Volatile and size limited
Easy to overwrite or lose data on restart

Create

CREATE EVENT SESSION [QuickCheck] ON SERVER
ADD EVENT sqlserver.attention(
  ACTION(sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.sql_text, sqlserver.session_id)
)
ADD TARGET package0.ring_buffer(SET max_events_limit = 10000, max_memory = 20);

ALTER EVENT SESSION [QuickCheck] ON SERVER STATE = START;

View results

SELECT CAST(t.target_data AS xml) AS event_xml
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t
ON s.address = t.event_session_address
WHERE s.name = ‘QuickCheck’ AND t.target_name = ‘ring_buffer’;

To make this friendlier, expose a view over the ring buffer:

CREATE OR ALTER VIEW dbo.XE_QuickCheck_View
AS
SELECT
  DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), SYSDATETIME()),
          x.value('(@timestamp)[1]','datetime2')) AS local_time,
  x.value('(data[@name="is_new_connection"]/value)[1]','bit') AS is_new_conn,
  x.value('(action[@name="client_app_name"]/value)[1]','nvarchar(256)') AS app,
  x.value('(action[@name="client_hostname"]/value)[1]','nvarchar(256)') AS host,
  x.value('(action[@name="sql_text"]/value)[1]','nvarchar(max)') AS sql_text
FROM (
  SELECT CAST(t.target_data AS xml) AS rb
  FROM sys.dm_xe_sessions s
  JOIN sys.dm_xe_session_targets t
    ON s.address = t.event_session_address
  WHERE s.name = 'QuickCheck' AND t.target_name = 'ring_buffer'
) d
CROSS APPLY rb.nodes('//RingBufferTarget/event') AS n(x);

3) Table (history you can query and join)

When to use: Trending, reporting, audits, and sharing results without opening .xel files.
How it works: Capture to file, then load into a user table on a schedule.

Pros

Queryable history in T-SQL
Join to app metadata and users
Easy to build dashboards

Cons

Slightly more work to maintain a loader job
You must manage table growth and retention

Create a history table

CREATE SCHEMA XE AUTHORIZATION dbo;
GO
CREATE TABLE XE.Events
(
  id            bigint IDENTITY(1,1) PRIMARY KEY,
  event_name    sysname NOT NULL,
  utc_time      datetime2(3) NOT NULL,
  local_time    datetime2(3) NOT NULL,
  client_app    nvarchar(256) NULL,
  client_host   nvarchar(256) NULL,
  sql_text      nvarchar(max) NULL,
  payload       xml NOT NULL
);

Reusable loader (example for long-running session)

;WITH src AS
(
  SELECT CAST(event_data AS xml) AS ed
  FROM sys.fn_xe_file_target_read_file('D:\XE\LongRunning*.xel', NULL, NULL, NULL)
)
INSERT XE.Events(event_name, utc_time, local_time, client_app, client_host, sql_text, payload)
SELECT
  ed.value('(/event/@name)[1]','sysname'),
  ed.value('(/event/@timestamp)[1]','datetime2(3)'),
  DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), SYSDATETIME()),
          ed.value('(/event/@timestamp)[1]','datetime2(3)')),
  ed.value('(/event/action[@name="client_app_name"]/value)[1]','nvarchar(256)'),
  ed.value('(/event/action[@name="client_hostname"]/value)[1]','nvarchar(256)'),
  ed.value('(/event/action[@name="sql_text"]/value)[1]','nvarchar(max)'),
  ed
FROM src;

Create a SQL Agent job that runs the loader every hour, then moves processed files to an Archive folder and deletes files older than your retention window.

Helpful view over all event files without loading

CREATE OR ALTER VIEW dbo.XE_AllFiles_View
AS
SELECT
object_name AS event_name,
CONVERT(datetime2, ed.value(‘(@timestamp)[1]’,’datetime2′)) AS utc_time,
DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), SYSDATETIME()),
CONVERT(datetime2, ed.value(‘(@timestamp)[1]’,’datetime2′))) AS local_time,
ed.value(‘(action[@name=”client_app_name”]/value)[1]’,’nvarchar(256)’) AS client_app,
ed.value(‘(action[@name=”client_hostname”]/value)[1]’,’nvarchar(256)’) AS client_host,
ed.value(‘(action[@name=”sql_text”]/value)[1]’,’nvarchar(max)’) AS sql_text,
ed AS event_xml
FROM (
SELECT object_name, CAST(event_data AS xml) AS ed
FROM sys.fn_xe_file_target_read_file(‘D:\XE*.xel’, NULL, NULL, NULL)
) f;

Which one should you choose

Live triage under load: Event file. It is durable and low overhead.
Quick peek during a call: Ring buffer with a view. No disk writes.
Reporting and audits: Event file plus loader to table. Then build views and dashboards.

Permissions reminder

Create and run sessions: ALTER ANY EVENT SESSION or CONTROL SERVER or sysadmin
Read metadata and targets: VIEW SERVER STATE
Write to file: SQL Server service account needs Modify on the XE folder (for example D:\XE)
Reading .xel from SSMS on a remote share also requires your Windows account to have Read on that share

Required permissions and file folder setup

Permissions

Create or change sessions: ALTER ANY EVENT SESSION or CONTROL SERVER or sysadmin
Read XE metadata and targets: VIEW SERVER STATE
File target: the SQL Server service account must have Write permission on the folder

Check the service account:

SELECT servicename, service_account
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%';

Create a folder for XE files
Create a local folder on each host such as D:\XE and grant Modify to the SQL Server service account. Give your DBA group Read if needed.

A safe session template

Use a consistent pattern with file rollover so disks do not fill up.

DECLARE @xe_path nvarchar(260) = N’D:\XE\’;

CREATE EVENT SESSION [Template] ON SERVER
ADD EVENT sqlserver.rpc_completed
ADD TARGET package0.event_file(
SET filename = @xe_path + N’Template.xel’,
max_file_size = 500,
max_rollover_files = 10
)
WITH (
MAX_MEMORY = 64 MB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF
);

Start or stop:

ALTER EVENT SESSION [Template] ON SERVER STATE = START;
ALTER EVENT SESSION [Template] ON SERVER STATE = STOP;
DROP EVENT SESSION [Template] ON SERVER;

Five useful Extended Events sessions for DBAs

1) Identify noisy or broken clients

Catches protocol errors and login churn with client details.

DECLARE @xe_path nvarchar(260) = N’D:\XE\’;

CREATE EVENT SESSION [NoisyClients] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.client_ip)
WHERE (error_number IN (17836)) — packet length mismatch
),
ADD EVENT sqlserver.login(
ACTION(sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.client_ip)
),
ADD EVENT sqlserver.logout(
ACTION(sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.client_ip)
)
ADD TARGET package0.event_file(
SET filename = @xe_path + N’NoisyClients.xel’,
max_file_size = 200,
max_rollover_files = 20
)
WITH (MAX_MEMORY = 32 MB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, STARTUP_STATE = OFF);

ALTER EVENT SESSION [NoisyClients] ON SERVER STATE = START;

Use it for old drivers, TLS issues, app servers that spam connect and disconnect.

2) Long-running queries

Replaces Profiler’s “Duration over N seconds” trace. Duration is microseconds.

DECLARE @xe_path nvarchar(260) = N’D:\XE\’;

CREATE EVENT SESSION [Long_Running_Queries] ON SERVER
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.sql_text, sqlserver.database_id)
WHERE (duration >= 5000000) — 5 seconds
),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.sql_text, sqlserver.database_id)
WHERE (duration >= 5000000)
)
ADD TARGET package0.event_file(
SET filename = @xe_path + N’LongRunning.xel’,
max_file_size = 500,
max_rollover_files = 20
)
WITH (MAX_MEMORY = 64 MB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, STARTUP_STATE = OFF);

ALTER EVENT SESSION [Long_Running_Queries] ON SERVER STATE = START;

Triage tip: Project cpu_time, logical_reads, writes. Tackle the highest CPU or reads first.

3) Query timeouts and cancels

Captures client timeouts as attention.

DECLARE @xe_path nvarchar(260) = N'D:\XE\';

CREATE EVENT SESSION [Query_Attention] ON SERVER
ADD EVENT sqlserver.attention(
  ACTION(sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.sql_text, sqlserver.session_id)
)
ADD TARGET package0.event_file(
  SET filename = @xe_path + N'QueryAttention.xel',
      max_file_size = 200,
      max_rollover_files = 20
);

ALTER EVENT SESSION [Query_Attention] ON SERVER STATE = START;

Use it for app timeouts, connection pool throttling, or blocking that trips client limits.

4) Deadlocks with full graphs

DECLARE @xe_path nvarchar(260) = N'D:\XE\';

CREATE EVENT SESSION [Deadlocks] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(
  SET filename = @xe_path + N'Deadlocks.xel',
      max_file_size = 200,
      max_rollover_files = 20
);

ALTER EVENT SESSION [Deadlocks] ON SERVER STATE = START;

Open the .xel in SSMS to view the XML graph and find the exact statements and resources.

5) Waits sampler

Waits show where time is spent. Filter out very short waits to reduce noise.

DECLARE @xe_path nvarchar(260) = N'D:\XE\';

CREATE EVENT SESSION [WaitsSampler] ON SERVER
ADD EVENT sqlserver.wait_completed(
  ACTION(sqlserver.session_id, sqlserver.client_app_name, sqlserver.client_hostname)
  WHERE (duration >= 1000)         -- 1 ms and up
)
ADD TARGET package0.event_file(
  SET filename = @xe_path + N'Waits.xel',
      max_file_size = 500,
      max_rollover_files = 20
);

ALTER EVENT SESSION [WaitsSampler] ON SERVER STATE = START;

Use it for confirming disk stalls, log bottlenecks, lock waits, or parallelism waits.

Ring buffer option for quick checks

If you only need a short, in-memory capture, replace the file target with:

ADD TARGET package0.ring_buffer(SET max_events_limit = 10000, max_memory = 20)

Reading files into a view or a history table

Reusable view over all .xel files

CREATE OR ALTER VIEW dbo.ReadXE_All
AS
SELECT
  object_name,
  CONVERT(datetime2, ed.value('(@timestamp)[1]','datetime2')) AS utc_time,
  DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), SYSDATETIME()),
          CONVERT(datetime2, ed.value('(@timestamp)[1]','datetime2'))) AS local_time,
  ed AS event_xml
FROM (
  SELECT CAST(event_data AS XML) AS ed
  FROM sys.fn_xe_file_target_read_file('D:\XE\*.xel', NULL, NULL, NULL)
) AS src;
GO

History table pattern

CREATE SCHEMA XE AUTHORIZATION dbo;
GO
CREATE TABLE XE.Events
(
  id         bigint IDENTITY(1,1) PRIMARY KEY,
  event_name sysname,
  utc_time   datetime2(3),
  local_time datetime2(3),
  payload    xml
);
GO

INSERT XE.Events(event_name, utc_time, local_time, payload)
SELECT
  object_name,
  CONVERT(datetime2(3), ed.value('(@timestamp)[1]','datetime2')),
  DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), SYSDATETIME()),
          CONVERT(datetime2(3), ed.value('(@timestamp)[1]','datetime2'))),
  ed
FROM (
  SELECT CAST(event_data AS XML) AS ed
  FROM sys.fn_xe_file_target_read_file('D:\XE\LongRunning*.xel', NULL, NULL, NULL)
) AS src;

Create a SQL Agent job to load new files on a schedule, then move or delete old files to keep the folder tidy.

Operating and housekeeping

List sessions and targets:

SELECT s.name, s.startup_state, t.target_name
FROM sys.dm_xe_sessions s
LEFT JOIN sys.dm_xe_session_targets t
  ON s.address = t.event_session_address
ORDER BY s.name;

Start, stop, drop:

ALTER EVENT SESSION [NoisyClients] ON SERVER STATE = START;
ALTER EVENT SESSION [NoisyClients] ON SERVER STATE = STOP;
DROP EVENT SESSION [NoisyClients] ON SERVER;

Troubleshooting quick hits

Directory invalid or Access denied
The folder does not exist or the SQL Server service account cannot write there. Create the folder and fix NTFS permissions.
Files grow too large
Set both max_file_size and max_rollover_files. Add filters such as duration thresholds.
View returns nothing
The path mask must match your files. Confirm the session is started and writing to the expected location.

Final notes

Profiler had a long run, but Extended Events is better for modern SQL Server work. Use file targets in production, keep short tests in the ring buffer, and load files into a table or view when you need analysis or reporting. Save these sessions as scripts in source control so the team can start, stop, and read them the same way every time.

Discover more from SQLyard

Subscribe to get the latest posts sent to your email.

SQL Profiler is deprecated. Use Extended Events instead

Summary

What is Extended Events

Targets: file vs ring buffer vs tables or views

Capture options: file vs view-only vs table

1) Event file (recommended for production)

2) View-only (no persistence) via ring buffer

3) Table (history you can query and join)

Which one should you choose

Permissions reminder

Required permissions and file folder setup

A safe session template

Five useful Extended Events sessions for DBAs

1) Identify noisy or broken clients

2) Long-running queries

3) Query timeouts and cancels

4) Deadlocks with full graphs

5) Waits sampler

Ring buffer option for quick checks

Reading files into a view or a history table

Operating and housekeeping

Troubleshooting quick hits

Final notes

Like this:

Related

Discover more from SQLyard

Leave a ReplyCancel reply

Sign up to receive email updates, fresh news and more!

Summary

What is Extended Events

Targets: file vs ring buffer vs tables or views

Capture options: file vs view-only vs table

1) Event file (recommended for production)

2) View-only (no persistence) via ring buffer

3) Table (history you can query and join)

Which one should you choose

Permissions reminder

Required permissions and file folder setup

A safe session template

Five useful Extended Events sessions for DBAs

1) Identify noisy or broken clients

2) Long-running queries

3) Query timeouts and cancels

4) Deadlocks with full graphs

5) Waits sampler

Ring buffer option for quick checks

Reading files into a view or a history table

Operating and housekeeping

Troubleshooting quick hits

Final notes

Share this:

Like this:

Related

Discover more from SQLyard

Related Posts

Leave a ReplyCancel reply

Discover more from SQLyard