Many a times Application team report SQL Server Blocking issues. In this article I will explain about Blocking and Its cause. Due to this blocking issue Application team asks the DBA team to capture the code which is causing the blocking. SQL Server profiler is one of the tool through which we can capture the SQL Statements running at the database server while users are accessing the application but as you all know that this tool is highly resource intensive and it’s not advised to run it specially in the production hours. I will go through a work around to setup and capture the SQL Server queries to identify the blocking issue and resolve it.

What is blocking?
“Blocking” occurs when one user connection is holding lock on an object or resource and at the same time another user connection tries to read or write on the same resource or objects.
This is a kind of scenario when a baby is holding a ball and he\she is blocking other babies from holding the same ball.
This is how the output of sp_who2 will look like in case there is any kind of blocking on the database server.

SQL Server Blocking

In the above example SPID 54 is blocking SPID ID 56.

We can schedule a SQL Server job to capture the blocking SPID’s SQL statements and troubleshoot the blocking issue.

Step 1: Create the stored procedure to capture the blocking information and wait types using below script.

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp_blocker_pss08]    Script Date: 08/12/2016 07:34:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp_blocker_pss08] (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')
as 
--version 19.2005 - 2005 or Later
if is_member('sysadmin')=0 
begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end

set nocount on
SET LANGUAGE 'us_english'
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @dbname nvarchar(128)
declare @status sql_variant
declare @useraccess sql_variant
declare @request varchar(12)

set @time = getdate()
declare @probclients table(spid smallint, request_id int, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,
   ignore_app tinyint, primary key (blocked, spid, request_id, ecid))
insert @probclients select spid, request_id, ecid, blocked, waittype, dbid,
   case when convert(varchar(128),hostname) = @appname then 1 else 0 end
   from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1)
begin
   set @time2 = getdate()
   print ''
   print '9.0 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.2005 '+ltrim(str(@latch))+' '+ltrim(str(@fast)) 

   insert @probclients select distinct blocked, 0, 0, 0, 0x0000, 0, 0 from @probclients
      where blocked not in (select spid from @probclients) and blocked != 0

   if (@fast = 1)
   begin
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage, last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121),net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid,
         sql_handle, stmt_start, stmt_end, request_id
      from master.dbo.sysprocesses
      where blocked!=0 or waittype != 0x0000
         or spid in (select blocked from @probclients where blocked != 0)
         or spid in (select spid from @probclients where blocked != 0)

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, request_id, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         print ''
         print 'SPIDs at the head of blocking chains'
         select distinct spid from @probclients -- change: added distinct
            where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
         if @latch = 0 and exists (select spid from @probclients where waittype between 0x0001 and 0x0017) -- Change: exists
         begin
            print 'SYSLOCKINFO'
            select @time2 = getdate()

            select spid = convert (smallint, req_spid),
               ecid = convert (smallint, req_ecid),
               rsc_dbid As dbid,
               rsc_objid As ObjId,
               rsc_indid As IndId,
               Type = case rsc_type when 1 then 'NUL'
                                    when 2 then 'DB'
                                    when 3 then 'FIL'
                                    when 4 then 'IDX'
                                    when 5 then 'TAB'
                                    when 6 then 'PAG'
                                    when 7 then 'KEY'
                                    when 8 then 'EXT'
                                    when 9 then 'RID'
                                    when 10 then 'APP' end,
               Resource = substring (rsc_text, 1, 16),
               Mode = case req_mode + 1 when 1 then NULL
                                        when 2 then 'Sch-S'
                                        when 3 then 'Sch-M'
                                        when 4 then 'S'
                                        when 5 then 'U'
                                        when 6 then 'X'
                                        when 7 then 'IS'
                                        when 8 then 'IU'
                                        when 9 then 'IX'
                                        when 10 then 'SIU'
                                        when 11 then 'SIX'
                                        when 12 then 'UIX'
                                        when 13 then 'BU'
                                        when 14 then 'RangeS-S'
                                        when 15 then 'RangeS-U'
                                        when 16 then 'RangeIn-Null'
                                        when 17 then 'RangeIn-S'
                                        when 18 then 'RangeIn-U'
                                        when 19 then 'RangeIn-X'
                                        when 20 then 'RangeX-S'
                                        when 21 then 'RangeX-U'
                                        when 22 then 'RangeX-X'end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end,
               req_transactionID As TransID, req_transactionUOW As TransUOW
            from master.dbo.syslockinfo s,
               @probclients p
            where p.spid = s.req_spid
               --and ((p.waittype between 0x0001 and 0x0017) or ()) --change: added line

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end
      else
         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
      print ''
   end  -- fast set

   else  
   begin  -- Fast not set
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage, last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121),net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid,
         sql_handle, stmt_start, stmt_end, request_id
      from master.dbo.sysprocesses

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, request_id, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         print ''
         print 'SPIDs at the head of blocking chains'
         select spid from @probclients
         where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
         if @latch = 0
         begin
            print 'SYSLOCKINFO'
            select @time2 = getdate()

            select spid = convert (smallint, req_spid),
               ecid = convert (smallint, req_ecid),
               rsc_dbid As dbid,
               rsc_objid As ObjId,
               rsc_indid As IndId,
               Type = case rsc_type when 1 then 'NUL'
                                    when 2 then 'DB'
                                    when 3 then 'FIL'
                                    when 4 then 'IDX'
                                    when 5 then 'TAB'
                                    when 6 then 'PAG'
                                    when 7 then 'KEY'
                                    when 8 then 'EXT'
                                    when 9 then 'RID'
                                    when 10 then 'APP' end,
               Resource = substring (rsc_text, 1, 16),
               Mode = case req_mode + 1 when 1 then NULL
                                        when 2 then 'Sch-S'
                                        when 3 then 'Sch-M'
                                        when 4 then 'S'
                                        when 5 then 'U'
                                        when 6 then 'X'
                                        when 7 then 'IS'
                                        when 8 then 'IU'
                                        when 9 then 'IX'
                                        when 10 then 'SIU'
                                        when 11 then 'SIX'
                                        when 12 then 'UIX'
                                        when 13 then 'BU'
                                        when 14 then 'RangeS-S'
                                        when 15 then 'RangeS-U'
                                        when 16 then 'RangeIn-Null'
                                        when 17 then 'RangeIn-S'
                                        when 18 then 'RangeIn-U'
                                        when 19 then 'RangeIn-X'
                                        when 20 then 'RangeX-S'
                                        when 21 then 'RangeX-U'
                                        when 22 then 'RangeX-X'end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end,
               req_transactionID As TransID, req_transactionUOW As TransUOW
            from master.dbo.syslockinfo

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end
      else
        print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
      print ''
   end -- Fast not set

   print 'sys.dm_os_wait_stats'
   select * from sys.dm_os_wait_stats where waiting_tasks_count > 0
   print 'OWS'

   Print ''
   Print '*********************************************************************'
   Print 'Print out SQL Statements for all blocked or blocking spids.'
   Print '*********************************************************************'

   declare ibuffer cursor fast_forward for
   select distinct cast (spid as varchar(6)) as spid, cast (request_id as varchar(12)) as request_id
   from @probclients
   where (spid <> @@spid) and (spid > 50) and
      ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))
      or spid in (select blocked from @probclients where blocked != 0))
   open ibuffer
   fetch next from ibuffer into @spid, @request
   while (@@fetch_status != -1)
   begin
      print ''
      --print 'DBCC INPUTBUFFER FOR SPID ' + @spid +'('+@request+')'
      print 'SQL Statement running FOR SPID ' + @spid +'('+@request+')'
      --exec ('dbcc inputbuffer (' + @spid + ',' + @request +')')
	  SELECT
		p.spid AS [SPID],
		--ltrim(rtrim(db_name(p.dbid))) AS [DBName],
		CASE WHEN txt.encrypted = 0 THEN txt.text ELSE N'encrypted' END AS [Query] ,
		p.login_time AS [Start Time],
		p.last_batch AS [Last Batch]
		FROM sys.sysprocesses p
		INNER JOIN sys.dm_exec_connections c (NOLOCK)
		ON c.session_id = p.spid
		cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) as txt
		WHERE p.spid = @spid

      fetch next from ibuffer into @spid, @request
   end
   deallocate ibuffer

   Print ''
   Print '*******************************************************************************'
   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'
   Print '*******************************************************************************'
   declare ibuffer cursor fast_forward for
   select distinct cast (dbid as varchar(6)) from @probclients
   where dbid != 0
   open ibuffer
   fetch next from ibuffer into @spid
   while (@@fetch_status != -1)
   begin
      print ''
      set @dbname = db_name(@spid)
      set @status = DATABASEPROPERTYEX(@dbname,'Status')
      set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')
      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
      if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'
         dbcc opentran(@dbname)
      else
         print 'Skipped: Status=' + convert(nvarchar(128),@status)
            + ' UserAccess=' + convert(nvarchar(128),@useraccess)

      print ''
      if @spid = '2' select @blocked = 'Y'
      fetch next from ibuffer into @spid
   end
   deallocate ibuffer
   if @blocked != 'Y' 
   begin
      print ''
      print 'DBCC OPENTRAN FOR DBID  2 [tempdb]'
      dbcc opentran ('tempdb')
   end

   print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' ' 
     + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.2005'

GO

Note: By default this stored procedure will be created in master database, if you want you can change the database to some other user database. Also this stored procedure will display information about the wait types as well.
once the stored procedure is created you can schedule a job to run it using a batch file and capture the output in a text file as below.

Step 2: Create a batch file using below content and save it on the server.

blocker_script_batch_file

rem @echo off
set hh=%time:~0,2%
if "%time:~0,1%"==" " set hh=0%hh:~1,1%
set yymmdd_hhmmss=%date:~10,4%%date:~4,2%%date:~7,2%_%hh%%time:~3,2%%time:~6,2%echo %2
sqlcmd -S%1 -E -w2000 -Q"exec master.dbo.sp_blocker_pss80" >>D:\blocker_check_script_%1_%yymmdd_hhmmss%.out

Step 3: Schedule a job to run the above batch file and pass the SQL Server instance name in the job. You can schedule it to run it every minute or 5 minute as per the requirement.

Note: Keep an eye on the disk space on the location where the blocking script output is stored and if required purge or move the old files to some other location.
This is how the output of the blocker script will look like and it will capture the SQL Statements along with other information.

Sample Output:
9.0 Start time: 2016-08-12 07:20:24.363 0 19.2005 1 1
 
SYSPROCESSES USER-PC\TECHNO  171050560
spid   status                         blocked open_tran waitresource                                                                                                                                                                                                                                                     waittype waittime             cmd              lastwaittype                     cpu         physical_io          memusage    last_batch                 login_time                 net_address  net_library  dbid   ecid   kpid   hostname                                                                                                                         hostprocess loginame                                                                                                                         program_name                                                                                                                     nt_domain                                                                                                                        nt_username                                                                                                                      uid    sid                                                                                      sql_handle             stmt_start  stmt_end    request_id 
------ ------------------------------ ------- --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- -------------------- ---------------- -------------------------------- ----------- -------------------- ----------- -------------------------- -------------------------- ------------ ------------ ------ ------ ------ -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------ ---------------------------------------------------------------------------------------- ---------------------- ----------- ----------- -----------
     2 background                           0         0                                                                                                                                                                                                                                                                  0x0158                  27134 XE TIMER         XE_TIMER_EVENT                             0                    0           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   0      0   2456                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
     3 background                           0         0                                                                                                                                                                                                                                                                  0x015A                7738805 XE DISPATCHER    XE_DISPATCHER_WAIT                         0                    0           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   0      0   2180                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
     4 background                           0         0                                                                                                                                                                                                                                                                  0x0060                    297 LAZY WRITER      LAZYWRITER_SLEEP                         546                    0           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   0      0   1840                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
     5 background                           0         0                                                                                                                                                                                                                                                                  0x007F                  97152 LOG WRITER       LOGMGR_QUEUE                               0                    0           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   0      0    312                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
     6 background                           0         0                                                                                                                                                                                                                                                                  0x0080                   1460 LOCK MONITOR     REQUEST_FOR_DEADLOCK_SEARCH                0                    0           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   0      0   1860                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
     7 background                           0         0                                                                                                                                                                                                                                                                  0x009D                7731875 SIGNAL HANDLER   KSOURCE_WAKEUP                             0                    0           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   1      0   4352                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
     9 background                           0         0                                                                                                                                                                                                                                                                  0x00A2                    796 TRACE QUEUE TASK SQLTRACE_INCREMENTAL_FLUSH_SLEEP           0                    0           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   1      0   2540                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
    10 background                           0         0                                                                                                                                                                                                                                                                  0x015E                    526 BRKR TASK        BROKER_TO_FLUSH                            0                    0           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   1      0   1284                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
    12 background                           0         0                                                                                                                                                                                                                                                                  0x007E                7736829 TASK MANAGER     ONDEMAND_TASK_QUEUE                        0                    0           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   1      0    956                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
    13 background                           0         0                                                                                                                                                                                                                                                                  0x0081                  74097 CHECKPOINT       CHECKPOINT_QUEUE                           0                    4           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   2      0   4332                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
    14 background                           0         0                                                                                                                                                                                                                                                                  0x00AE                7732213 BRKR EVENT HNDLR BROKER_EVENTHANDLER                       31                   21           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   1      0   1692                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
    17 background                           0         0                                                                                                                                                                                                                                                                  0x00A9                7733744 BRKR TASK        BROKER_TRANSMITTER                         0                    0           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   1      0   3240                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
    18 background                           0         0                                                                                                                                                                                                                                                                  0x00A9                7733743 BRKR TASK        BROKER_TRANSMITTER                         0                    0           0 2016-08-12 05:11:32.293    2016-08-12 05:11:32.293                                   1      0   3300                                                                                                                                              sa                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       1 0x01000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x00000000000000000000           0           0           0
    54 sleeping                             0         1                                                                                                                                                                                                                                                                  0x0000                      0 AWAITING COMMAND MISCELLANEOUS                            265                    3           2 2016-08-12 05:29:40.413    2016-08-12 05:27:27.153    DED502BF4CF9 LPC               5      0      0 TECH                                                                                                                             4112        TECH\user                                                                                                                        Microsoft SQL Server Management Studio - Query                                                                                   TECH                                                                                                                             user                                                                                                                                  1 0x010500000000000515000000F80B6F0A8F0FBF8F892C993BE8030000000000000000000000000000000000 0x0100050081CEE804A00E           0           0           0
    56 suspended                           54         0 RID: 5:1:194:18                                                                                                                                                                                                                                                  0x0003                6636491 SELECT           LCK_M_S                                    0                    0           2 2016-08-12 05:29:45.363    2016-08-12 05:29:45.353    DED502BF4CF9 LPC               5      0   4972 TECH                                                                                                                             4112        TECH\user                                                                                                                        Microsoft SQL Server Management Studio - Query                                                                                   TECH                                                                                                                             user                                                                                                                                  1 0x010500000000000515000000F80B6F0A8F0FBF8F892C993BE8030000000000000000000000000000000000 0x01000500CA302D34E00D           0          -1           0
ESP 30
 
SYSPROC FIRST PASS
spid   request_id  ecid   waittype
------ ----------- ------ --------
     2           0      0 0x0158  
     3           0      0 0x015A  
     4           0      0 0x0060  
     5           0      0 0x007F  
     6           0      0 0x0080  
     7           0      0 0x009D  
     9           0      0 0x00A2  
    10           0      0 0x015E  
    12           0      0 0x007E  
    13           0      0 0x0081  
    14           0      0 0x00AE  
    17           0      0 0x00A9  
    18           0      0 0x00A9  
    56           0      0 0x0003  
Blocking via locks at 2016-08-12 07:20:24.363
 
SPIDs at the head of blocking chains
spid  
------
    54
 
sys.dm_os_wait_stats
wait_type                                                    waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms 
------------------------------------------------------------ -------------------- -------------------- -------------------- --------------------
LCK_M_SCH_M                                                                    13                   89                   37                    0
LCK_M_S                                                                         1                    0                    0                    0
LCK_M_X                                                                         5                  160                   77                    2
LATCH_SH                                                                        1                    0                    0                    0
LATCH_EX                                                                        5                   27                   27                   27
PAGELATCH_SH                                                                   37                    0                    0                    0
PAGELATCH_EX                                                                   47                   32                   31                    0
PAGEIOLATCH_SH                                                                662                 9585                  142                   96
PAGEIOLATCH_UP                                                                 20                  180                   37                    0
PAGEIOLATCH_EX                                                                  6                  104                   28                    0
LAZYWRITER_SLEEP                                                             7767              7736985                 1056                  416
IO_COMPLETION                                                                  82                 2168                  115                    0
ASYNC_IO_COMPLETION                                                             1                  256                  256                    0
ASYNC_NETWORK_IO                                                             1123                 1982                  240                   20
CHKPT                                                                           1                 1948                 1948                  105
SLEEP_DBSTARTUP                                                                17                 1843                  136                  111
SLEEP_DCOMSTARTUP                                                               2                  600                  300                    0
SLEEP_TASK                                                                  10493              3866249                 1058                  330
SLEEP_SYSTEMTASK                                                                1                 1844                 1844                    0
OLEDB                                                                          13                   49                   23                    0
THREADPOOL                                                                      6                    1                    0                    0
SOS_SCHEDULER_YIELD                                                          1400                   52                   36                   49
ONDEMAND_TASK_QUEUE                                                             1                    0                    0                    0
LOGMGR_QUEUE                                                                   42              7641520              3660215                   67
REQUEST_FOR_DEADLOCK_SEARCH                                                  1548              7736933                 5018              7736933
CHECKPOINT_QUEUE                                                                9              7662622              2661412                    2
KSOURCE_WAKEUP                                                                  1                    0                    0                    0
SQLTRACE_INCREMENTAL_FLUSH_SLEEP                                             1934              7735979                 4015                    9
BROKER_TRANSMITTER                                                              2                    0                    0                    0
BROKER_MASTERSTART                                                              1                    1                    1                    0
BROKER_EVENTHANDLER                                                             8                  744                  295                   82
WRITELOG                                                                       53                  384                   20                    1
MSQL_XP                                                                        98                 1043                  453                    0
LOGBUFFER                                                                       3                    0                    0                    0
EE_SPECPROC_MAP_INIT                                                            1                   16                   16                    0
BROKER_TASK_STOP                                                                6                40094                10007                    0
DAC_INIT                                                                        1                    1                    1                    0
XE_TIMER_EVENT                                                                427              7711575                30007              7710255
XE_DISPATCHER_WAIT                                                              1                    0                    0                    0
BROKER_TO_FLUSH                                                              3769              3867447                 1071                  199
PREEMPTIVE_OS_GENERICOPS                                                       15                 1988                  688                    0
PREEMPTIVE_OS_AUTHENTICATIONOPS                                              2035                  286                    4                    0
PREEMPTIVE_OS_DECRYPTMESSAGE                                                  339                   12                    0                    0
PREEMPTIVE_OS_DELETESECURITYCONTEXT                                           330                   34                    4                    0
PREEMPTIVE_OS_AUTHORIZATIONOPS                                                357                   21                    5                    0
PREEMPTIVE_OS_LOOKUPACCOUNTSID                                                341                  177                    1                    0
PREEMPTIVE_OS_REVERTTOSELF                                                    339                    6                    0                    0
PREEMPTIVE_OS_COMOPS                                                            1                  497                  497                    0
PREEMPTIVE_OS_CRYPTOPS                                                          2                  849                  756                    0
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT                                              31                    6                    0                    0
PREEMPTIVE_OS_CRYPTIMPORTKEY                                                    8                    2                    2                    0
PREEMPTIVE_OS_DOMAINSERVICESOPS                                                 1                    1                    1                    0
PREEMPTIVE_OS_FILEOPS                                                          60                  568                  251                    0
PREEMPTIVE_OS_CREATEFILE                                                       45                    2                    0                    0
PREEMPTIVE_FILESIZEGET                                                         14                    4                    3                    0
PREEMPTIVE_OS_GETFILEATTRIBUTES                                                38                  120                   58                    0
PREEMPTIVE_OS_WRITEFILEGATHER                                                   6                  111                   59                    0
PREEMPTIVE_OS_LIBRARYOPS                                                        1                 2573                 2573                    0
PREEMPTIVE_OS_GETPROCADDRESS                                                   98                  252                   76                    0
PREEMPTIVE_OS_LOADLIBRARY                                                       2                  129                   65                    0
PREEMPTIVE_OS_PIPEOPS                                                           1                  613                  613                    0
PREEMPTIVE_OS_DISCONNECTNAMEDPIPE                                             330                   15                    2                    0
PREEMPTIVE_OS_REPORTEVENT                                                      37                  137                  125                    0
PREEMPTIVE_OS_WAITFORSINGLEOBJECT                                             136                 1748                  240                    0
PREEMPTIVE_OS_QUERYREGISTRY                                                   128                   30                    1                    0
PREEMPTIVE_OS_SQMLAUNCH                                                         1                    0                    0                    0
PREEMPTIVE_XE_CALLBACKEXECUTE                                                   8                    0                    0                    0
PREEMPTIVE_XE_DISPATCHER                                                        1                    0                    0                    0
PREEMPTIVE_XE_SESSIONCOMMIT                                                     1                    0                    0                    0
PREEMPTIVE_XE_TARGETINIT                                                        1                    0                    0                    0
PREEMPTIVE_XE_TIMERRUN                                                          1                    0                    0                    0
PREEMPTIVE_LOCKMONITOR                                                          1                    0                    0                    0
WRITE_COMPLETION                                                               43                  476                   38                    0
FT_IFTSHC_MUTEX                                                                 1                 1396                 1396                    0
FT_IFTS_SCHEDULER_IDLE_WAIT                                                   128              7620270                60009                    5
PERFORMANCE_COUNTERS_RWLOCK                                                     2                    0                    0                    0
SQLTRACE_FILE_WRITE_IO_COMPLETION                                               4                   26                   26                    0
OWS
 
*********************************************************************
Print out SQL Statements for all blocked or blocking spids.
*********************************************************************
 
SQL Statement running FOR SPID 54(0)
SPID   Query                                                                                                                                                                                                                                                            Start Time              Last Batch             
------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- -----------------------
    54 begin tran a
insert into student(name,fname,lname)
values('dd','dsds','dwdw')
                                                                                                                                                                                2016-08-12 05:27:27.153 2016-08-12 05:29:40.413
 
SQL Statement running FOR SPID 56(0)
SPID   Query                                                                                                                                                                                                                                                            Start Time              Last Batch             
------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- -----------------------
    56 select * from student
                                                                                                                                                                                                                                          2016-08-12 05:29:45.353 2016-08-12 05:29:45.363
 
*******************************************************************************
Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.
*******************************************************************************
 
DBCC OPENTRAN FOR DBID 1 [master]
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
DBCC OPENTRAN FOR DBID 2 [tempdb]
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
DBCC OPENTRAN FOR DBID 5 [admin]
Transaction information for database 'admin'.

Oldest active transaction:
    SPID (server process ID): 54
    UID (user ID) : -1
    Name          : a
    LSN           : (22:187:2)
    Start time    : Aug 12 2016  5:29:40:413AM
    SID           : 0x010500000000000515000000f80b6f0a8f0fbf8f892c993be8030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
End time: 2016-08-12 07:20:24.423

References: The stored procedure has been used from below Microsoft support site and modified to use the DMVs in place of DBCC inputbuffer.

Microsoft Support Website

Udemy.com Home page 125x125

2 COMMENTS

LEAVE A REPLY