Blocking occurs in SQL Server 2005 when one process has one or more records locked, while another process tries to acquire locks on the same set (or subset) of records. This causes the second process to wait until the first process is done so it can acquire the records; this can cause other processes to be blocked if they are waiting on the resources that the second connection has acquired. These processes are ultimately dependent upon the first process to complete its work before they can complete theirs. This can create a daisy-chain of processes waiting to complete their work.

This blocking situation is a bit different than a deadlock scenario. A deadlock occurs when process A has ownership of resources that process B needs. Process B, in turn, owns resources that process A needs to complete its work. In theory, this would last forever. Fortunately, SQL Server 2005 has algorithms to find these scenarios and stop one process so the other can finish its work. On the other hand, blocking can last a lot longer than a deadlock because it isn’t stopped automatically by SQL Server 2005. The blocked processes must wait until the blocking connection finishes its work before they can finish theirs.

Finding blocking connections

SQL Server 2005 has several system tables and views that can provide a ton of insight into blocking problems that you may be facing in your database environment.

Through these procedures, you can find blocking chains. I define a blocking chain as one particular process is blocking one or more processes, and those processes are blocking other processes. These blocking chains happen all the time, especially when there are instances of long-running transactions. Many times, I have seen a DBA find the process that is causing the most locking and stop the process. This is usually not a great idea, and it is definitely not the way to find the root of the problem. The processes that cause the most locking are usually doing so because they are being blocked by another process.

The following statement pulls information from the sys.sysprocesses system table for user processes. The information is placed into a temporary table named #Processes that I will use for my recursive Common Table Expression (CTE) query. The sys.sysprocesses table is a great tool because it contains a lot of data in one table. (The sys.sysprocesses table may not be included in future versions of SQL Server.) I can also use the CROSS APPLY construct to get the actual SQL statement that is being used based upon the sql_handle value in the table.

SELECT

            s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),


            s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid,                      s.dbid), Definition = CAST(text AS VARCHAR(MAX))


INTO        #Processes


FROM      sys.sysprocesses s


CROSS APPLY sys.dm_exec_sql_text (sql_handle)


WHERE

           s.spid > 50

The following statement declares and uses the recursive CTE for finding blocking chains in the temp table I just created for the sys.sysprocesses table.

The WITH statement declares the CTE with the code inside of the parentheses being the body of the expression. In the first SELECT statement, I am finding all processes that are not blocked themselves but are blocking at least one other process. To do this, I just join the #Processes temp table to itself based on the SPID field and the BlockingSPID field. This is a crucial step in the query, as I am locating the starting point for finding the chain of processes that are being blocked.

This first SELECT can essentially be thought of as a “base case,” which will act as a root for the rest of the recursion. This SELECT has a couple of functions that will allow me to tell the hierarchy of the recursion. The RowNo field uses the ROW_NUMBER function which will indicate the separate number of processes that are blocking other processes. The LevelRow, which I initially declare as 0, will indicate the recursion level for that particular blocking process. (This information will make more sense later in the article when I give an example of the information that is provided by the CTE.)

The next SELECT statement after UNION ALL (there always has to be a UNION ALL in a recursive CTE) is the recursive section; it joins what is returned by the CTE and the #Processes temp table based upon the BlockingSPID from the temp table and the SPID field from the CTE. For this statement, I am only concerned with the blocked statements.

The last SELECT statement runs the recursive CTE and returns the values.

WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow)

AS


(


     SELECT


      s.SPID, s.BlockingSPID, s.Definition,


      ROW_NUMBER() OVER(ORDER BY s.SPID),


      0 AS LevelRow


    FROM


      #Processes s


      JOIN #Processes s1 ON s.SPID = s1.BlockingSPID


    WHERE


      s.BlockingSPID = 0


    UNION ALL


    SELECT


      r.SPID,  r.BlockingSPID, r.Definition,


      d.RowNo,


      d.LevelRow + 1


    FROM


      #Processes r


     JOIN Blocking d ON r.BlockingSPID = d.SPID


    WHERE


      r.BlockingSPID > 0


)


SELECT * FROM Blocking

ORDER BY RowNo, LevelRow

Analyzing the sample code

Figure A shows an example of a potential output by the recursive CTE declared above. In the output, there are two processes blocking other processes; this is identified in a couple of ways. First, there are two records that have a 0 in the BlockingSPID field. These records will have the value 0 in the LevelRow field; they are also the “base case” for the recursion. The RowNo field indicates the separate set of blocking groups. The first set of statements that are blocked have the value 1 for the RowNo field, the second set of statements that are blocked have the value 2 for the RowNo field, etc.
Figure A

In analyzing the first group of blocked statements, I see that procedure usp_StoredProcedure1 is the initial point of blocking — that procedure is blocking usp_StoredProcedure2. The LevelRow field can be used here, or I can try to match the SPID in one row to the BlockingSPID value in the next row. I find that the LevelRow field is the easier of the two choices. I also see the following:

  • usp_StoredProcedure2 is blocking usp_StoredProcedure3.
  • usp_StoredProcedure3 is blocking usp_StoredProcedure4.
  • usp_StoredProcedure 4 is blocking two stored procedures: usp_StoredProcedure5 and usp_StoredProcedure14. The LevelRow value is the same for these two rows.
  • The blocking analysis concludes for RowNo 1 with usp_StoredProcedure5 blocking the final statement usp_StoredProcedure6.

Using this tool

There are two very useful ways to use this tool for finding blocking on your SQL Server 2005 system. One way is to run it every couple of minutes and store the results in a table for later analysis. This is very handy if you encounter blocking problems during the night because it allows you to find the problem the next day. The other way is to wrap the functionality up into a system stored procedure that you can easily call anytime if someone is complaining about sessions timing out or the database engine running slow.

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.


—————————————————————————————–

Get database tips in your inbox

TechRepublic’s free Database Management newsletter, delivered each Tuesday, contains hands-on SQL Server and Oracle tips and resources. Automatically subscribe today!