Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been tasked with identifying any blocking happening on a production server. My plan to achieve this is to use

EXECUTE sp_configure 'blocked process threshold', 5

In combination with a server side trace to collect a Blocked Process Report.

I understand that this activity will incur a performance hit, I'd like to find some way of quantifying what this will be.

share|improve this question
add comment

1 Answer

Minimal hit. The cost of evaluating the blocked process list is already there since this is how deadlocks are detected. The cost of generating and writing an event in the server side log is quite low and any server IO should be able to handle an extra write into LOG/*.trc every 5 seconds (At worst). But 5 is a quite low setting, you may be flooded by reports. Is your server server blocking so well behaved as to consider any 5 second block an incident to investigate?

Make sure you monitor the generated traces and make sure you don't cause an self-induced outage due to disk space exhaustion...

share|improve this answer
    
I agree - the hit is minimal, but the setting of '5' a bit on the low side. I set most of mine at 20. So if a blocking situation lasts for 3 minutes, there are ~ 9 entries on the trace –  John Alan May 30 '13 at 10:44
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.