05 June 2013

Percentage of blocked connections

by Stuart Ainsworth

15,882 0 Performance

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.

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.

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


		
			
			
Transact-SQL
			
			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();
			
				
					
				
					1
2
3
4
5
				
						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();

Instances to collect from

Select all

Databases to collect from

Collection frequency

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

15,882 Rate

Click to rate this post!

[Total: 4 Average: 5]

Percentage of blocked connections

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics