Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As a developer, I use SQL Profiler quite often. It's a good debugging tool, both to track what my code is doing and to analyse performance problems.

But I've always used it on my development environment, and in a very controlled way.

  • Start my application, and get it into a specific state
  • Start a trace on the profiler
  • Perform a specific sequence of actions on my application
  • Stop the trace and examine the results.

Can the SQL Profiler be practically used in an in-production environment?

My first concern is that it would degrade the performance.

My second concern is that, because it's in production, you aren't triggering the interesting actions itself. You would have to leave the profiler running for a long period then analyse the results. Would the result set become too unwieldy? (Taking up too much disk space and being too hard to query).

Does anyone use the SQL Profiler in production?

share|improve this question
1  
If you know what you are looking for then you might not even need tracing, e.g dba.stackexchange.com/questions/756/… –  Gaius Jan 24 '11 at 5:41
add comment

4 Answers

up vote 12 down vote accepted

Using Sql Server Profiler (GUI tool) to trace a production server is not a good idea. But it depends on load. Use server-side sql tracing (see sp_trace_XXX procedures) instead of it. Also I have found articles:

Performance Impact: Profiler Tracing vs. Server Side SQL Tracing,

Automating Server Side Tracing in SQL Server

Avoid Causing Problems with Profiler

maybe it will be interested and useful.

Book Online says:

  • Run Profiler remotely instead of directly on server
  • Avoid including events that occur frequently (e.g. Lock:Acquired) unless absolutely needed
  • Include only event classes needed
  • Specify limiting filters to reduce the number of events
  • Avoid redundant data (e.g. SQL:BatchStarting and SQL:BatchCompleted)
  • Avoid running large traces with Profiler; consider a server-side SQL Trace instead
  • Limit server-side trace file size and manage space usage
share|improve this answer
1  
To minimize impact filter as best you can and trace to a file via sp_trace commands. The GUI running remotely will cause the most impact but you can use it to easily generate a script with all your filters that you can quickly modify to dump to files instead. Set the # of files and file size appropriately. –  AndrewSQL Jan 24 '11 at 17:07
add comment

The Profiler will always introduce a performance impact.

If you are using SQL Server 2008R2+ you can use extended events. This provides much of the information you see in the profiler with a fraction of the performance hit.

Books online introduction http://technet.microsoft.com/en-us/library/bb630354(v=sql.105).aspx

This feature received a big update in SQL Server 2012 which now includes a GUI in SSMS.

share|improve this answer
add comment

I use SQL Profiler against production all the time. When done correctly (filtering so that you get back a very small amount of data) against a server the risk is minimal. Tracing everything down would be useless.

share|improve this answer
add comment
  1. Yes, the act of monitoring will require some resources. Running it on an overloaded server could kill it.

  2. You'll actually monitor real life load: your actions could get lost in the noise of this load.

We run it on production sometimes. Mainly with a text filter for specific code, or with CPU/duration filters to trap longer running queries. And we don't try to capture XML execution plans or some such nonsence

The key is to know what you are looking for: we don't tend to leave it running and trap everything.

In this case, if you want to see the results of some actions can you do it out of hours?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.