{"id":589,"date":"2016-01-09T17:09:12","date_gmt":"2016-01-09T23:09:12","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=589"},"modified":"2016-01-11T08:04:07","modified_gmt":"2016-01-11T14:04:07","slug":"blocker-script","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=589","title":{"rendered":"Identify Lead Blocker Script"},"content":{"rendered":"

Many third-party tools have\u00a0 pretty lead blocking graphs and etc. You may not have access to something than\u00a0grand. \u00a0My friend and co-work JD Pellerin <\/span><\/a>wrote this script.\u00a0He 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.<\/p>\n

\"Blocking

Sample Blocking Results<\/p><\/div>\n


\nHere is the basis of that report and super helpful script to\u00a0keep around. You could even make your own pretty graphs. Ahhhhh…<\/p>\n

-- \r\n--Custom Blocking Tracer\r\n-- 20150907\r\n-- Developed by J.D. Pellerin \r\n-- https:\/\/www.linkedin.com\/in\/jpellerin1\r\n--------------------------------------------------------------------------------------------------\r\n--\r\nDeclare @DBName Varchar(150)\r\nset @DBName = ''\r\n\r\nDeclare @sp_who2 Table \r\n(SPID INT,  \r\nStatus VARCHAR(1000) NULL,  \r\nLogin SYSNAME NULL,  \r\nHostName SYSNAME NULL,  \r\nBlkBy SYSNAME NULL,  \r\nDBName SYSNAME NULL,  \r\nCommand VARCHAR(1000) NULL,  \r\nCPUTime INT NULL,  \r\nDiskIO INT NULL,  \r\nLastBatch VARCHAR(1000) NULL,  \r\nProgramName VARCHAR(1000) NULL,  \r\nSPID2 INT,\r\nRequestID int) \r\n\r\nINSERT INTO @sp_who2\r\nEXEC msdb..sp_who2\r\n\r\nSelect \r\n  s3.SPID\r\n  ,[CommandType] = s3.Command\r\n  ,s3.BlkBy\r\n  ,s3.Status\r\n  ,s3.[Login]\r\n  ,s3.HostName\r\n  ,s3.DBName\r\n  ,CAST(((DATEDIFF(s,er.start_time,GetDate()))\/3600) as varchar) + ' hour(s), '\r\n              + CAST((DATEDIFF(s,er.start_time,GetDate())%3600)\/60 as varchar) + 'min, '\r\n              + CAST((DATEDIFF(s,er.start_time,GetDate())%60) as varchar) + ' sec' as running_time\r\n  ,CAST((er.estimated_completion_time\/3600000) as varchar) + ' hour(s), '\r\n              + CAST((er.estimated_completion_time %3600000)\/60000 as varchar) + 'min, '\r\n              + CAST((er.estimated_completion_time %60000)\/1000 as varchar) + ' sec' as [MS est_time_to_go]\r\n  ,[Executing Query] = SUBSTRING(st.text,er.statement_start_offset \/ 2,\r\n                                       ( CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2\r\n                                         ELSE er.statement_end_offset  END\r\n                                         - er.statement_start_offset )\/ 2)  \r\n  ,[Parent Query]       = st.text\r\nfrom @sp_who2 s3\r\nleft outer join msdb.sys.dm_exec_requests er on er.session_id = s3.SPID\r\nouter APPLY msdb.sys.dm_exec_sql_text(er.sql_handle) as st \r\nwhere s3.BlkBy <> '  .'\r\nunion all \r\nSelect \r\n  s3.SPID\r\n  ,[CommandType] = s3.Command\r\n  ,s3.BlkBy\r\n  ,s3.Status\r\n  ,s3.[Login]\r\n  ,s3.HostName\r\n  ,s3.DBName\r\n  ,CAST(((DATEDIFF(s,er.start_time,GetDate()))\/3600) as varchar) + ' hour(s), '\r\n              + CAST((DATEDIFF(s,er.start_time,GetDate())%3600)\/60 as varchar) + 'min, '\r\n              + CAST((DATEDIFF(s,er.start_time,GetDate())%60) as varchar) + ' sec' as running_time\r\n  ,CAST((er.estimated_completion_time\/3600000) as varchar) + ' hour(s), '\r\n              + CAST((er.estimated_completion_time %3600000)\/60000 as varchar) + 'min, '\r\n              + CAST((er.estimated_completion_time %60000)\/1000 as varchar) + ' sec' as [MS est_time_to_go]\r\n  ,[Executing Query] = SUBSTRING(st.text,er.statement_start_offset \/ 2,\r\n                                       ( CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2\r\n                                         ELSE er.statement_end_offset  END\r\n                                         - er.statement_start_offset )\/ 2)  \r\n  ,[Parent Query]       = st.text\r\nfrom @sp_who2 s3\r\nleft outer join msdb.sys.dm_exec_requests er on er.session_id = s3.SPID\r\nouter APPLY msdb.sys.dm_exec_sql_text(er.sql_handle) as st \r\nwhere  Cast(s3.SPID as varchar) in (Select Distinct s4.BlkBy from @sp_who2 s4 where isnumeric(s4.BlkBy) = 1)\r\n--and s3.SPID <>@@SPID\r\nORDER BY 3\r\n<\/pre>\n
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.<\/p>\n
SET NOCOUNT ON \r\n\/*\r\nCreate Blocking statement \r\n1-9-2016 \r\njackdonnell.com\r\n\r\nAdapted from http:\/\/carlosferreira.com\/how-to-create-a-blocking-transaction-in-sql-server-t-sql-exclusive-lock\/\r\n*\/\r\n\r\n--Create Global Temp Table\r\nCREATE TABLE ##BLOCKEREXAMPLE (Value VARCHAR(8))\r\n--Populate it \r\nINSERT INTO ##BLOCKEREXAMPLE (Value)\r\nVALUES ('RED')\r\n\r\nBEGIN TRANSACTION -- Create table lock fro blocker\r\nSELECT VALUE FROM dbo.##BLOCKEREXAMPLE WITH (TABLOCKX, HOLDLOCK);\r\n\r\nWAITFOR DELAY '00:10:00' -- Waitt 10 minutes \r\nROLLBACK TRANSACTION  -- Rollback\r\n\r\n\r\nIF OBJECT_ID ('##BLOCKEREXAMPLE','U') IS NOT NULL \r\nBEGIN\r\nDROP TABLE ##BLOCKEREXAMPLE\r\nEND\r\n\r\n\r\n\/*\r\nExecute the following in different query windows. The above SELECT statement \r\nwill lock they table and these commands will be blocked as long as the above \r\nquery has not completed.\r\n1-9-2016 \r\njackdonnell.com\r\n*\/\r\n\r\n\/*\r\nUPDATE ##BLOCKEREXAMPLE\r\nSET Value = 'Orange'\r\n\r\nUPDATE ##BLOCKEREXAMPLE\r\nSET Value = 'Green'\r\n\r\nUPDATE ##BLOCKEREXAMPLE\r\nSET Value = 'Purple'\r\n*\/\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Many third-party tools have\u00a0 pretty lead blocking graphs and etc. You may not have access to something than\u00a0grand. \u00a0My friend and co-work JD Pellerin wrote this script.\u00a0He used this as a basis for an SSRS report that can be executed …<\/p>\n
Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[273,266,22,351,5],"tags":[375,376,381,303,263,382,377,378,315,249,35,379,295,52,380],"class_list":["post-589","post","type-post","status-publish","format-standard","hentry","category-administration","category-dba","category-sql-server","category-ssms","category-t-sql","tag-block","tag-blocker","tag-blocking","tag-dba","tag-dmv","tag-lead","tag-msdb-sys-dm_exec_requests","tag-msdb-sys-dm_exec_sql_text","tag-query","tag-script","tag-select","tag-sp_who2","tag-sql-server","tag-ssms","tag-waitfor-delay"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/589","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=589"}],"version-history":[{"count":14,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/589\/revisions"}],"predecessor-version":[{"id":615,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/589\/revisions\/615"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=589"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=589"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=589"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}