SQL Server event tracing
Tracing events in SQL Server is key to understanding and optimizing database workloads. SQL Server provides three options for tracing events: SQL Trace, SQL Server Profiler, and Extended Events.
SQL Server Profiler is a graphical interface for managing and viewing SQL Trace. You can configure SQL Trace to run “server side” in which case events are stored on a file and collected for later review. Extended Events, introduced in SQL Server 2008, provides a more programmable event tracing option and relieves long-running concerns about potential performance implications related to SQL Trace.
(Note that although the performance concerns surrounding SQL Trace are warranted, in most cases, these problems are related to how a trace is configured in SQL Server Profiler. The default settings for a new trace in SQL Server Profiler create a trace that is often highly impactful to workload performance and provides more information than can be easily consumed.)
Using Extended Events to trace events
Extended Events sessions can be created and managed using a graphical interface in SQL Server Management Studio (SSMS). The value of Extended Events is unlocked when you program it to collect trace information relevant to a specific need, which was not an option with SQL Trace. In addition, Extended Events creates less performance overhead in general and by design.
The challenge with Extended Events is its complexity. Getting started is easy enough but troubleshooting and analysis with Extended Events has a more significant learning curve. However, experts who invest their time in mastering Extended Events can enjoy the benefit of targeted, low-impact SQL Server event tracing.
