Implementing a Blocking Monitor

Blocking means that one query (Query1) is locking resources that another query (Query2) is waiting to use. Blocking can seriously affect response time and throughput. Response time measures how long it takes for a user who issues a query to get a response. Obviously, response time for Query2 increases if Query2 is doing nothing because Query1 is blocking it. Throughput refers to the number of users who can log in and execute queries at the same time without bogging down the server. Complex blocking chains--in which Query1 blocks Query2 which blocks Query3, and so on--can keep key system resources locked for long periods and serialize access so that only one or two users can effectively use the system at one time. You definitely want to avoid either of these situations.

Blocking problems are difficult to troubleshoot, because often they don't show up in development environments in which few users are running queries against the system. To make matters worse, one infrequently run query or report can start a long, complex blocking chain. Trying to track intermittent performance problems that inevitably result from a blocking chain can be frustrating.

Master.sysprocesses contains one row for every connection to SQL Server. This table is a materialized table--SQL Server dynamically creates it in memory every time a user accesses it, rather than storing it on disk--and it has several interesting columns, including blocked and waittype. If a query is blocking a connection, master.sysprocesses.blocked will contain the server process ID (spid) of the blocking connection. The stored procedures and table we describe here use this information to implement a simple yet effective blocking monitor, which you can run as a background task.

First, create tables for storing blocked lock information and controlling the background process. The statement

CREATE TABLE BlockMonitorInfo
(
spid smallint,
blocked smallint,
waittype binary,
InfoTime datetime
)
go

creates a table to store the blocking data. The following statement

CREATE TABLE BlockMonitorControl
(
BlockMonitorOn tinyint
)
go

creates a control table that lets you turn the background process on and off. The following INSERT statement populates the control table and initially turns on the background process that you'll create later.

INSERT INTO BlockMonitorControl
VALUES (1)
go

Next, Listing 1 shows how you create the two stored procedures that let you turn the block monitor on and off.

Running StartBlockMonitor begins populating the BlockMonitorInfo table with information from master.. sysprocesses about connections that other connections are currently blocking. Table 1, posted on the Windows NT Magazine Web site (http://www.winntmag.com; enter 3867 in the instaNT Doc text box), lists Microsoft's definitions for waittype values.

You can make this blocking monitor solution even more powerful by combining it with SQLTrace output. This output lets you see the exact commands that SQL Server was executing on both the blocked and the blocking connection. As you troubleshoot blocking problems, focus on the root of the problem. For example, if Query1 is blocking Query2, which is blocking Query3, you want to fix the root block (i.e., Query1). Fixing Query1 is likely to have a cascading effect and automatically fix subsequent blocking problems. Here's a query that will let you quickly decipher the information in BlockMonitorInfo to figure out which connections are the root in a blocking tree.

SELECT
BlockMonitorInfo.*
FROM
BlockMonitorInfo,
(SELECT distinct InfoTime FROM BlockMonitorInfo)
BlockChainTime
WHERE
BlockMonitorInfo.InfoTime =
BlockChainTime.InfoTime
and Blocked not in
(SELECT spid
FROM BlockMonitorInfo
WHERE InfoTime =
BlockChainTime.InfoTime)

This information won't necessarily solve your blocking problems, but it will help you see which queries and objects are causing the most trouble. Table 2 (posted at http://www.winntmag.com; enter 3867 in the instaNT Doc text box) provides some additional resources about blocking.