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 profile is the good ol’ way of doing things and still remains superior in some ways to XE (extended events) – but it has been officially placed on the deprecation list #1.
- The data is real time.
- Data is easy to read into SQL Server and pair-down later using tSQL.
- May years’ worth of documentation from the user community.
Select * into from Get trace data()
Select event_name, events.* from
Tracing with SQL Server Extended Events (XE)
Extended events were introduced in SQL Server 2008 containing not too many more event types 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, marginally less overhead than Profiler.
- Can easily run perpetually without worrying about filling up a drive or a database.
- 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_AX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB)
–Drop an XE session:
DROP EVENT SESSION [batch_completed] ON SERVER
–View all XE sessions:
SELECT * FROM sys.dm_xe_ions AS XES
–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
“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.”