SQL Server Utilities - Maximize SQL Trace

In this post I will discuss and present some practical use cases for SQL Trace also known as “Server Side Trace”.

Before we move forward I would like to first make the distinction between SQL trace and SQL Server Profiler.

SQL Trace is a database engine technology while SQL Server Profiler is a .NET application (and a great one) that uses system stored procedures exposing the functionality of SQL Trace by implementing the Microsoft.SqlServer.Management.Trace namespace.

A key different to point out between SQL Trace and Profiler is the Trace IO Providers used by each. When you use SQL Trace to capture data to a file the File provider is being used while Profiler uses the Rowset provider. Each of the Trace IO providers comes in to provide different functionality in order to meet different requirements but for the discussion of this post I would like to emphasize the fact that the File provider is much lighter compared to the Rowset provider.

Another key difference to mention is that the File provider guarantees that no events will be lost whereas the Rowset providers does not guarantee that and under load events can be dropped.

SQL Server Profiler is a great way (and probably the simplest) for a quick and dirty trace and I definitely use it quiet often but if we want to leave traces running for along time or have them running constantly, SQL Trace or Extended Events is the way to go.

Here is a link to a ppt file that I have presented when talking about this topic years back.

Note that while It is true that SQL Trace has been announced as a deprecated feature and Extended Events is expected to be the replacing feature I do not see it happening in the near future nor beyond. Keep in mind that if SQL Trace is killed it takes Profiler down along and while Extended Events is a great infrastructure for tracing SQL Server many DBAs still feel more comfortable with SQL Trace and Profiler in particular.

For more information about SQL Trace architecture you can refer to Chapter 2: Tracing and Profiling of the excellent book “Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization”. The information in this chapter is the best I have seen for the topic in discussion.

Here is a link to a first post showing a practical real life example using the power of SQL Trace

Capture database exceptions