29

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?

Improve this question
1

4 Answers 4

Reset to default
19

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
Improve this answer
1
  • 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
    Commented Jan 24, 2011 at 17:07
21

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.

Improve this answer
7

  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?

Improve this answer
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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