Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links



Search this Blog


Post Categories


SQL Profiler best practices

SQL Profiler is a great tool that allows you to see what's going on inside SQL Server.  You can find out what your worst performing queries are, how often a query is executed, and loads of other stuff.  I'm typically interested in the things that are impacting performance such as long durations of stored procedures.

One important thing to know when running a trace using SQL Profiler is that the trace can degrade performance.  To minimize its impact, you should follow these best practices:

  1. Never run SQL Profiler on the database server
  2. Never trace directly to a table, instead trace to a file (You can query the active trace file using this technique)
  3. Filter the results, such as "Duration > 1000"
  4. Include only the events that you are interested in, such as SP:Completed and SQL:BatchCompleted

For more information on SQL Profiler, check out this article.

You should also know that a server-side trace, SQL Trace, can use even less resources than SQL Profiler.  SQL Trace allows you to create a trace programmatically by calling system stored procedures.  Due to this, you can automatically collect trace data via SQL jobs. 

For more information on SQL Trace, start here.

Print | posted on Wednesday, August 06, 2008 5:36 PM | Filed Under [ SQL Server - Database Administration ]



# re: SQL Profiler best practices

Hi Tara,

Just one point of clarification: Using Profiler to save to a file is not not the same as doing a server-side trace, and the Profiler tool will always use the (much less efficient) rowset provider. Rather than use Profiler itself to save to a file, it's much better to script the trace definition and do everything server-side when possible.

Read more on this topic in the chapter I wrote for "Inside Microsoft SQL Server 2005: Query Tuning and Optimization"

8/7/2008 2:29 PM | Adam Machanic

# re: SQL Profiler best practices


I'm very aware of Profiler to file and server-side trace being different. I've got quite a complex server-side trace running on a continuous basis. It saves a few days of raw trace data and also archives aggregated data.

Could you point out in my blog post where your clarification applies? I don't see that I made a mistake in my wording, but a second set of eyes would be helpful.
8/7/2008 2:51 PM | Tara

# re: SQL Profiler best practices

Hi Tara,

You mention that Profiler degrades performance and that the four tips will help minimize this problem. However, as I'm sure you know, saving to a file via Profiler does not help in this regard. To the contrary; if you use the "Server processes trace data" option you'll actually incur the cost of two traces, and not using that option you'll still have all of the normal problems associated with the rowset provider. I assumed that you actually meant to tell readers to use a server-side trace, so I added the clarification.
8/7/2008 3:15 PM | Adam Machanic

# re: SQL Profiler best practices


But saving to a file while running SQL Profiler does help with performance. A couple of years ago, I was working on a production problem. I was running SQL Profiler from a client machine but saving the results to a trace table. I started getting frantic calls from our support people that performance was suddenly horrible. I stopped the trace, performance went back to normal. I could repeatedly do this with the same results. I then changed my trace to save to a file. This completely saved us from our performance issues.

On this particular system, we are talking about 75% improvement when saving to a file as compared to a table.

Note that I do not select the option "Server processes trace data" when saving to a file.

A server-side trace works even better, but sometimes you just don't have the time.
8/7/2008 3:23 PM | Tara

# re: SQL Profiler best practices

Ah, I get the disconnect now; I didn't understand that you were comparing saving to a file vs. saving to a table and not comparing that to simply using the UI. I personally only use Profiler with extreme filters in place and almost always just read the data straight from the UI. On the rare occasion that I want to do something more with I save it to a file or table after the fact. So I guess my comments are colored by the way I personally use the product! Good reminder that these are very flexible products with lots of different use cases and we all see things through differently colored lenses.
8/7/2008 3:38 PM | Adam Machanic

# re: SQL Profiler best practices


Ah! Yes I now see your point and why I was confused. I agree on everything and will change my post to make it more clear. Just got to figure out how to word that statement.
8/7/2008 3:43 PM | Tara
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET