How to gather SQL Server blocking information to troubleshoot blocking issues

Introduction

This script is to gather SQL Server blocking information to troubleshoot blocking issues.

Scenarios

Sometimes, people want to find if there is any block in their SQL Server. So we provide this script to demonstrate how to achieve this goal. We will create a block first and then use this script to find the block information. Please do it in a test environment.

Script

You can use this script in this way:

1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Select the specified database and create a “New Query”, copy the code from session1.sql, paste it, and then create another query and copy code from session2.sql and run the script.

3. Select the specified database again and create a “New Query”, then copy code from GetBlockingInformation.sql and run the script.

Note: Please finish step 2 and step 3 sequentially within two minutes.

After the script finishes running, we’ll get the following figure:

 

 

Here are some code snippets for your reference.

SQL
Edit|Remove
mysql
SELECT dm_owt.wait_duration_ms, 
dm_owt.wait_type, 
dm_es.status, 
dm_est.TEXT, 
dm_owt.session_ID, 
dm_es.cpu_time, 
dm_es.memory_usage, 
dm_es.logical_reads, 
dm_es.total_elapsed_time, 
dm_es.program_name, 
DB_NAME(dm_er.database_id) DatabaseName, 
dm_owt.blocking_session_id, 
dm_er.wait_resource
FROM sys.dm_os_waiting_tasks dm_owt 
INNER JOIN sys.dm_exec_requests dm_er ON dm_owt.session_id = dm_er.session_id 
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_er.session_id 
CROSS APPLY sys.dm_exec_sql_text (dm_er.sql_handle) dm_est 
CROSS APPLY sys.dm_exec_query_plan (dm_er.plan_handle) dm_eqp 
WHERE dm_es.is_user_process = 1 
GO 
 

SELECT dm_owt.wait_duration_ms,  
dm_owt.wait_type,  
dm_es.status,  
dm_est.TEXT,  
dm_owt.session_ID,  
dm_es.cpu_time,  
dm_es.memory_usage,  
dm_es.logical_reads,  
dm_es.total_elapsed_time,  
dm_es.program_name,  
DB_NAME(dm_er.database_id) DatabaseName,  
dm_owt.blocking_session_id,  
dm_er.wait_resource 
FROM sys.dm_os_waiting_tasks dm_owt  
INNER JOIN sys.dm_exec_requests dm_er ON dm_owt.session_id = dm_er.session_id  
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_er.session_id  
CROSS APPLY sys.dm_exec_sql_text (dm_er.sql_handle) dm_est  
CROSS APPLY sys.dm_exec_query_plan (dm_er.plan_handle) dm_eqp  
WHERE dm_es.is_user_process = 1  
GO  
 



 

 

Prerequisites

SQL Server 2005 or higher version