SQL Server Blocking Report

This post can help a SQL DBA / Developer to quickly find the SQL Server Blocking Report using T-SQL scripts. There are various ways to get the SQL Server Blocking Report but here we are going to discuss only on T-SQL scripts which can be handy for any DBA or Developer to quickly get the details. Usually we need not worry about the process blocking when it occurs occasionally for a short time periods but we can consider it’s as a critical problem when the same issue repeating multiple times or blocking exists for a long time. Below are the different ways to identify and retrieve SQL Server Blocking Report:

• System Stored Procedures
• DMV
• Activity Monitor
• SSMS Reports
• SQL Server Profiler

Here is the link for the detailed information.

Script to Get SQL Server Blocking Report:

Execute the below script, capture the details and analyze these details to identify the root cause then we can provide the resolution based on the root cause.

SELECT
	CASE WHEN 
	CAST( LTRIM(RTRIM(s.blocked)) AS SMALLINT) > 0 
	THEN 'Waiting / Blocked' 
	ELSE 'Blocking' End 		AS 'Process_Type',
	LTRIM(RTRIM(s.spid))		AS 'SPID',
	LTRIM(RTRIM(s.blocked))		AS 'Blocked',
	LTRIM(RTRIM(s.cpu))		AS 'CPU', 
	db_name(LTRIM(RTRIM(s.dbid)))	AS 'DBName',
	LTRIM(RTRIM(s.login_time))	AS 'Login_Time', 
	LTRIM(RTRIM(s.last_batch))	AS 'Last_Batch',
	LTRIM(RTRIM(s.status))		AS 'Status',
	LTRIM(RTRIM(s.loginame))	AS 'LoginName', 
	LTRIM(RTRIM(s.hostname))	AS 'HostName', 
	LTRIM(RTRIM(s.program_name))	AS 'ProgramName',
	LTRIM(RTRIM(s.cmd))		AS 'CMD',
	LTRIM(RTRIM(EST.TEXT))		AS 'Full_Query',
	LTRIM(RTRIM(s.waittime))	AS 'Wait_Time',
	LTRIM(RTRIM(s.lastwaittype))	AS 'Wait_Type',
	LTRIM(RTRIM(s.waitresource))	AS 'Wait_Resource',
	LTRIM(RTRIM(s.cpu))		AS 'CPU_Time(MS)',
	LTRIM(RTRIM(s.physical_io))	AS 'Disk R/W',
	LTRIM(RTRIM(s.memusage))	AS 'Mem_Usage(Total_Pages)',
	LTRIM(RTRIM(s.open_tran))	AS 'NoOfOpenTran',
	LTRIM(RTRIM(s.nt_domain))	AS 'Windows_Domain',
	LTRIM(RTRIM(s.nt_username))	AS 'Windows_UserName'
FROM	sys.sysprocesses s
	CROSS APPLY sys.dm_exec_sql_text(s.sql_handle)EST
WHERE	spid in(select spid from sys.sysprocesses where blocked<>0) or
	spid in(select blocked from sys.sysprocesses);

Script to Get SQL Server Blocking Report – Detailed:

Above script can get the maximum information. But sometimes we can see that the blocking might occur between two stored procedures. In that case the above script returns the entire stored procedure code instead of the exact query. Below script can help us in identifying the exact SQL Statement that is blocked / blocking from the stored procedure.

SELECT 
	CASE WHEN 
	CAST( LTRIM(RTRIM(s.blocked)) AS SMALLINT) > 0 
	THEN 'Waiting / Blocked' 
	ELSE 'Blocking' End 		AS 'Process_Type',
	LTRIM(RTRIM(s.spid))		AS 'SPID',
	LTRIM(RTRIM(s.blocked))		AS 'Blocked',
	db_name(LTRIM(RTRIM(s.dbid)))	AS 'DBName',
	LTRIM(RTRIM(s.login_time))	AS 'Login_Time', 
	LTRIM(RTRIM(s.last_batch))	AS 'Last_Batch',
	LTRIM(RTRIM(s.status))		AS 'Status',
	LTRIM(RTRIM(s.loginame))	AS 'LoginName', 
	LTRIM(RTRIM(s.hostname))	AS 'HostName', 
	LTRIM(RTRIM(s.program_name))	AS 'ProgramName',
	LTRIM(RTRIM(s.cmd))		AS 'CMD',
	ER.[statement_start_offset]	AS 'Statement_Start',
	ER.[statement_end_offset]	AS 'Statement_End',
	EST.TEXT			AS 'Full_Query',	
	CASE
	 WHEN ER.[statement_start_offset] > 0 THEN
		CASE ER.[statement_end_offset]  
		   WHEN -1 THEN  
			  SUBSTRING(EST.TEXT, (ER.[statement_start_offset]/2) + 1, 2147483647) 
		   ELSE   
			  SUBSTRING(EST.TEXT, (ER.[statement_start_offset]/2) + 1, (ER.[statement_end_offset] - ER.[statement_start_offset])/2)   
		END  
	 ELSE  
		CASE ER.[statement_end_offset]  
		   WHEN -1 THEN  
			  RTRIM(LTRIM(EST.[text]))  
		   ELSE  
			  LEFT(EST.TEXT, (ER.[statement_end_offset]/2) +1)  
		END  
	END				AS 'Exact_Statement',
	LTRIM(RTRIM(s.waittime))	AS 'Wait_Time',
	LTRIM(RTRIM(s.lastwaittype))	AS 'Wait_Type',
	LTRIM(RTRIM(s.waitresource))	AS 'Wait_Resource',
	LTRIM(RTRIM(s.cpu))		AS 'CPU_Time(MS)',
	LTRIM(RTRIM(s.physical_io))	AS 'Disk R/W',
	LTRIM(RTRIM(s.memusage))	AS 'Mem_Usage(Total_Pages)',
	LTRIM(RTRIM(s.open_tran))	AS 'NoOfOpenTran',
	LTRIM(RTRIM(s.nt_domain))	AS 'Windows_Domain',
	LTRIM(RTRIM(s.nt_username))	AS 'Windows_UserName'
FROM	sys.dm_exec_requests ER 
	CROSS APPLY sys.dm_exec_sql_text(ER.[sql_handle]) EST  
	INNER JOIN sys.sysprocesses s ON ER.session_id = s.spid
WHERE	ER.session_id IN
	(SELECT spid FROM sys.sysprocesses WHERE blocked<>0
	UNION
	SELECT blocked FROM sys.sysprocesses) 
ORDER BY	ER.[session_id], ER.[request_id];

Here is the Script File: sql_server_blocking_report