Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
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 Nov 10 '09 at 1:24
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks, Nick. Running a server-side trace seems like a good idea for my situation. –  Sung Nov 9 '09 at 14:37
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.