SQL Trace

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.

The SentryOne team understands that both SQL Trace and Extended Events are important when troubleshooting and optimizing database performance. SentryOne SQL Sentry collects the most actionable event data, presents it in a consumable manner for novices and experts alike, and ensures that tracing overhead remains low by configuring the safest options internally. SQL Sentry also maintains a running history of collected events and presents them through useful features such as the Top SQL, Disk Activity, Deadlocks, and QuickTraces tabs in the SentryOne interface.

See for yourself how SQL Sentry simplifies SQL Server event tracing—download a free SentryOne trial today.
 
Download Trial

SentryOne SQL Sentry QuickTrace™

A QuickTrace in SentryOne SQL Sentry provides the actionable information you need to quickly identify events that are affecting SQL Server performance. A QuickTrace combines process-level data and trace events collected to deliver a comprehensive view of SQL Server activity for a specified time range, with low overhead and minimal impact on your SQL Server. With a QuickTrace, all events are collected and results are displayed in an easy-to-read graphical interface.

QuickTrace Benefits

  • Run a QuickTrace on demand or automate the Run QuickTrace action based on certain conditions such as blocks or performance thresholds

  • Automated aggregation of metrics such as CPU, I/O, recompiles, cache misses, and cursor operations

  • Metrics are grouped and sorted so that you can easily see processes, hosts, applications, or users responsible for activity during a specified time range

  • View QuickTrace events overlaid on the Performance Analysis Dashboard charts

  • Export a QuickTrace in a variety of formats

  • And more

QuickTrace takes the frustration out of event tracing so that you can quickly and easily pinpoint activity on your SQL Server that is slowing performance.

Download and try SQL Sentry for free today!

 
Download Trial