Blocking Analysis

USE [master] GO /****** Object: Table [dbo].[MG_BlockingAnalysis] Script Date: 07/17/2013 11:19:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MG_BlockingAnalysis]( [WaitMSQty] [int] NOT NULL, [CallingSpId] [smallint] NOT NULL, [CallingUserName] [nvarchar](30) NULL, [CallingProgramName] [nvarchar](40) NULL, [BlockingSpId] [smallint] NULL, [DbName] [nvarchar](128) NULL, [CallingSQL] [varchar](max) NULL, [CallingResourceId] [bigint] NULL, [CallingResourceType] [nvarchar](30) NULL, [CallingRequestMode] [nvarchar](30) NULL, [BlockingSQL] [varchar](max) NULL, [BlockingResourceType] [nvarchar](30) NULL, [BlockingRequestMode] [nvarchar](30) NULL, [BlockingResourceId] [bigint] NULL, [BlockingObjectName] [nvarchar](255) NULL, [BlockingCursorSQL] [nvarchar](max) NULL, [dateadded] [datetime] NULL ) ON [PRIMARY] GO USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create function [dbo].[fn_GetResourceName] (@res_id bigint, @db_id int) returns varchar(255) as begin declare @db_name nvarchar(255)= DB_NAME(@db_id) declare @objname nvarchar(255), @out nvarchar(255) declare @dyn_sql nvarchar(500) = N'SELECT @out = object_name(object_id) FROM ' + @db_name + '.sys.partitions ps1 WHERE ps1.hobt_id = @res_id' exec sp_executesql @dyn_sql, N'@res_id nvarchar(100),@out nvarchar(100) output', @res_id, @out OUTPUT return @out end GO SET ANSI_PADDING OFF GO while 1 = 1 begin insert into master..MG_BlockingAnalysis SELECT er.wait_time AS WaitMSQty , er.session_id AS CallingSpId , LEFT(nt_user_name, 30) AS CallingUserName , LEFT(ces.program_name, 40) AS CallingProgramName , er.blocking_session_id AS BlockingSpId , DB_NAME(er.database_id) AS DbName , CAST(csql.text AS varchar(max)) AS CallingSQL , clck.CallingResourceId , clck.CallingResourceType , clck.CallingRequestMode , CAST(bsql.text AS varchar(max)) AS BlockingSQL , blck.BlockingResourceType , blck.BlockingRequestMode , blck.BlockingResourceId , blck.BlockingObjectName , bng_txt.text as BlockingCursorSQL , GETDATE() as dateadded --into master..MG_BlockingAnalysis FROM master.sys.dm_exec_requests er WITH (NOLOCK) JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK) ON er.session_id = ces.session_id CROSS APPLY fn_get_sql (er.sql_handle) csql JOIN ( -- Retrieve lock information for calling process, return only one record to -- report information at the session level SELECT cl.request_session_id AS CallingSpId , MIN(cl.resource_associated_entity_id) AS CallingResourceId , MIN(LEFT(cl.resource_type, 30)) AS CallingResourceType , MIN(LEFT(cl.request_mode, 30)) AS CallingRequestMode -- (i.e. schema, update, etc.) FROM master.sys.dm_tran_locks cl WITH (nolock) WHERE cl.request_status = 'WAIT' -- Status of the lock request = waiting GROUP BY cl.request_session_id ) AS clck ON er.session_id = clck.CallingSpid JOIN ( -- Retrieve lock information for blocking process -- Only one record will be returned (one possibility, for instance, -- is for multiple row locks to occur) SELECT bl.request_session_id AS BlockingSpId , bl.resource_associated_entity_id AS BlockingResourceId ,CASE WHEN bl.resource_type = 'OBJECT' THEN OBJECT_NAME(bl.resource_associated_entity_id) WHEN bl.resource_type IN ('KEY', 'PAGE', 'RID') THEN dbo.fn_GetResourceName(bl.resource_associated_entity_id, DB_ID()) ELSE 'n.a.' END AS BlockingObjectName , MIN(LEFT(bl.resource_type, 30)) AS BlockingResourceType , MIN(LEFT(bl.request_mode, 30)) AS BlockingRequestMode FROM master.sys.dm_tran_locks bl WITH (nolock) GROUP BY bl.request_session_id , bl.resource_associated_entity_id ,CASE WHEN bl.resource_type = 'OBJECT' THEN OBJECT_NAME(bl.resource_associated_entity_id) WHEN bl.resource_type IN ('KEY', 'PAGE', 'RID') THEN dbo.fn_GetResourceName(bl.resource_associated_entity_id, DB_ID()) ELSE 'n.a.' END ) AS blck ON er.blocking_session_id = blck.BlockingSpId AND clck.CallingResourceId = blck.BlockingResourceId JOIN master.sys.dm_exec_connections ber WITH (NOLOCK) ON er.blocking_session_id = ber.session_id CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql --Get the sql code if it is 'hidden' by a JDBC \ Cursor FETCH API call outer apply sys.dm_exec_cursors (er.blocking_session_id) bng outer apply sys.dm_exec_sql_text (bng.sql_handle) bng_txt WHERE ces.is_user_process = 1 AND er.wait_time > 0 waitfor delay '00:00:03' end