Tracing events in SQL Server

When you want to trace or instrument SQL Server activity, whether it be for performance purposes, security, auditing, etc., you really have two baked-in methods. Well, there are a few others, but they are not all that robust.

Tracing with SQL Profiler

Tracing with SQL Server Profiler is the good ol' way of doing things and still remains superior in some ways to XE (Extended Events), but it has been officially deprecated (the deprecation was announced with SQL Server 2012).

• The data is real time (although the XE live data viewer in SSMS is too).
• Trace data is easy to read into SQL Server and pare down later using T-SQL.
• Many years' worth of documentation from the user community. Trace files can be loaded into a table with fn_trace_gettable. The first example reads a trace file (and any rollover files) into a table, and the second joins to sys.trace_events to turn the numeric event class into an event name:

SELECT *
INTO dbo.TraceData
FROM fn_trace_gettable('C:\Traces\YourTrace.trc', DEFAULT);

SELECT te.name AS event_name, t.*
FROM dbo.TraceData AS t
JOIN sys.trace_events AS te
    ON t.EventClass = te.trace_event_id;

Tracing with SQL Server Extended Events (XE)

Extended Events were introduced in SQL Server 2008 with a modest number of event types, not many more than were available in Profiler, but that didn't last long. Microsoft has been busy adding lots of new event types as they have been aggressively adding features to the database engine.

• Definitions are easy to understand and port to other SQL Servers.
• Data is stored easily in various formats inside and outside of SQL Server.
• In most cases, less overhead than Profiler.
• Can easily run perpetually without worrying about filling up a drive or a database, as long as you use a bounded target (a ring buffer with a maximum memory size, or an event file with a maximum size and rollover file count).
• Can be easily turned on and then turned off without the need to re-create each "trace" (it's really called a session). Create an XE session:

CREATE EVENT SESSION [batch_completed] ON SERVER
ADD EVENT sqlserver.sql_batch_completed
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
);

A new session is created in the stopped state, so you need to start it (see below). To capture more than one event, separate the ADD EVENT clauses with commas.

Drop an XE session:

DROP EVENT SESSION [batch_completed] ON SERVER;

View XE sessions:

All sessions that are defined on the server, whether they are running or not:

SELECT * FROM sys.server_event_sessions;

Only the sessions that are currently running:

SELECT * FROM sys.dm_xe_sessions;

Start an XE session:

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

Stop an XE session:

ALTER EVENT SESSION [batch_completed] ON SERVER STATE = STOP;

Via Microsoft:

"We are announcing the deprecation of SQL Server Profiler for Database Engine Trace Capture and Trace Replay. These features will be supported in the next version of SQL Server, but will be removed in a later version. The specific version of SQL Server has not been determined."