8

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.

6

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

  • 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

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.