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 the profiler
  • Perform a specific sequence of actions on my application
  • Stop the profiler 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?

Full disclosure: I've posted the same question on the Database Administrators Beta Site. Serverfault members who want to support the DBA stackexchange site have the opportunity to answer the question there rather than here.

  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. 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?

  • 1
    I agree with gbn, it definitely requires resources, so you'll need to be careful when running it. We typically use it with a filter on the login to debug the action(s) of a particular user for a specific problem. We also periodically run a trace for the whole day to gather information. Something like this definitely needs to be scheduled on days with lighter loads, and needs to be very closely watched. It also helps the load to log to local files on another computer and then process the data afterwards. – Paul Kroon Jan 26 '11 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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