Home / server / Questions / 82553
Accepted
dance2die
Asked: 2009-11-08 11:42:45 +0800 CST

Resource consumption on running Deadlock profiler against production database

  • 772

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?

monitoring

1 Answers

  1. Best Answer

    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.

    • 3