patternsqlMinor
Performance impact of sp_configure blocked process threshold change
Viewed 0 times
impactblockedprocesssp_configurethresholdperformancechange
Problem
I've been tasked with identifying any blocking happening on a production server. My plan to achieve this is to use
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.
EXECUTE sp_configure 'blocked process threshold', 5In 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.
Solution
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...
Make sure you monitor the generated traces and make sure you don't cause an self-induced outage due to disk space exhaustion...
Context
StackExchange Database Administrators Q#43406, answer score: 6
Revisions (0)
No revisions yet.