It is well known that
running a profiler consumes a lot of resources depending on what events are being monitored.

I am interested in monitoring deadlocks that occurs occasionally (3~5 a week).
using SQL Server profiler with following event selections (every event but highlighted ones from TSQL_Locks template)

alt text

Would it slow down to monitor(for one whole week without stopping) only locks against production databases?

Are there any rules of thumb on when and how long to run the profiler for deadlocks?

  • 1
    Don't monitor for Lock:Deadlock Chain because it happens quite often (eahc time the server walks a chain of locks to detect if deadlocks are present, ie. every few seconds). I would only monitor for the Deadlock Graph event and nothing else, on server side trace as Nick already suggested. – Remus Rusanu Nov 9 '09 at 18:01
  • @Remus: Thank you for the tip. As I was reading about Lock:Deadlock Chain on BOL, it doesn't looks like I'd need to monitor it. – Sung M. Kim Nov 10 '09 at 1:24

Use a server side trace instead. Do a search for using Profiler to create server side traces to figure out the details.

Here is a decent blog post on the topic. This is the best way to reduce the impact on your production db.

Your other alternative is to turn on trace flags 1204 and/or 1222 & monitor the output in the errorlog.

How long should you run the trace for? Long enough to figure out & fix the code involved. You'll also want to run the trace again once the fix has been put in place to make sure its not re-occurring.

There maybe some that argue its always wise to have the trace flags enabled. I think it depends on the application, as trace flags also create additional load.

  • Thanks, Nick. Running a server-side trace seems like a good idea for my situation. – Sung M. Kim Nov 9 '09 at 14:37

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.