The Fundamentals Of Using SQL Server Profiler To Trace SQL Activity

SQL Server Profile is a powerful tool for tracing activity within Microsoft SQL Server. It can be used to help you better understand what is happening on the sever during query execution or at any given point in time when the server is online. SQL Server Profiler, which is accessible from SQL Management Studio, collects the majority of it’s data from the underlying Extended Events, which captures a significant amount of activity.

One important thing to keep in mind when working with Profiler is that running a SQL trace can be a bit of a resource hog as Profiler provides a graphical interface for capturing the activity.  Depending on the data you are capturing as well as the length of time you intend to run the trace, it’s generally a good idea to execute the trace remotely instead of running it directly from the SQL Server.  With that said, there is another, less resource intensive option called SQL Trace which uses a collection of built in stored procedures to trace activity within SQL Server.  Profiler actually uses the SQL Trace procedures but allows you to configure them in a graphical, user friendly manner.  Though I will not discuss SQL Trace in this post, it’s certainly worth looking into, especially if you are needing to run a constant trace against a database or server.

The first step to running a trace is to launch SQL Server Profiler.  Once you’re logged into SQL Management Studio, click on Tools then select SQL Server Profiler.

sql_server_profiler_menu

Once Profiler loads the first thing you will need to do is select the server that you want to run the trace against. Once you’ve chosen the server click Connect and you will then see the Trace Properties configuration window.  This is where you will name and configure the trace prior to running it.

The Trace Properties window has two tabs that you will want to be familiar with.   The General tab includes options to name the trace, as well as the ability to use a predefined template and configure output options.  There is also an option to enable trace stop time.  This allows you to automatically set the trace to end at a predefined time.

trace_properties

Though not always necessary, its good practice to name your trace.  In the example above I have named it Test Trace.  I am using the Standard (default) template.  It’s a good idea to familiarize yourself with the various templates that Microsoft has provided.  Often times, when running a trace, you’re looking for something specific.  Depending on the issue that is being researched, one of the templates may provide a better method for capturing and reviewing the data.  A quick way to better understand this is to select each of the templates and then switch to the Event Selection tab and see how the list of events and columns has changed.

The General tab also allows you to save the trace to a file or table within the database.  Though I rarely select these options it’s good to know that they are available.  Keep in mind, you can also save the trace to a file once it has run by selecting Save from the file menu.

The Standard (default) template includes many of the options available. Though is is often overkill, its a good selection for demonstrating how powerful SQL Server Profiler can be.

Once the General tab has been configured, select the Events Selection tab.

There are numerous options available here.  Knowing what to capture may require some additional research but in many cases if you are selecting the default options you should be able to capture everything that is needed to identify the source of the issue you are researching.  If you want to add additional events to the trace click the check box next to Show all events.   This will allow you to select additional items to be captured during the trace.  In the example below, I have added the Errors and Warnings event to the default template.

trace_properties_event_selections

Another important step which can help you narrow down the scope of the trace is to add column filters. Filters allow you to only capture data based on a specific set of rules.  The most common filter that I tend to use is the LoginName or SPID filter.  By specifying a login name or SPID I can set the trace to only capture data from a specific user.  This cuts out a significant amount of data that would otherwise clutter the trace.  Though this is not always an option, as sometimes you can’t easily replicate a certain issue, if possible, this is an excellent strategy for creating a targeted trace.

edit_filter_trace

Once you have configured any desired filters, click OK to return to the Trace Properties window.  When you are ready to begin the trace click Run.

As the trace is running, any activity that occurs within the predefined parameters will begin to display within the trace window.  The top half of the screen will display the actual events.  If you click on a particular event you will see the details of that event displayed in the lower half of the screen.

In the following example I started a trace in order to capture all the activity for my login.  Once the trace was started I ran a simple SELECT script which included an INNER JOIN.  I also ran a script to create a table in the DYNAMICS database named TestTable.  I then dropped the table.

Once both scripts had been executed, I stopped the trace.

trace_results

As you can see, both actions were captured by the trace.  The event currently selected in the above screenshot shows the details of the SELECT statement.  You will notice there are actually two records displayed for each event.  One shows that the batch was being started and the other shows that it completed.  The important thing to note about these two records is that the BatchCompleted record will also include the details of the execution time and read/write metrics.  This can be important when diagnosing performance issues or opportunities for database tuning.

There are literally thousands of ways to configure a trace.  Having a deep understanding of the inner workings of your database will help you better understand all the options that are available when configuring SQL Server Profiler to run a trace.  Personally, I found it very useful when first learning about Profiler to run a filtered trace against my login and perform a series of actions against the server and then dig into the trace to better understand how each type of action is recorded.  This will help you better understand what you are looking at when reviewing the trace log.