By David Wiseman (Administrator)published 27 Mar 2009, modified 28 Mar 2009
My Rating:
Not Rated

SSMS 2008 Tip - Trace Query in SQL Server Profiler

I've been using SSMS 2008 for a few months at the time of writing this article, but I've only recently noticed a feature called "Trace Query in SQL Server Profiler".  This feature allows you to run SQL Server Profiler at the click of the button to capture events for the current query window (The trace is filtered on the current SPID). 

Why SQL Server Profiler?

SQL Server Profiler is categorized as a performance tool and its purpose is to capture events from SQL Server.  The tool can capture the queries issued to SQL Server by users and client applications along with information such as the application name, login name, CPU usage, reads, writes and duration of the query.  A large number of other events are available to capture but it's beyond the scope of this article to discuss all the features and options of SQL Server Profiler.

The information you capture from SQL Server Profiler can be very useful for performance tuning and troubleshooting purposes. For example, SQL Server Profiler allows you to run a trace to capture slow running queries, helping you target the best candidates for performance tuning.  You might also be in a situation where you need additional insight into an application to troubleshoot a particular problem.  I was asked to look at a query timeout problem by a developer recently.  Activity Monitor was the first tool I used to troubleshoot the problem and I was able to identify the cause of the timeout as a lock wait.  SQL Server Profiler was able to give me detailed information about what the application was doing and enabled me to pinpoint the problem quickly without needing access to the application source code.  In this case it turned out that the application had obtained an exclusive lock on one connection and later tried to access the same information on a different connection.  The second connection was been blocked by the first, but the first connection was also waiting for the second connection to complete its transaction, leading to a deadlock situation.  SQL Server does have a deadlock detection algorithm, but it would be impossible for it to detect the deadlock in this particular situation - eventually the .NET framework default timeout kicked in returning a timeout error to the application.

Why "Trace Query in SQL Server Profiler"?

The key to effective use of SQL Server Profiler is learning which events/information to capture and what filters to apply for a particular scenario. There are occasions where you want to view detailed information about a stored procedure or a batch that you are running in SQL Server management studio. This might be for troubleshooting purposes, or you might be interested in performance tuning a particular stored procedure and want to know which part of the procedure is slow running.  You can always load SQL Server Profiler and set it up to capture the information you are interested in, filtering it for the current connection.  Alternatively, you can setup and run the trace at the click of a button from Management Studio with the new  "Trace Query in SQL Server Profiler" feature.

Using "Trace Query in SQL Server Profiler"

The "Trace Query in SQL Server Profiler" feature is available to select from the "Query" menu in Management Studio, or you can use the CTRL+ALT+P shortcut.  You can also add a button to your toolbar by performing the following steps:

  • Open SQL Server Management Studio
  • From the Tools menu, select "Customize"
  • In the "Commands" tab, select the "Query" category
  • Scroll until you view the "Trace Query in SQL Server Profiler" command.  Drag and drop this to the toolbar.

  • The button is now on your toolbar - simply click it to launch SQL Server Profiler.

After running "Trace Query In SQL Server Profiler", SQL Server Profiler is launched and a trace is setup and running that filters for events issued from the current query window. The trace produced is based on the "TSQL_SPs" trace template, capturing the following events:

  • Audit Login
  • Audit Logout
  • ExistingConnection
  • RPC:Starting
  • SP:Completed
  • SP:Starting
  • SP:StmtStarting
  • SQL:BatchStarting

You can customize this template to your own requirements in SQL Server Profiler by clicking "File\Templates\Edit Template" option in the menu.  I often find it useful to capture the "completed" events as these allow you to include performance information. e.g. CPU, Duration, Reads & Writes.  Once the template has been modified it displays as "TSQL_SPs (User)" and the original template is still available to select.  The "Trace Query in SQL Server Profiler" will use your modified version of the trace.

I think "Trace Query in SQL Server Profiler" is a really useful feature, but it doesn't seem to have had much publicity.  I'm almost ashamed to say that I've been using SSMS 2008 for a few months and have completely missed this feature until recently.  Maybe you have also missed this useful edition to Management Studio - Hope you find this tip useful!