Identify Blocked SQL Processes Quickly
Improve application performance by creating a utility that tells you which processes are being blocked.
TECHNOLOGY TOOLBOX: SQL Server
Your database can be a primary source of poor application performance. Create your own utility that tells you which processes are being blocked and which process is causing the blocking. The system stored procedure sp_who2 is a well-known SQL Server utility that determines which processes are running on the database server and which processes are being blocked, thus slowing down application performance. However, this utility doesn't easily identify the root cause of the blocking or the dependency of the blocked processes. Developing your own utility can solve these problems.
The database block-tracing utility, which I've named dba_BlockTracer, extracts data about the running processes by inspecting the system view sys.sysprocesses (see Listing 1). This view queries the underlying system table sysprocesses, which is the same table used when running sp_who2.
Prevent any unnecessary locking, such as holding locks and honoring locks, by setting the transaction isolation level to "read uncommitted." Next, examine the sys.sysprocesses system view to determine if any blocked processes exist. You can test this quickly by using the EXISTS keyword, which exits as soon as the first record is found. If it doesn't find any blocked processes, a message will display and the routine will exit.
If it does find blocked processes, the sys.sysprocesses will self-join with itself, based on the process ID (spid) and execution context ID (ecid). Actually, the spid identified in the blocked field will be joined to the spid of this self-joined table. This ensures that only blocking or blocked processes are in the output. To determine the root-blocking processes, include records only when the blocked field is equal to 0 (in other words, if no processes are blocking this process). Similarly, you can identify processes that are being blocked by removing the filtering code.
Each output selects relevant fields from the sys.sysprocesses view. The output is displayed as two onscreen grids. The first grid shows which process is the root-blocking spid -- the underlying cause of the blocking. The second grid displays the spids that are being blocked.
The underlying SQL used by the processes is also displayed to enhance the value of the returned data. Examining this SQL can further help highlight why the performance is slow. Make a call to the dba_GetSQLForSpid function to get the underlying SQL. This function accepts one parameter: the SQL Server process ID (@spid) of a running process (see Listing 2).
Obtain the sql_handle field from the sys.sysprocesses system view by using the passed process ID. This field is a handle to the currently running SQL, which you can retrieve by passing the sql_handle to the dynamic management function dm_exec_sql_text.
You create both the stored procedure (dba_BlockTracer) and the function (dba_GetSQLForSpid) in the master database. This is appropriate because they're both database-wide system routines.
This utility works with SQL Server 2005, but you can amend it to work with SQL Server 2000 by changing any references to sys.sysprocesses to master.dbo.sysprocesses. Additionally, comment out the call to the dba_GetSQLForSpid function, or make a call to the fn_get_sql function if you have SQL Server 2000 SP 3 or greater.
Put the Utility to Use
Let's take a look at how the blocking process works. First, open a new window in SQL Server Management Studio. Copy this SQL code into the window, and run it:
-- 1. Create 'work' Database.
CREATE DATABASE IWS_Temp
-- 2.Change to working database.
-- 3. Create 'work' table.
CREATE TABLE [dbo].[tblCountry](
[CountryId] [int] IDENTITY(1,1) NOT NULL,
[Code] [char](3) NOT NULL,
[Description] [varchar](50) NOT NULL,
) ON [PRIMARY]
-- 99. DROP DATABASE IWS_Temp
The SQL code creates a new database (called IWS_TEMP), changes to that database workspace, and creates a table called tblCountry, which has three fields (see Figure 1). Next, open another new window in SQL Server Management Studio, copy this SQL code into it, and run it:
-- Change to working database.
-- 4. Start transaction, and insert a record.
INSERT INTO [dbo].[tblCountry] ([Code], [Description])
This SQL code starts a new transaction and inserts a new record into the tblCountry table. At this point, the transaction is pending and has not been committed or rolled back.
Next, open another new window, copy this SQL code into it, and run it:
-- Change to working database.
-- 5. Query the table tblCountry.
SELECT * FROM [dbo].[tblCountry]
This SQL code selects records from the tblCountry table. Note the status on the bottom left-hand side of this window says "Executing query …" (see Figure 2). This simple query is unable to obtain the records it needs because of the previously created (uncommitted) transaction.
Open another new window, copy the same SQL code, and run it. This creates a second dependent process and allows you to see the relationship between dependent blocked processes. Finally, open another new window, copy this SQL code into it, and run it:
This SQL code runs the stored procedure dba_BlockTracer (see Figure 3). The output shows two grids. The first grid shows that the root-blocking spid is 86; the SQL text shows it is the insert statement. The second grid shows two blocked spids. You can see that spid 122 is blocked by spid 104, which itself is blocked by spid 86, which is the root-blocking spid. Both spid 104 and 122 are trying to select records from the tblCountry table. The spid is given on the status line at the bottom of each window. You can see how the spids relate to their underlying SQL. Finally, issue the rollback associated with the pending transaction. The database should be deleted.
The dba_BlockTracer utility can help you identify the root cause of any blocking easily and quickly. It also allows you to determine which dependent processes are being blocked, together with the underlying SQL text and the databases involved.
You can schedule this utility to run periodically as a SQL Server job and provide details on which processes are blocking/blocked. Inspecting these details can show you what processes might need to be rescheduled -- for example, you might not want to run the PnL batch process at the same time as any Fund Manager processing. It might also show which processes need to have their Isolation Levels investigated, with a view to greater concurrency (read: less blocking). But the utility's main use is to inspect what is happening when the applications/databases seem sluggish.