• How to monitor blocking in SQL Server 2005 and in SQL Server 2000 [ZTMS]


    How to monitor blocking in SQL Server 2005 and in SQL Server 2000

    Article ID : 271509
    First Published :
    8/14/2000
    Last Reviewed :
    11/2/2007
    Revision : 14.1
    Modification Type : Minor
    Language Locale : en-us
    Article Status : Published
    Confidentiality : Public

    MICROSOFT INTERNAL SUPPORT INFORMATION

    IMPORTANT: The list(s) in this article were modified during migration to KMS. To see the original version of the list, please refer to the TextLegacy section at the bottom of this article.

    SUMMARY

    If you are using Microsoft SQL Server 2000, you must apply SQL Server 2000 Services Pack 3a (SP3a) before you use the script that is described in this article.

    For more information about how to obtain SQL Server 2000 Service Pack 3a, click the following article number to view the article in the Microsoft Knowledge Base:
    290211 How to obtain the latest SQL Server 2000 service pack
    This article is an update for SQL Server 2000 or later versions of SQL Server of the following Microsoft Knowledge Base article, which applies to Microsoft SQL Server 7.0:
    251004 How to monitor SQL Server 7.0 blocking
    This article documents the usage and design of a stored procedure that you can use to diagnose blocking and performance issues. For a description of how to understand and resolve blocking, see the following article in the Microsoft Knowledge Base:
    224453 Understanding and resolving SQL Server 7.0 or 2000 blocking problems
    In Microsoft SQL Server 2005, you can also use the Blocked Process Report event class in SQL Server Profiler to capture the information about a task that has been blocked for more than a specified amount of time.

    For more information about the Blocked Process Report event class, visit the following Microsoft Developer Network (MSDN) Web site:

    MORE INFORMATION

    The following description of the sp_blocker_pss80 stored procedure captures this information. This information also applies to SQL Server 2005.
    The starting time (according to the computer that is running SQL Server) so that this sampling of blocking can be time-aligned with other performance information, such as a Microsoft Windows NT Performance Monitor log or a SQL Profiler log.
    Information about connections to SQL Server, by querying the sysprocesses system table.
    Information about lock resources, by querying the syslockinfo system table.
    Information about resource waits, by running DBCC SQLPERF(WAITSTATS).
    The current running SQL Server Batch for connections that are blocked by others or blocking others, by running the DBCC INPUTBUFFER statement.
    The ending time, according to the computer that is running SQL Server.
    The stored procedure was created with the following optimizations to reduce the effect on performance and blocking by running this stored procedure:
    There is no output generated unless at least one connection is waiting on a resource.
    The sysprocesses and syslockinfo system tables from the master database are queried directly, to increase performance and to prevent this stored procedure from becoming blocked. Therefore, this stored procedure is specific to Microsoft SQL Server 2000 or later versions of SQL Server.
    A small worktable is created by the cursor to obtain the DBCC INPUTBUFFER output; this should have no noticeable effect on the usage in the tempdb database.
    Because the blocking can change while gathering the information, a fast mode exists, which increases performance by decreasing the results to relevant rows of the sysprocesses and syslockinfo system tables.
    If trying to track non-lock resource waits, a latch mode exists, which causes the lock output to be omitted.

    MICROSOFT INTERNAL SUPPORT INFORMATION

    This script does not report the sysprocesses column context_info , which you can use to isolate a blocking problem more.
    This stored procedure is useful by running it from any query tool. However, Microsoft suggests that you follow these steps to perform blocking analysis:
    1. Create the stored procedure sp_blocker_pss80 , which is included at the end of this article, from any query tool while you are connected with a login that has sysadmin privileges on the SQL Server server or the instance of SQL Server you plan to monitor.
    2. Create a script file with the following query to run the stored procedure in a loop. Note that the delay should be between 5 and 60 seconds:
    WHILE 1=1
    BEGIN
       EXEC master.dbo.sp_blocker_pss80
       -- Or for fast mode 
       -- EXEC master.dbo.sp_blocker_pss80 @fast=1
       -- Or for latch mode 
       -- EXEC master.dbo.sp_blocker_pss80 @latch=1
       WAITFOR DELAY '00:00:15'
    END
    GO 
    3. This output is very useful when combined with a Microsoft Windows NT Performance Monitor log and a SQL Profiler log, so creating both at the same time is suggested. For information concerning which Profiler and Performance Monitor events to capture, and for information about how to interpret the results, see the following article in the Microsoft Knowledge Base:
    224453 Understanding and resolving SQL Server 7.0 or 2000 blocking problems
    4. Run the script file created in step 2 from either Isql.exe, the Osql.exe query tool, or the Sqlcmd utility in a Windows command prompt on the computer that is running SQL Server for which you intend to monitor to prevent networking problems from disconnecting the query tool. Following is an example command line that you can use to start Osql.exe, which assumes that the client is run from the computer that is running SQL Server, and that the script file name is Checkblk.sql. Make sure to correct the -S parameter, and replace "server" with the name of your SQL Server server (or "servername\instance" if you are monitoring a named instance) . Also correct the -i parameter, and replace "checkblk.sql" with the path and name to the script file created in step 2.
    osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000 
    Note that you must use the other command line switches for the following reasons:

    To prevent line wrapping in the output files which makes it easier to read.
    To send the output to a file, specified with the -o parameter, instead of to the screen so that if the query tool has problems, you still have output until the query tool fails.
    Following is the script to create the sp_blocker_pss80 stored procedure:

    MICROSOFT INTERNAL SUPPORT INFORMATION

    This code has been optimized because of poor performance in the original SQL Server 7.0 version of this script in the following ways:
    Performs only two table scans on the sysprocesses table and one table scan on the syslockinfo table. This change was made because of the fact that these fake system tables can be slow to query in high CPU utilization situations.
    The syslockinfo table is always queried directly for lock information.
    All locks held for clients involved in blocking are shown during fast mode execution because the self join on syslockinfo performed poorly when the number of locks increased to over about 100.
    The joins against the spt_values table were replaced with Case statements. Here are the queries to determine the values for those columns:
    -- Lock Type
    select number, name from spt_values where type = 'LR'
    -- Lock Mode
    select number, name from spt_values where type = 'L'
    -- Lock Status
    select number, name from spt_values where type = 'LS' 
    When future modifications of this blocking script are considered, test the performance of the script while the following commands are run from a Command prompt:
    100 clients holding about 70000 locks with little or no CPU utilization:
    ostress -Usa -P -dmaster -n100 -q -Q"begin tran select * from spt_values 
    (holdlock rowlock) waitfor delay '00:05:00' commit tran" 
    100 clients holding about 2500 locks with CPU stress:
    ostress -Usa -P -dpubs -n100 -r1000 -q -Q"begin tran select * from authors 
    (holdlock rowlock) waitfor delay '00:00:01' commit tran" 

    MICROSOFT INTERNAL SUPPORT INFORMATION

    Here is the version history for this script:
    
    date       author   comment
    2000-08-24 MikeZ    Original version using 7.0 script as template but using  table variables instead of temporary tables
    2000-12-19 MikeZ    Added distinct to insert #probclients select to correct duplicate key problem
    2001-02-20 KeithElm Correct problems of creation when Quoted Identifiers enabled
    2001-03-01 MikeZ    Changes to allow filtering and import into table for quick analysis
    2001-03-15 MikeZ    Added DBCC OPENTRAN
                        Commented waittype check to remove DBCC INPUTBUFFER and PSS for spid not involved in blocking
    2001-07-02 MikeZ    Added ecid to temp table to correct duplicate key problem
    2002-02-26 MikeZ    Added DBCC SQLPERF(WAITSTATS)
                        Always report Endtime of stored procedure execution if waits exist
                        Changed date format to ODBC Canonical Format (121 from 113)
                        Added @latch parameter to omit syslockinfo output
    2002-07-08 MikeZ    Added ms datediffs to track slow sysprocesses and syslockinfo passes
                        Removed keyword and runtime from sysprocesses and syslockinfo
                        Added 'No blocking via locks' to output and removed syslockinfo pass when no blocking
    2002-08-09 MikeZ    Added filtering so that PSSDiag is not reported for DBCC INPUTBUFFER and DBCC PSS output
    2003-01-09 MikeZ    Removed DBCC TRACEON(3604) call due to sending DBCC PSS output to temporary table
                        Changed default to @fast = 1
                        Run DBCC PSS only if slow mode or running on SQL Server 2000 Service Pack 3 or later
                        Run DBCC PSS for all spids in blocking chain instead of just lead blocker
                        Run DBCC OPENTRAN when runnning versions before SQL Server 2000 Service Pack 3
                        Reduced sysprocesses columns if running on SQL Server 2000 Service Pack 3 or later
                        Report message of 'No Waittypes' instead of no output in that situation since DBCC TRACE output not shown
                        Report rowset of spids with waittypes collected in first sysprocesses pass in case changes occur later
                        Modified filtering so that PSSDiag is not seen in output unless it has non WAITFOR waittype
    2003-04-11 MikeZ    Changed DBCC PSS to be called less often due to exceptions
    2003-11-24 MikeZ    Previously created custom version ommitting DBCC PSS, DBCC INPUTBUFFER and DBCC SQLPERF(WAITTYPES) but never released
    2003-11-24 MikeZ    Removed call to DBCC PSS due to bug 470728
                        Removed sysprocesses row format version 8.1 (SP3+) and replaced with 8.2 with includes extra columns
                        Change to prevent null servername from affecting the import of sysprocesses output
                        Added message of 'Blocking via locks at' for findstr analysis
    2003-12-02 MikeZ    Created two versions of sp_blocker_pss80 in the code section (Pre-SQL Server 2000 SP3 and Post-SQL Server 2000 SP3 versions). Note Will cause errors but 
                        correct version will be created
    2004-02-26 MikeZ    Fixed batch termination issue due to running DBCC OPENTRAN on inaccessable databases
    2006-04-06 MikeZ    Fixed repeated DBCC Inputbuffer calls for same spid. Set Language to English to prevent parsing errors with import script.
    2006-10-10 MikeZ    Changed reference to master.dbo.sysprocesses. Corrected case sensitivity errors on variables @status and @useraccess. 
    use master
    GO
    if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss80') and sysstat & 0xf = 4)
       drop procedure dbo.sp_blocker_pss80
    GO
    create procedure dbo.sp_blocker_pss80 (@latch int = 0, @fast int = 1, @appname sysname='PSSDIAG')
    as 
    --version 17SP3
    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
    
    set @time = getdate()
    declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,
       ignore_app tinyint, primary key (blocked, spid, ecid))
    insert @probclients select spid, 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 or waittype != 0x020B)
    begin
       set @time2 = getdate()
       print ''
       print '8.2 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2))
    
       insert @probclients select distinct blocked, 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
          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, 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 s,
                   @probclients p
                where p.spid = s.req_spid
    
                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
          from master.dbo.sysprocesses
    
          print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
    
          print ''
          print 'SYSPROC FIRST PASS'
          select spid, 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 'DBCC SQLPERF(WAITSTATS)'
       dbcc sqlperf(waitstats)
    
       Print ''
       Print '*********************************************************************'
       Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
       Print '*********************************************************************'
    
       declare ibuffer cursor fast_forward for
       select distinct cast (spid as varchar(6)) as spid
       from @probclients
       where (spid <> @@spid) 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
       while (@@fetch_status != -1)
       begin
          print ''
          print 'DBCC INPUTBUFFER FOR SPID ' + @spid
          exec ('dbcc inputbuffer (' + @spid + ')')
    
          fetch next from ibuffer into @spid
       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)')
    GO    
    
    create procedure dbo.sp_blocker_pss80 (@latch int = 0, @fast int = 1, @appname sysname='PSSDIAG')
    as 
    --version 17
    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
    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
    
    set @time = getdate()
    declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,
       ignore_app tinyint, primary key (blocked, spid, ecid))
    insert @probclients select spid, 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 or waittype != 0x020B)
    begin
       set @time2 = getdate()
       print ''
       print '8 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2))
    
       insert @probclients select distinct blocked, 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
          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 waittype != 0x0000)
    
          print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
    
          print ''
          print 'SYSPROC FIRST PASS'
          select spid, 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 s,
                   @probclients p
                where p.spid = s.req_spid
    
                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
          from master.dbo.sysprocesses
    
          print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
    
          print ''
          print 'SYSPROC FIRST PASS'
          select spid, 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 'DBCC SQLPERF(WAITSTATS)'
       dbcc sqlperf(waitstats)
    
       Print ''
       Print '*********************************************************************'
       Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
       Print '*********************************************************************'
    
       declare ibuffer cursor fast_forward for
       select distinct cast (spid as varchar(6)) as spid
       from @probclients
       where (spid <> @@spid) 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
       while (@@fetch_status != -1)
       begin
          print ''
          print 'DBCC INPUTBUFFER FOR SPID ' + @spid
          exec ('dbcc inputbuffer (' + @spid + ')')
    
          fetch next from ibuffer into @spid
       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)')
    GO 

    The information in this article applies to:
    Microsoft SQL Server 2005 Standard Edition
    Microsoft SQL Server 2005 Workgroup
    Microsoft SQL Server 2005 Developer Edition
    Microsoft SQL Server 2005 Enterprise Edition
    Microsoft SQL Server 2000 Personal Edition
    Microsoft SQL Server 2000 Standard Edition
    Microsoft SQL Server 2000 Workgroup Edition
    Microsoft SQL Server 2000 Developer Edition
    Microsoft SQL Server 2000 Enterprise Edition
  • 相关阅读:
    yii 引入文件
    CodeForces 621C Wet Shark and Flowers
    面试题题解
    POJ 2251 Dungeon Master
    HDU 5935 Car(模拟)
    HDU 5938 Four Operations(暴力枚举)
    CodeForces 722C Destroying Array(并查集)
    HDU 5547 Sudoku(dfs)
    HDU 5583 Kingdom of Black and White(模拟)
    HDU 5512 Pagodas(等差数列)
  • 原文地址:https://www.cnblogs.com/liangqihui/p/1083549.html
Copyright © 2020-2023  润新知