In this short tutorial, we’ll look at traversing parent-child structures using recursive common table expressions, and turning the data into human-readable lists. This is a great way to represent paths and hierarchy-based data in reports and end-user outputs.
The example data we’re using here is an imaginary blocking chain. In SQL Server, as in practically any database software, a database transaction will create locks on objects that it uses. When an object is locked by one process, any other process that wants to use or modify this object will have to wait for the first process to first let go of the lock. In this context, you would say that the second process is “blocked” by the first process.
You can view a current list of blocking sessions on the server using the following dynamic management view in SQL Server:
SELECT DISTINCT session_id, blocking_session_id, wait_type, wait_duration_ms, ROW_NUMBER() OVER ( PARTITION BY waiting_task_address ORDER BY wait_duration_ms, resource_address) AS dupl FROM sys.dm_os_waiting_tasks ORDER BY session_id;
Hopefully, however, your server won’t contain a lot of blocking transactions, so we’ll create our own working table with some test data.
CREATE TABLE #connections ( spid int NOT NULL, blockedBy int NULL, PRIMARY KEY CLUSTERED (spid) ); --- Example data: INSERT INTO #connections (spid, blockedBy) VALUES (53, NULL), (54, NULL), (55, NULL), (56, 64), (57, NULL), (59, 56), (60, NULL), (61, NULL), (62, NULL), (63, 64), (64, 53), (65, NULL), (66, 56);
Listing recursive relations
Blocking transactions can be recursive. In this example, SPID 63 is blocked by 64, which in turn is blocked by 53. Because of this recursive nature, we’re going to use a recursive common table expression to map all the relations.
All recursive CTEs consist of two parts, an anchor and a recursion. Here, the anchor is a list of first-level (direct) relations. After the UNION ALL section, we’re recursing up through the chain of relations.
--- Table of blocking SPIDs: WITH list (spid, blockedBy, [level]) AS (--- The anchor of the recursive CTE is the first-level relation --- from the SPID to the blocking SPID. SELECT spid, blockedBy, 1 FROM #connections WHERE blockedBy IS NOT NULL UNION ALL --- From here, we'll recurse the blocking SPID's own blocking --- SPIDs, and increment the "level" counter by one. SELECT list.spid, conn.blockedBy, list.[level]+1 FROM list INNER JOIN #connections AS conn ON list.blockedBy=conn.spid WHERE conn.blockedBy IS NOT NULL) SELECT spid, blockedBy, [level] FROM list ORDER BY spid, [level], blockedBy;
Here’s the result:
Turning recursive relations into paths
Finally, we can use the same recursive common table expression to build a string containing the “path” of each SPID’s blocking processes.
WITH list (spid, blockedBy, [level], list) AS (--- The anchor of the recursive CTE is the first-level relation --- from the SPID to the blocking SPID. --- The "list" column is a varchar(100) column that contains an --- ordered path of all the blocking relations. SELECT spid, blockedBy, 1, CAST(blockedBy AS varchar(100)) AS list FROM #connections WHERE blockedBy IS NOT NULL UNION ALL --- From here, we'll recurse the blocking SPID's own blocking --- SPIDs, and increment the "level" counter by one. SELECT list.spid, conn.blockedBy, list.[level]+1, --- Suffix the new level's SPID to the end of the "list" string. CAST(list+' <- '+CAST(conn.blockedBy AS varchar(100)) AS varchar(100)) AS list FROM list INNER JOIN #connections AS conn ON list.blockedBy=conn.spid WHERE conn.blockedBy IS NOT NULL) SELECT spid, list AS blockedByChain FROM list WHERE [level]=(SELECT MAX(sub.[level]) FROM list AS sub WHERE sub.spid=list.spid) ORDER BY spid, [level], blockedBy;
For every recursion of the CTE, we’re building a string containing the blocking path in the “list” column. The WHERE clause at the end is meant to filter only the final product of the recursion, and not all the intermediate steps used to create it.
Here’s the output:
To implement this query on actual blocking data on your server, just populate the temp table from the DMV mentioned in the beginning of the article:
TRUNCATE TABLE #connections; INSERT INTO #connections (spid, blockedBy) SELECT DISTINCT session_id, blocking_session_id FROM sys.dm_os_waiting_tasks;