Capture lead blocker\blocking details in SQL 2008 along with SQL Commands exceeding 60 seconds

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s