Percentage of blocked connections

| 0 comments | 10,017 views
Categories:

VN:F [1.9.17_1161]
Rating: 5.0/5 (2 votes cast)

This metric reports the number of connections that are currently blocked, divided by the total count of current connections.

Most well-balanced SQL Servers will have some degree of blocking. This metric attempts to measure the impact of lead blocking queries against other queries. Higher values indicate that many connections are being blocked, and queries should be tuned to reduce the amount of contention.

For more information, see INF: Understanding and resolving SQL Server blocking problems.

Percentage of blocked connections, 5.0 out of 5 based on 2 ratings

Metric definition

Metric Name:
Percentage of blocked connections
Description:

This metric reports the number of connections that are currently blocked, divided by the total count of current connections.

Most well-balanced SQL Servers will have some degree of blocking. This metric attempts to measure the impact of lead blocking queries against other queries. Higher values indicate that many connections are being blocked, and queries should be tuned to reduce the amount of contention.

Guideline values: Higher percentage values indicate more blocking. This number will should change with each collection as queries are being run against the server. Trends are more important than spikes, although spikes can help you identify candidates for query tuning.

Possible solutions: If high values are noticed, query plans should be examined for tuning opportunities to reduce blocks. Keeping transactions short on well-indexed tables can reduce blocking contention; blocking can also be reduced by rewriting poorly defined queries (e.g., queries with unnecessary joins).

For more information, see INF: Understanding and resolving SQL Server blocking problems.

Enter the T-SQL query that will collect data:
SELECT  CAST(SUM(CASE WHEN blocking_session_id <> 0 THEN 100.00
                      ELSE 0.00
                 END) / COUNT(*) AS NUMERIC(10, 2))
FROM    sys.dm_exec_requests
WHERE database_id = DB_ID();
Select instances to collect from:
Select all
Choose databases to collect from:
All databases
Set collection frequency:
Collect data every minute
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

An alert is not required

VN:F [1.9.17_1161]
Rating: 5.0/5 (2 votes cast)