Blocking sessions in SQL Server

The query given below is very handy to find blocking sessions in sql server.

SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
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)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
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
GO
DROP TABLE #T
GO

The result shows a tree structure where the parent blocking session can be easily identified.

To know more about deadlocks click here

Troubleshooting Performance Issues in SQL Server

There are many reasons for SQL Server slowdown. They can be broadly classified into three types to start our investigation.

1. Resource Bottleneck – CPU, Memory, I/O.  Check tools used for troubleshooting
2. Tempdb Bottlenck – There is only one tempdb for a SQL Server instance. If there is an application that overloads tempdb through excessive DML/DDL operations it can affect other processes on the server.
3. Slow running User Query – Performance of existing queries can degrade or a new query can be taking longer than expected to complete. Statistics/ Indexes / Locking / Blocking / Deadlocks/ Schema design / Isolation Level are factors that can affect the slowdown.