Master of None

Many third-party tools have  pretty lead blocking graphs and etc. You may not have access to something than grand.  My friend and co-work JD Pellerin wrote this script. He used this as a basis for an SSRS report that can be executed by operations teams to identify potential batch issues. Play around with tweak it to fit your needs.

Sample Blocking Results

Here is the basis of that report and super helpful script to keep around. You could even make your own pretty graphs. Ahhhhh…

-- 
--Custom Blocking Tracer
-- 20150907
-- Developed by J.D. Pellerin 
-- https://www.linkedin.com/in/jpellerin1
--------------------------------------------------------------------------------------------------
--
Declare @DBName Varchar(150)
set @DBName = ''

Declare @sp_who2 Table 
(SPID INT,  
Status VARCHAR(1000) NULL,  
Login SYSNAME NULL,  
HostName SYSNAME NULL,  
BlkBy SYSNAME NULL,  
DBName SYSNAME NULL,  
Command VARCHAR(1000) NULL,  
CPUTime INT NULL,  
DiskIO INT NULL,  
LastBatch VARCHAR(1000) NULL,  
ProgramName VARCHAR(1000) NULL,  
SPID2 INT,
RequestID int) 

INSERT INTO @sp_who2
EXEC msdb..sp_who2

Select 
  s3.SPID
  ,[CommandType] = s3.Command
  ,s3.BlkBy
  ,s3.Status
  ,s3.[Login]
  ,s3.HostName
  ,s3.DBName
  ,CAST(((DATEDIFF(s,er.start_time,GetDate()))/3600) as varchar) + ' hour(s), '
              + CAST((DATEDIFF(s,er.start_time,GetDate())%3600)/60 as varchar) + 'min, '
              + CAST((DATEDIFF(s,er.start_time,GetDate())%60) as varchar) + ' sec' as running_time
  ,CAST((er.estimated_completion_time/3600000) as varchar) + ' hour(s), '
              + CAST((er.estimated_completion_time %3600000)/60000 as varchar) + 'min, '
              + CAST((er.estimated_completion_time %60000)/1000 as varchar) + ' sec' as [MS est_time_to_go]
  ,[Executing Query] = SUBSTRING(st.text,er.statement_start_offset / 2,
                                       ( CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
                                         ELSE er.statement_end_offset  END
                                         - er.statement_start_offset )/ 2)  
  ,[Parent Query]       = st.text
from @sp_who2 s3
left outer join msdb.sys.dm_exec_requests er on er.session_id = s3.SPID
outer APPLY msdb.sys.dm_exec_sql_text(er.sql_handle) as st 
where s3.BlkBy <> '  .'
union all 
Select 
  s3.SPID
  ,[CommandType] = s3.Command
  ,s3.BlkBy
  ,s3.Status
  ,s3.[Login]
  ,s3.HostName
  ,s3.DBName
  ,CAST(((DATEDIFF(s,er.start_time,GetDate()))/3600) as varchar) + ' hour(s), '
              + CAST((DATEDIFF(s,er.start_time,GetDate())%3600)/60 as varchar) + 'min, '
              + CAST((DATEDIFF(s,er.start_time,GetDate())%60) as varchar) + ' sec' as running_time
  ,CAST((er.estimated_completion_time/3600000) as varchar) + ' hour(s), '
              + CAST((er.estimated_completion_time %3600000)/60000 as varchar) + 'min, '
              + CAST((er.estimated_completion_time %60000)/1000 as varchar) + ' sec' as [MS est_time_to_go]
  ,[Executing Query] = SUBSTRING(st.text,er.statement_start_offset / 2,
                                       ( CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
                                         ELSE er.statement_end_offset  END
                                         - er.statement_start_offset )/ 2)  
  ,[Parent Query]       = st.text
from @sp_who2 s3
left outer join msdb.sys.dm_exec_requests er on er.session_id = s3.SPID
outer APPLY msdb.sys.dm_exec_sql_text(er.sql_handle) as st 
where  Cast(s3.SPID as varchar) in (Select Distinct s4.BlkBy from @sp_who2 s4 where isnumeric(s4.BlkBy) = 1)
--and s3.SPID <>@@SPID
ORDER BY 3

I used the following script to simulate the blocking for the above results. I creates and populates a Global Temp Table ( ##BLOCKEREXAMPLE). The second part executes a select statement within a transaction. The commented statements can be run in separate query windows to simulate the blocking.

SET NOCOUNT ON 
/*
Create Blocking statement 
1-9-2016 
jackdonnell.com

Adapted from http://carlosferreira.com/how-to-create-a-blocking-transaction-in-sql-server-t-sql-exclusive-lock/
*/

--Create Global Temp Table
CREATE TABLE ##BLOCKEREXAMPLE (Value VARCHAR(8))
--Populate it 
INSERT INTO ##BLOCKEREXAMPLE (Value)
VALUES ('RED')

BEGIN TRANSACTION -- Create table lock fro blocker
SELECT VALUE FROM dbo.##BLOCKEREXAMPLE WITH (TABLOCKX, HOLDLOCK);

WAITFOR DELAY '00:10:00' -- Waitt 10 minutes 
ROLLBACK TRANSACTION  -- Rollback


IF OBJECT_ID ('##BLOCKEREXAMPLE','U') IS NOT NULL 
BEGIN
DROP TABLE ##BLOCKEREXAMPLE
END


/*
Execute the following in different query windows. The above SELECT statement 
will lock they table and these commands will be blocked as long as the above 
query has not completed.
1-9-2016 
jackdonnell.com
*/

/*
UPDATE ##BLOCKEREXAMPLE
SET Value = 'Orange'

UPDATE ##BLOCKEREXAMPLE
SET Value = 'Green'

UPDATE ##BLOCKEREXAMPLE
SET Value = 'Purple'
*/

Tiny URL for this post:
 Copy