DBA and SysAdmin World

September 18, 2008

spWho3 active

Filed under: Script — Tags: , — solihinho @ 12:15 AM
question Background
Here’s some common conversations that DBA faces almost everyday
User : What’s going on the database? Why does my report be longer than usually?
DBA : Okay, just let me check. Please wait a second.

What should DBA do?
First thing that DBA should do is check what processes are running in the server.I believe SQL Server DBA know  these SPs that provide from SQL Server is sp_who or sp_who2 (undocumented in SQL Server BOL). sp_who2 is enhancement from sp_who. Both of these stored procedures provide information about processes in the SQL server. We can filter the information to show not the idle processes by using active parameter.
Usage : sp_who2 active

After you type sp_who2 active and run it, it will show us the SP id, user who run this process, host name, program name, CPU time, disk IO etc. The culprit which must be checked is the process with biggest CPU time. Not always but nice to check. Unfortunately, sp_who or sp_who2 does not provide what SP name or query statement is running. To know the SP or query statement, we use DBCC INPUTBUFFER (SPID).

Let me repeat the steps, first we run sp_who2 active. From this SP we get all the processes which is running on that server. Look at the biggest CPU time or disk IO process. Note the SP ID, than run DBCC INPUTBUFFER with parameter SP ID of the process.  You will get the statement which is running on that server. So you can analyze this statement if possibly a culprit or not. i.e : look at the parameter of the statement. Sometimes user run a report with big period.

The issue is sometimes we are not so lucky to find the culprit. We have to run the sp_who2 active and DBCC INPUTBUFFER several times. So why we don’t enhance sp_who2 so that can display the statement when we run sp_who2. In our office, we call it spWho3

IdeaMicrosoft doesn’t recommend to use the prefix “sp_” when we create SP in user-created database because SQL Server look for SP with prefix “sp_” in the following order : master database than user-created database.

Script
This script is enhancement of sp_who2. This script for display active processes only and data order by CPU time descending. You can modified this script depend on your necessity.

SET NOCOUNT ON    

DECLARE
    @SPID	    SMALLINT
   ,@SQLID          SMALLINT
   ,@SqlStatement   VARCHAR(4000) 

IF object_id('tempdb..#tbl_sysprocesses') IS NOT NULL
	DROP TABLE #tbl_sysprocesses     

IF object_id('tempdb..#spid_cmds') IS NOT NULL
	DROP TABLE #spid_cmds     

IF object_id('tempdb..#spid') IS NOT NULL
	DROP TABLE #spid

SELECT
  spid
 ,CAST(NULL AS VARCHAR(8000)) AS commandtext
 ,status
 ,sid
 ,hostname
 ,program_name
 ,cmd
 ,cpu
 ,physical_io
 ,blocked
 ,dbid
 ,convert(SYSNAME, rtrim(loginame)) AS loginname
 ,substring(convert(VARCHAR,last_batch,111) ,6  ,5 )
		+ ' ' +  substring( convert(VARCHAR,last_batch,113) ,13 ,8 )
       as 'last_batch_char'
 INTO   #tbl_sysprocesses
 FROM   master.dbo.sysprocesses (NOLOCK)
 WHERE  NOT(lower(status)  = 'sleeping'
         	AND upper(cmd) IN (
             'AWAITING COMMAND'
            ,'MIRROR HANDLER'
            ,'LAZY WRITER'
            ,'CHECKPOINT SLEEP'
            ,'RA MANAGER' )
         	AND blocked = 0)

--Show only active processes
DELETE #tbl_sysprocesses
WHERE lower(status)  = 'sleeping'
   AND upper(cmd) IN  (
        'AWAITING COMMAND'
       ,'LAZY WRITER'
      ,'CHECKPOINT SLEEP' )     AND blocked = 0  

CREATE TABLE #spid_cmds
(
     SQLID       INT IDENTITY
    ,SPID        INT
    ,EventType   VARCHAR(30)
    ,Parameters  INT
    ,Command     VARCHAR(8000)
)       

SELECT DISTINCT spid INTO #spid FROM #tbl_sysprocesses    

WHILE (1 = 1)
BEGIN
   SET @SPID = NULL
   SELECT TOP 1 @SPID = spid FROM #spid 	     

   IF (@SPID IS NULL) GOTO _NEXT

   INSERT INTO #spid_cmds (EventType, Parameters, Command)
      EXEC('DBCC INPUTBUFFER( ' + @SPID + ')')    

   SELECT @SQLID = MAX(SQLID) FROM #spid_cmds    

   UPDATE #spid_cmds SET spid = @SPID WHERE SQLID = @SQLID      

   DELETE FROM #spid WHERE spid = @SPID
END    

_NEXT:

UPDATE p
SET p.commandtext = s.command
FROM #tbl_sysprocesses p
INNER JOIN #spid_cmds s ON p.spid = s.spid    

SET @SqlStatement =
'
   SELECT     SPID          = spid
     	    ,CommandText
             ,Status        =
                  rtrim(CASE lower(status)
                     When ''sleeping'' THEN lower(status)
                     Else                   upper(status)
                  END)
            ,Login         = loginname
            ,HostName      =
                  CASE hostname
                     When NULL  THEN ''  .''
                     When '' '' THEN ''  .''
                     Else    hostname
                  END
            ,BlkBy         =
                  CASE ISNULL(blocked,0)
                     When 0 THEN 0
                     Else blocked
                  END
            ,DBName        = case when dbid = 0 THEN NULL
			    when dbid <> 0 THEN db_name(dbid) END
            ,Command       = cmd
            ,CPUTime       = cpu
            ,DiskIO        = physical_io
            ,LastBatch     = last_batch_char
            ,ProgramName   = program_name
      FROM  #tbl_sysprocesses
      ORDER BY CPUTime DESC
'
EXEC(@SqlStatement)

DROP TABLE #tbl_sysprocesses
DROP TABLE #spid_cmds
DROP TABLE #spid  

SET NOCOUNT ON
Advertisements

4 Comments »

  1. Very useful script. nice work!!

    Comment by Sam Ratatouille — September 30, 2008 @ 10:43 PM

  2. Very good and useful script!!

    Comment by Nilesh — January 27, 2009 @ 5:28 PM

  3. 1. Why are you jumping (GOTO) out of a loop while you can just BREAK?
    2. Why are you using dynamic sql to EXECute the @SqlStatement? It seems to run fine when executing directly..

    Comment by Tom Shaeffer — March 16, 2009 @ 6:21 PM

    • Thanks for your comment Tom
      1. You’re right we can use BREAK for this case. I think it’s only about scripting style :)
      2. Wow thanks for this correction. You’re rite again. That’s better for this case. Actually on my original script I put where statement “where login like ‘ + @loginname which is need dynamic sql. I just try to share simpler script.

      Comment by solihinho — March 18, 2009 @ 10:47 PM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: