Export (0) Print
Expand All
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Expand Minimize
2 out of 5 rated this helpful - Rate this topic

Identifying Common Administrative Issues for Microsoft SQL Server 2000

SQL Server 2000
 

Microsoft Corporation

November 2001

Applies to:
   Microsoft® SQL Server™ 2000

Summary: This article describes stored procedures that can be used to identify server and database configurations outside the recommended configuration parameters for Microsoft SQL Server 2000, as well as to troubleshoot blocking issues. (19 printed pages)

Download Storedpro.exe.

Contents

Introduction
Installing the Stored Procedures
Check Server Configuration (sp_rk_audit_configure)
Configuration Options that Are Checked
   Running sp_rk_audit_configure
   How sp_rk_audit_configure Works
   Modifying sp_rk_audit_configure
Check Database Configuration (sp_rk_audit_dboptions)
   Running sp_rk_audit_dboptions
   How sp_rk_audit_dboptions Works
   Other Stored Procedures Called by sp_rk_audit_dboptions_check_1_db
   Modifying sp_rk_audit_dboptions_check_1_db to Look at Different Values
Application Troubleshooting
   Running sp_rk_blocker_blockee
   How sp_rk_blocker_blockee Works
   Modifying sp_rk_blocker_blockee

Introduction

This paper describes user-defined stored procedures that can be used to identify server and database configurations that are outside the recommended configuration parameters for Microsoft® SQL Server™ 2000. The stored procedures are:

sp_rk_audit_configure
Checks server configuration for potential improvements.
sp_rk_audit_db_options
Checks the configuration of each database for potential improvements.
sp_rk_blocker_blockee
Returns information about all blocking and blocked processes.

These stored procedures can be used "as is" or modified by a database administrator to fit the specific needs of an enterprise.

Warning   Do not use the stored procedures described in this paper on a production server without fully understanding how they work and what they do. It is strongly recommended that the techniques described in this paper be tested on a development computer.

Installing the Stored Procedures

This installation procedure assumes that the instance of SQL Server on which these stored procedures are being installed allows trusted connections, and that the installation script is run by someone with sysadmin privileges. If you are unfamiliar with terms used in this paper, see SQL Server Books Online.

To install the stored procedures

  1. Download the storedpro.exe self-extracting executable file to your computer.
  2. To extract the SQL scripts, run the storedpro.exe file.
  3. Start SQL Query Analyzer, and on the File menu, click Open.
  4. Navigate to the folder in which the SQL scripts are stored, and then double-click sp_rk_create_audit_sp_in_master.sql (Figure 1). This loads the master setup stored procedure.

     Click here for larger image

    Figure 1. Loading the master setup procedure (click image to see larger picture)

  5. In the script, locate the comment "—Begin Customer changes here." Following this comment, there are three parameters whose values must be changed:
    • @srcdir

      The location of the SQL scripts that were installed on the computer.

    • @lisdir

      The location of where the output files generated by this installation script are to be stored.

    • @srv

      The name of the instance of SQL Server on which the stored procedures are to be installed.

  6. On the Query menu, click Results in Text, and then execute the procedure. This installs the stored procedures in the master database (Figure 2).

     Click here for larger image

    Figure 2. Installing the stored procedures in the master database (click image to see larger picture)

As shown in the preceding illustration, the sp_rk_create_audit_sp_in_master.sql script dynamically generates a command prompt call to the osql utility, and then runs it in the xp_cmdshell extended stored procedure. The –E flag means that osql is using trusted connections. If osql is not using trusted connections, each command line must be modified to use the –U username and –P password flags.

Very little data is returned to the SQL Query Analyzer results pane because the work is shelled out to the command line; however, the osql calls generate log files that are stored in C:\Temp by default.

In SQL Query Analyzer, in Object Browser, click master, and then click Stored Procedures. The following stored procedures should be listed:

dbo.sp_rk_audit_clustered_widedbo.sp_rk_check_non_dbo_owned_objects
dbo.sp_rk_audit_configuredbo.sp_rk_create_audit_sp_in_master
dbo.sp_rk_audit_dboptionsdbo.sp_rk_helpfile
dbo.sp_rk_audit_dboptions_check_1_dbdbo.sp_rk_indexoption
dbo.sp_rk_audit_old_outer_joinsdbo.sp_rk_pk_uk_fk_index_analysis
dbo.sp_rk_audit_tlogsdbo.sp_rk_server_is_listening_on
dbo.sp_rk_blocker_blockeedbo.sp_rk_tableoption

If the stored procedures are not listed and there are no error messages in the results pane, check the log files generated by the osql calls. You will not see any error messages if, for example, the procedures are run against a server that does not exist, or with an account that does not have the appropriate permissions.

To view the log files in their default directory

  1. On the Start menu, click Run, and then type c:\temp (or, if you changed this default, type the directory where the log files are stored).
  2. Select one of the sp_rk*.lis files.
  3. On the File menu, click Open With, and then select Notepad.

After the stored procedures have been installed, they can be run immediately with their default set of checks.

SQL Server 2000 can adjust many of its configuration parameters automatically at run time. For this reason, database administrators are advised to leave these parameters set at their default values. Some options, for example, max worker threads, can be adjusted manually to improve performance. The max worker threads option can be used to configure the number of worker threads available to SQL Server processes. The stored procedures described in this paper can help determine which, if any, server configurations are outside the Microsoft recommendations for a database application.

Note   Some of the sp_configure parameters apply only to SQL Server version 7.0 and have been removed in SQL Server 2000, for example, max async IO.

Check Server Configuration (sp_rk_audit_configure)

The sp_rk_audit_configure stored procedure is a stand-alone stored procedure. sp_rk_audit_configure checks the server configuration options. The default ranges for these server configuration options are hard-coded into the stored procedure based on Microsoft recommendations. Changing these ranges and adding a new option to check are covered in "Modifying sp_rk_audit_configure" in this paper. sp_rk_audit_configure has no required parameters.

Configuration Options that Are Checked

The sp_rk_audit_configure stored procedure checks whether or not TCP/IP Sockets is installed and whether the tempdb or msdb databases have grown since the last time SQL Server was started.

The following table shows the configuration options that sp_rk_audit_configure checks and the recommended values.

Configuration optionRecommended value
affinity mask = 0
allow updates= 0
fill factor (%)Not between 1 and 49
lightweight pooling= 0
locksNot between 1 and 9999
max async IO>= 32 (SQL Server 7.0 only)
max server memory (MB)> 32 or > (Total RAM – 48 megabytes (MB))
min memory per query (KB)>= 1024
network packet size (B)Between 4096 and 16384
open objects= 0
priority boost= 0
query governor cost limit= 0
query wait (s)= -1
recovery interval (min)= 0 or between 5 and 30
resource timeout (s)= 10
set working set size= 0
spin counter= 0
time slice (ms)Between 100 and 300

Running sp_rk_audit_configure

sp_rk_audit_configure gathers information about the server configuration by running stored procedures, such as sp_configure and xp_msver, and then storing their results in temporary tables.

To run sp_rk_audit_configure

  1. In SQL Query Analyzer, in the query window, type sp_rk_audit_configure.
  2. On the Query menu, click Results in Text, and then execute the stored procedure.

    As shown in Figure 3, the Results pane lists server configuration options that are less than optimal, as well as the number of possible problems to investigate.

     Click here for larger image

    Figure 3. The results of executing the sp_rk_audit_configure stored procedure (click image to see larger picture)

How sp_rk_audit_configure Works

This section describes some of the important sections of code in the sp_rk_audit_configure stored procedure.

The #t temporary table stores results from the sp_configure system stored procedure:

create table #t (
      [name] varchar(128), 
      minimum int,
      maximum int, 
      config_value int,
      run_value int
)

The #t_serverinfo temporary table stores results from the xp_msver stored procedure:

create table #t_serverinfo( 
      [Index] int, 
      [Name] varchar(255), 
      Internal_Value int, 
      Character_Value varchar(255)
)

To see all possible configuration settings in sp_configure, the sp_rk_audit_configure stored procedure sets the show advanced options parameter to 1:

   execute sp_configure 'show advanced options', 1
   reconfigure with override
   reconfigure

sp_configure and xp_msver are run and the results from these stored procedures are inserted into their respective temporary tables:

   insert into #t ([name], minimum, maximum, config_value, run_value)
   execute sp_configure
   insert into #t_serverinfo( [Index] , [Name] , Internal_Value , 
   Character_Value )
   exec master..xp_msver

Most of the remaining code in sp_rk_audit_configure compares the entries in the #t temporary table created by sp_configure to the hard-coded values. For example, the following code checks to see whether an affinity mask is in place. For more information about using an affinity mask in a multiprocessor environment, see SQL Server Books Online.

    if exists (select 1 from #t where [name] = 'affinity mask' and 
                             (config_value <> 0 or run_value <> 0))
    begin
      select 'Recommend value to be set to 0 for ', * 
      from #t 
      where name = 'affinity mask'
      set @nr = @nr + 1
    end

Finally, sp_rk_audit_configure checks to see whether the tempdb or msdb databases have expanded since SQL Server was started. If these databases have expanded, the sp_rk_audit_configure stored procedure recommends that the databases be resized. The stored procedure does this by exploiting the different times at which SQL Server updates its system tables when a database file expands. The sysfiles system table in the local database always has current information about the size of the database files; however, the sysaltfiles system table in the master database is updated only when the server is restarted.

      select * 
      from  master.dbo.sysaltfiles a 
            inner join tempdb.dbo.sysfiles f 
            on a.fileid = f.fileid
      where dbid = db_id('tempdb') and 
            a.size <> f.size

Modifying sp_rk_audit_configure

The sp_rk_audit_configure stored procedure can be customized to:

  • Check for nested triggers.
  • Check for product version information.
  • Increase max async IO (SQL Server 7.0 only).

For example, if the servers are high performance servers with intelligent disk subsystems, the amount of asynchronous IO requests can be changed from 32 to 64.

To increase the max async IO

  1. In SQL Query Analyzer, in Object Browser, expand the master database.
  2. In Stored Procedures, right-click sp_rk_audit_configure, and then click Edit.

    This loads the stored procedure in a new query window.

    Instead of being in a CREATE PROCEDURE statement, sp_rk_audit-configure is now in an ALTER PROCEDURE statement. Thus, all existing permissions and dependencies will not be lost. For more information, see "ALTER PROCEDURE" in SQL Server Books Online.

  3. Locate the following code:
    if exists (select 1 from #t where [name] = 'max async IO' and 
                  (config_value < 32 or run_value < 32))
    begin
    select 'Recommend value to be set to 32 or more for ', * from #t where 
                  name = 'max async IO'
       set @nr = @nr + 1
    end
    
  4. In the code in Step 3, replace the value 32 with 64:
  5. Recompile the stored procedure.

To see whether nested triggers are enabled

  1. In SQL Query Analyzer, in Object Browser, expand the master database.
  2. In Stored Procedures, right-click sp_rk_audit_configure, and then click Edit. This loads the stored procedure in a new query window.
  3. Locate the following code:
    -- conclusions
        if @nr > 0 
        begin
           select 'found ', @nr, ' possible problems to investigate. 
                Please also Investigate individual databases. End of 
                      Report.'
        end
        else
        begin
           select 'found nothing to investigate server wide. 
                Please Investigate individual databases. End of Report.'
        end
    
        return 0
    
  4. Paste the following code into the stored procedure just before the "—conclusions" comment shown in Step 3.
    if exists (select 1 from #t where [name] = 'nested triggers' and 
                   (config_value = 0))
    begin
    select 'Nested Triggers are currently disabled!'
       set @nr = @nr + 1
    end
    
  5. Recompile the stored procedure.

To check the version of SQL Server you are running

To check whether the server is running SQL Server 2000, look at the product version information returned from xp_msver, which is called by sp_rk_audit_dboptions_check_1_db. You can also check for any release of SQL Server, including service packs, by adjusting the major and the minor release numbers in the character_value string.

  1. In SQL Query Analyzer, in Object Browser, expand the master database.
  2. In Stored Procedures, right-click sp_rk_audit_configure, and then click Edit. This loads the stored procedure in a new query window.
  3. Locate the following line of code:
    from #t_serverinfo where [Name] = 'ProductVersion'
    
  4. Paste the following code into the stored procedure immediately after the line of code in Step 3.
    If Exists (select 1 from #t_serverinfo where [Name] = 'ProductVersion' 
                            and Character_Value = '8.00.100')
    Begin
       Select 'This server is running SQL 2000 .'
    End
    
  5. Recompile the stored procedure.

Check Database Configuration (sp_rk_audit_dboptions)

The sp_rk_audit_dboptions stored procedure is the entry point to a suite of stored procedures that will check a number of database-specific parameters for one or all of the databases on the server. This section contains a short description of each of these stored procedures.

Important   The sp_rk_audit_dboptions stored procedure must be run first because it creates all the temporary tables that are referenced by the other stored procedures described in this section.
sp_rk_audit_dboptions_check_1_db
Checks the following database options. It also checks all databases for database and transaction log backups within the last month and makes sure that log and database files are on separate disks.
Database optionValue
auto update statisticsOFF
auto create statisticsOFF
autocloseON
autoshrinkON
dbo use onlyON
OfflineON

If the database is not a system database, the following options and values are also reported.

Database optionValue
select into/bulkcopyON
trunc. log on chkpt.ON
sp_rk_check_non_dbo_owned_objects
Checks the database for objects not owned by dbo.
sp_rk_indexoption
Looks for indexes with option values. For more information, see "sp_indexoption" in SQL Server Books Online.
sp_rk_tableoption
Looks for tables with option values. For more information, see "sp_tableoption" in SQL Server Books Online.
sp_rk_pk_uk_fk_index_analysis
Checks each table for at least one PRIMARY KEY, FOREIGN KEY, or UNIQUE constraint. It also lists any table without an index.
sp_rk_audit_old_outer_joins
Checks for all user-defined stored procedures in the database with old (non–SQL-92 standard) joins (*= or =*).
sp_rk_audit_clustered_wide
Looks for wide clustered keys with secondary indexes.

For more information about SQL Server performance tuning, see the topics "Optimizing Database Performance" and "Setting Database Options" in SQL Server Books Online.

Running sp_rk_audit_dboptions

The sp_rk_audit_dboptions stored procedure creates temporary tables that will store information about the environment of each database.

To run sp_rk_audit_dboptions

  1. In SQL Query Analyzer, in the query window, type sp_rk_audit_dboptions.
  2. On the Query menu, select Results in Text, and then execute the stored procedure.

    Figure 4 shows a section of the results.

     Click here for larger image

    Figure 4. Partial results of the sp_rk_audit_dboptions stored procedure (click image to see larger picture)

    sp_rk_audit_dboptions discovered the following:

    • A database called intranet has not been backed up in more than a week.
    • The transaction log for this database has never been backed up.
    • The log and database files share the same drive, which is not recommended.
    • Some tables do not have PRIMARY KEY, UNIQUE, or FOREIGN KEY constraints.

How sp_rk_audit_dboptions Works

This section describes some of the important pieces of code in the sp_rk_audit_dboptions stored procedure that has these parameters and default values:

sp_rk_audit_dboptions(@dbname sysname = NULL, @DebugFlag int = 0)

The #t_indexes temporary table is used by the sp_rk_pk_uk_fk_index_analysis stored procedure:

      create table #t_indexes( 
            tablename varchar(255),    
            indexname varchar(255), 
            indid int, 
            indextype varchar(15), 
            objectowner varchar(80)
      )

The #t_dbfiles temporary table is used by the sp_rk_audit_dboptions_check_1_db and the sp_rk_helpfile stored procedures:

      create table #t_dbfiles( 
            [name] varchar(256),    
            fileid int,
            [filename] varchar(1024),
            [filegroup] varchar(256),
            [size] decimal(15,0), 
            maxsize decimal(15,0), 
            growth decimal(15,0),
            usage char(12), 
            growth_type char(1) -- '%' percent, 'K' Kb
      )

The #t_serverversioninfo temporary table is used by the sp_rk_audit_dboptions stored procedure:

   create table #t_ serverversioninfo ( 
      [Index] int, 
            [Name] varchar(255), 
            Internal_Value int, 
            Character_Value varchar(255)
      )

The #t_sp temporary table is used by the sp_rk_audit_dboptions and the sp_rk_audit_old_outer_joins stored procedures:

   create table #t_sp([text] varchar(8000) ) 

The #t_logs80 temporary table is used by the sp_rk_audit_dboptions and the sp_rk_audit_tlogs stored procedures:

   create table #t_logs80(
      FileID int, 
      FileSize int, 
      StartOffset int, 
      FSeqNo int, 
      Status int, 
      Parity int, 
      CreateLSN varbinary(48)
   )

The #t temporary table is used by the sp_rk_audit_dboptions and the sp_rk_audit_dboptions_check_1_db stored procedures:

      create table #t (
            [name] varchar(128), 
            dbname varchar(256) default null
      )

If the sp_rk_audit_dboptions stored procedure is called without any parameters, it will call sp_rk_audit_dboptions_check_1_db for each database. Otherwise, it will call the sp_rk_audit_dboptions_check_1_db stored procedure for the specified database only.

sp_rk_audit_dboptions_check_1_db( 
@dbname sysname= NULL, 
@SQLServerVersion int = 70, 
@DebugFlag int = 0)

Most of the code in the sp_rk_audit_dboptions_check_1_db stored procedure examines the result set from sp_dboption, including:

auto update statisticsdbo use only
auto create statisticsoffline
autocloseselect into/bulkcopy
autoshrinktrunc. log on chkpt.

sp_dboption database name will list all of the database options that are set to ON for a database. The #t temporary table created by sp_rk_audit_dboptions stores the list of all options that are set to ON for each database. To check whether a database has an option set to ON, the code must check the #t temporary table. For example, the following code checks to see whether a database has the auto update statistics option set to ON.

   if not exists (select 1 from #t where dbname = @dbname and [name] = 
                       'auto update statistics')
   begin
      select 'database ' + @dbname + ' disables: auto update statistics' + 
                       ' - not recommended'
   end

After checking for a variety of options in the #t temporary table, sp_rk_audit_dboptions_check_1_db checks the database and log backup history stored in msdb.dbo.backupset.

sp_rk_audit_dboptions_check_1_db first checks to see whether the database has been backed up. Then, it stores the backup_finish_date in @db_backup_finish_date. If this value is NULL, the database has never been backed up.

select top 1 @db_backup_finish_date = backup_finish_date
from msdb.dbo.backupset 
where database_name = @dbname and type = 'D' -- Database
order by backup_finish_date desc

if (@db_backup_finish_date is NULL)
begin
   select 'database ' + @dbname + ' has no backup history 
      (either backup not done or msdb data is lost). Ensure you 
            have full db backup!'
end

If the value is not NULL, the stored procedure checks to see whether the database has been backed up in the last seven days:

else if (DATEDIFF(day, @db_backup_finish_date, getdate()) > 7) – 
                  recommend backup at least on weekly basis
begin
   select 'database ' + @dbname + ' has no backup since ' + 
                  convert(varchar(30),@db_backup_finish_date,100) + ' – 
                  Ensure you have full db backup.'
End

The stored procedure then checks to see whether the transaction log has been backed up:

select top 1 @tlog_backup_finish_date = backup_finish_date
from msdb.dbo.backupset 
where database_name = @dbname and type = 'L' -- Log file (Transactional)
order by backup_finish_date desc
if (@tlog_backup_finish_date is NULL)
begin
   select 'database ' + @dbname + ' has no backup LOG history (either 
                      backup log not done or msdb data is lost). Ensure 
                      you have T-LOG backup!'
end 
If the transaction log has been backed up, then the stored procedure 
      checks to see whether it has been backed up within the last day:
else if (DATEDIFF(day, @tlog_backup_finish_date, getdate()) > 1) – 
                  recommend backup at least on daily basis
begin
   select 'database ' + @dbname + ' has no T-LOG backup since ' + 
                  convert(varchar(30),@tlog_backup_finish_date,100) + ' – 
                  Ensure you have periodic T-LOG backup.'
End

The stored procedure then checks the table to ensure that the database and transaction log files are stored on separate logical drives.

Other Stored Procedures Called by sp_rk_audit_dboptions_check_1_db

sp_rk_audit_dboptions_check_1_db then calls a series of other stored procedures:

sp_rk_check_non_dbo_owned_objects
Checks the uid (user ID) column of sysobjects for objects owned by users other than dbo or INFORMATION_SCHEMA.
sp_rk_indexoption
Checks the lockflags column of sysindexes. This indicates whether locking constraints have been set on any indexes.
sp_rk_tableoption
Checks sysobjects for any table options.
sp_rk_pk_uk_fk_index_analysis
Finds tables that do not have a PRIMARY KEY, FOREIGN KEY, or UNIQUE constraint. It also looks for tables on which no indexes are defined.
sp_rk_audit_old_outer_joins
Checks the text of all stored procedures stored in the text column of the syscomments table to find any procedures that are using the old syntax for outer joins (*= or =*).
sp_rk_audit_clustered_wide
Finds tables that have wide clustered and other indexes present.
sp_rk_audit_tlogs
Checks the amount of virtual log files (30+) and growth factor (<10%) of transaction logs.

Modifying sp_rk_audit_dboptions_check_1_db to Look at Different Values

The sp_rk_audit_dboptions_check_1_db stored procedure checks all of the database options by looking at the result set of the sp_dboption stored procedure, which is stored in the #t temporary table. By default, sp_rk_audit_dboptions_check_1_db does not check all of the configurable database options that are queried by sp_dboption. One such option is recursive triggers. If your application uses recursive triggers, which are disabled by default, you can use sp_rk_audit_dboptions_check_1_db to check this option.

To modify sp_rk_audit_dboptions_check_1_db to look at different values

  1. In SQL Query Analyzer, in Object Browser, expand the master database, and then click the Stored Procedures folder.
  2. Right-click sp_rk_audit_dboptions_check_1_db, and then select Edit.

    This loads the stored procedure into a new query window.

    Instead of being in a CREATE PROCEDURE statement, the stored procedure is now in an ALTER PROCEDURE statement. Thus, all existing permissions and dependencies will not be lost.

  3. Find the section of the stored procedure that begins to look at the sp_dboption information (stored in #t). The first part of the code looks like this:
    if not exists (select 1 from #t where dbname = @dbname and [name] = 
                      'auto update statistics')
       begin
          select 'database ' + @dbname + ' disables: auto update 
                      statistics' + ' - not recommended'
          set @nr = @nr + 1
       end
    
  4. Copy the preceding code and paste it directly above the first IF statement.
    Note   This code checks the auto update statistics option. To check the recursive triggers option, replace the auto update statistics with recursive triggers.
    if not exists (select 1 from #t where dbname = @dbname and [name] = 
                   'recursive triggers')
       begin
          select 'database ' + @dbname + ' disables: recursive triggers' + 
                   ' - not recommended'
          set @nr = @nr + 1
       end
    
  5. On the Query menu, select Execute. The screen should look like Figure 5.

     Click here for larger image

    Figure 5. Results of running the code (click image to see larger picture)

Application Troubleshooting

Blocking is one of the common issues a database administrator encounters. The sp_rk_blocker_blockee stored procedure, another stand-alone procedure, lists all of the blocking server process identifiers (SPIDs), their input buffers, the SPIDs that they are blocking, their wait time, and input buffers. The data returned from this stored procedure makes it easy to identify the causes of blocking.

Running sp_rk_blocker_blockee

sp_rk_blocker_blockee has no parameters.

After this stored procedure is installed, it can be run immediately.

In SQL Query Analyzer, in the query window, type sp_rk_blocker_blockee. You will not see any results unless blocking is in progress.

To test the stored procedure with simulated blocking

  1. In SQL Query Analyzer, in the database drop-down list, select pubs, and then open a new query window.
  2. Paste, and then execute the following code:

    Code Example 34.4

    Create table a(
       Col1 int
    ) 
    GO
    insert a values(1)
    
  3. Close the query window.
  4. Open a new query window for the pubs database.
  5. Paste the following code into the query window:

    Code Example 34.5

    Begin tran
       -- Execute this first!
       Update a set Col1 = 1 where Col1 = 1
       -- this will keep this transaction opened for 1 minute
       Waitfor delay '000:01:00'
    Commit tran
    
  6. Open another new query window for the pubs database.
  7. Paste the following code into the query window:

    Code Example 34.6

    Begin Tran
       -- Execute this second!
       Update a set Col1 = 5 - 4 where Col1 = 1
    Commit tran
    
  8. Open one more new query window.

    Select any database from the database drop-down list.

  9. In the query window, type sp_rk_blocker_blockee, but do not execute the stored procedure immediately.
  10. In SQL Query Analyzer, click Window, and then select Tile Vertically so the three query windows are displayed.
  11. Execute the code from Step 5, and then execute the code from Step 7.
  12. Within one minute of executing the code from Step 5, execute the sp_rk_blocker_blockee stored procedure from Step 9. The Results pane should look like Figure 6.

     Click here for larger image

    Figure 6. Results of executing the sp_rk_blocker_blockee stored procedure (click image to see larger picture)

How sp_rk_blocker_blockee Works

sp_rk_blocker_blockee queries the master.dbo.sysprocesses system table. This table is dynamically generated and holds information about the processes that are running on SQL Server. For more information about sysprocesses and the other system tables, see SQL Server Books Online.

Examine the following columns of the sysprocesses table:

  • spid

    Holds the server process ID for the process.

  • blocked

    Is either 0 or holds the SPID of a blocking process.

  • waittime

    Indicates the amount of time that the process has been blocked.

sp_rk_blocker_blockee stores the information it retrieves from the sysprocesses table in two cursors: blocker_cursor and blockee_cursor.

blocker_cursor holds all of the SPIDs that appear at the head of the blocking chains:

DECLARE blocker_cursor CURSOR for
   SELECT spid from master.dbo.sysprocess WHERE spid IN (SELECT blocked
FROM master.dbo.sysprocesses) AND blocked = 0

blockee_cursor holds all blocked processes:

Declare blockee_cursor CURSOR for
SELECT spid, blocked, waittime FROM master.dbo.sysprocesses WHERE 
             blocked > 0

sp_rk_blocker_blockee then loops through the blocker_cursor, each time finding the SPIDs in the blockee_cursor whose blocked columns match the blocker SPID. For each SPID it finds, sp_rk_blocker_blockee lists the wait time and input buffer. To find the input buffer of a process, run DBCC INPUTBUFFER(spid). For more information about the DBCC commands, including DBCC INPUTBUFFER, see SQL Server Books Online.

Modifying sp_rk_blocker_blockee

The code for sp_rk_blocker_blockee does not need much modification. When checking for blocking problems, you must be careful not to add complex logic because this will only make the problem worse. However, if catching the blocking is a problem, use the SQL Server Agent to run sp_rk_blocker_blockee at specified intervals automatically. The SQL Server Agent can be modified to log its results to a table instead of printing them on the screen. For more information about the SQL Server Agent, see "Automating Administrative Tasks" in SQL Server Books Online.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

©2001 Microsoft Corporation. All rights reserved.

Microsoft is a trademark of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Show:
© 2014 Microsoft. All rights reserved.