It is very hard to find good and bad blocking, in case you need to capture blocking in your database which is exceeding a specific timeframe than below mentioned script can be used.
Step 1 Create Table to store blocking information
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CatchBlocking](
[CaptureDate] [datetime] NULL,
[session id] [smallint] NULL,
[login] [nvarchar](256) NULL,
[database] [nvarchar](256) NULL,
[task state] [nvarchar](120) NULL,
[command] [nvarchar](32) NULL,
[application] [nvarchar](256) NULL,
[head blocker] [varchar](1) NULL,
[login time] [datetime] NULL,
[last request start time] [datetime] NULL,
[monitoring time] [datetime] NULL,
[blocked minutes] [nvarchar](256) NULL,
[host name] [nvarchar](256) NULL,
[text] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Step 2 Insert the below code in a job step which will execute after every minute
DECLARE @loginname sysname,
@databasename sysname,
@hostname sysname,
@userprocessesonly char(1),
@headblocker nvarchar(1),
@minutesofblocking int,
@monitortime datetime
— Filter your monitoring here!
—————————————–
SET @loginname = ”
SET @databasename = ”
SET @hostname = ”
SET @userprocessesonly = ‘Y’ –Y
SET @headblocker = ‘1’ — 1
SET @minutesofblocking = ’60’ — 1
–Blocking can be captured in terms of minutes and seconds, below in the script use ss\minutes accordingly to
–capture blocking in minutes\seconds
—————————————–
SET @monitortime = getdate()
INSERT INTO dbo.CatchBlocking
SELECT GETDATE(),
[session id] = s.session_id,
— [User Process] = CONVERT(CHAR(1), s.is_user_process),
[login] = s.login_name,
[database] = isnull(db_name(p.dbid),
N”),
[task state] = isnull(t.task_state,
N”),
[command] = isnull(r.command,
N”),
[application] = isnull(s.program_name,
N”),
[head blocker] = CASE
— session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND
(r.blocking_session_id = 0 OR
r.session_id IS NULL) THEN ‘1’
— session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ”
END,
[login time] = s.login_time,
[last request start time] = s.last_request_start_time,
[monitoring time] = @monitortime,
[blocked minutes] = cast(datediff(ss,s.last_request_start_time, @monitortime) AS int),
[host name] = isnull(s.host_name,
N”),
st.text AS ‘Text’
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_connections c
ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r
ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t
ON (r.session_id = t.session_id AND
r.request_id = t.request_id)
LEFT OUTER JOIN (
— In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
— waiting for several different threads. This will cause that thread to show up in multiple rows
— in our grid, which we don’t want. Use ROW_NUMBER to select the longest wait for each thread,
— and use it as representative of the other wait relationships this thread is involved in.
SELECT *,
row_number()
OVER (
partition BY waiting_task_address
ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks) w
ON (t.task_address = w.waiting_task_address) AND
w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2
ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g
ON (g.group_id = s.group_id)
LEFT OUTER JOIN sys.sysprocesses p
ON (s.session_id = p.spid)
CROSS apply sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE
P.dbid in (select dbid from sys.sysdatabases where name like ‘%IDEA%’)
AND
(@loginname = ” OR
s.login_name = @loginname) AND
(@databasename = ” OR
isnull(db_name(p.dbid),
N”) = @databasename) AND
(@hostname = ” OR
@hostname = isnull(s.host_name,
N”)) AND
(s.is_user_process = CASE
WHEN upper(@userprocessesonly) = ‘Y’ THEN 1
ELSE s.is_user_process
END) AND
(@headblocker = ” OR
@headblocker = CASE
— session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND
(r.blocking_session_id = 0 OR
r.session_id IS NULL) THEN ‘1’
— session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ”
END) AND
datediff(ss,s.last_request_start_time,@monitortime) >= @minutesofblocking AND
(st.text NOT LIKE ‘%BACKUP%’ AND st.text NOT LIKE ‘%RESTORE%’)
ORDER BY s.session_id
— If there is an unreported head blocker session let’s send a new mail.
IF EXISTS (SELECT hbs1.[session id]
FROM dbo.CatchBlocking hbs1
WHERE cast(hbs1.[monitoring time] AS nvarchar(256)) = cast(@monitortime AS nvarchar(256)) AND
hbs1.[session id] NOT IN (SELECT hbs2.[session id]
FROM dbo.CatchBlocking hbs2
WHERE cast(hbs2.[monitoring time] AS nvarchar(256)) < cast(@monitortime AS nvarchar(256)) AND
hbs2.[session id] = hbs1.[session id] AND
hbs2.[last request start time] = hbs1.[last request start time] AND
hbs2.text = hbs1.text))
select * from dbo.CatchBlocking
GO
coresql.in 