Percentage of blocked 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.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

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.

Metric definition

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.

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

0

Collection frequency

60

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

An alert is not required