There are many blog posts on the internet that have explained time and again regarding this topic. Many of them are pretty basic stuff while some of them are quite advanced. A while back I myself wrote a blog post explaining how to find blocking sessions in Oracle database. These posts usually talk about activity monitor, sp_who2, sysprocesses, etc.
A simple query to provide us information regarding locks in SQL Server would look like this
SELECT * FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL
This code block is quite helpful because looking carefully at the query output tells us who is waiting for whom which lets us know exactly what is to be done for the lock to be removed.
Although this query is already sufficient in itself, I wanted a query output which would help to understand the locks visually for all sorts of stakeholders, when I as a DBA, am requested for such information. And even before I could write such query I found a blog post from Pinal Dave having such a query which is shown below
SET NOCOUNT ON
SELECT SPID, BLOCKED, REPLACE (REPLACE(T.TEXT, CHAR(10), ‘ ‘), CHAR (13), ‘ ‘ ) AS BATCH
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
CAST (REPLICATE (‘0′, 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED = R2.SPID)
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 +R.SPID) AS VARCHAR (100)), 4) AS VARCHAR(1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED =BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED = R.SPID
SELECT N’ ‘ + REPLICATE (N’| ‘,LEN (LEVEL)/4 – 1) +
CASE WHEN (LEN(LEVEL)/4 – 1) = 0
THEN ‘HEAD – ‘
ELSE ‘|—— ‘ END
+ CAST (SPID AS NVARCHAR (10)) + N’ ‘ + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
DROP TABLE #T
The query output from the above code block gives a visual representation of the block that is present in the database which can be called a blocking tree. This piece of code is my daily go to code for finding locks and their respective locking sessions in the database.
Let me know what codes or tools do you use to find locks in your database.