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

September 13, 2008

Defragmentation Tools

Filed under: Tools — Tags: , — solihinho @ 10:27 AM
logo Issue
These few days, disk fragmentation on our servers become an important issue. Some case prove that when fragmentation occur, the backup on that server become more slowly than ever besides make your server/PC not in the best performance. Actually we have scheduled to defrag the C drive  every month using windows defrag command line. Unfortunately some drive still fragmented.

Recommended Tool
I found Defraggler, tool for defragmenting disk. This tool is very great. It can defrag not only whole disk, but it is also able defrag individual files. Some of my servers which is before can not be defrag with windows defrag tool, should be no issue with this tool. It also give visualization that allowing us to visually see the location of file on the disk. Last but not least, this tool is completely free for both corporate and individual use Applause. Just download from http://www.defraggler.com

Thank for Piriform Ltd for the nice tool Not worthy. Now I use this tool to defrag my PC and my laptop too.

df1 df4

September 12, 2008

System Administrator Command Line

Filed under: Tools — Tags: , , — solihinho @ 11:36 PM
images As a Windows 2K/2K3 System Administrator, there are some command lines that a we need to know to help us solving the problem or to show information of the server or network. Go to windows command prompt by type “cmd” from Start -> Run to go into console mode.

Here some command line that  is useful for a Windows System Administrator. Some command lines also exist in UNIX system.

  1. ping <hostname>
    i.e : ping www.yahoo.com
    When SA want to check if the server reachable over the network or not, ping should be the only command can be used. If there is a reply message that mean the connection to the server is OK but if there is message request time out, something wrong happen to that server connection. It can be caused by many matters like hardware failure, the network cable unplug, the server is down etc. You can add parameter -t to test connection to the server until you press ctrl+C.
  2. IPConfig/all
    This command used for display all current TCP/IP network configuration such as IP address, MAC Address etc.
  3. IPConfig/flushdns
    Purges the DNS Resolver cache.
  4. nbstat-a <hostnamename>
    Returns the NetBIOS name table and MAC address of the address card for the computer name specified. Few days ago, our network team ask  us to collect all our server’s MAC Address. There are 2 ways to do this task. We can do it by remote console to the server one by one and use command ipconfig/all or use NBTSTAT command from our local PC Cool.
  5. tasklist
    This command line for displays list of applications / processes currently running either a local or remote server. To display remote server processes use Tasklist /s <hostname>.
  6. taskkill
    This command used to end one or more processes. We can kill the process by the process id or image name. To further information used this command type taskkill/?.
  7. tracert <destination server>
    used to determine the route taken by packets across an IP network.
  8. nslookup
    to find various details relating to DNS. i.e :  when you type nslookup www.google.com, you can see the IP address that used by www.google.com. You can also type nslookup <IP Address> to  get the server name which is used that IP address.
  9. netstat
    displays the active TCP connections and ports on which the computer is listening.
IdeaMostly the command lines have a documentation for help us see what parameter that we can use on the command line. Just type command line/? i.e : taskkill/?

September 8, 2008

Useful SQL Server Commands

Filed under: SQL Server — Tags: , — solihinho @ 1:08 AM
SQL Server
Since I am be a DBA, there are some system SPs (Stored Procedures) and DBCC (Database Console Commands) that I use regularly on my daily activities for display some information and for maintain the database.

Commands For Display Information

  • Reports information about a specified database or all databases
    use : sp_helpdb
  • Reports information about the indexes on a table or view
    use : sp_helpindex
  • Returns statistics information about columns and indexes on the specified table
    use : sp_helpstats
  • Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure
    use : sp_helptext
  • Reports information about a database object
    use : sp_help
  • Returns the physical names and attributes of files associated with the current database. use : sp_helpfile
  • Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause
    use : sp_tables
  • Returns column information for the specified tables or views that can be queried in the current environment
    use : sp_columns
  • Reports information about locks
    use : sp_lock
  • Displays the number of rows, disk space reserved, and disk space used by a table
    use : sp_spaceused
  • Displays or changes global configuration settings for the current server
    use : sp_configure
  • Provides information about current users and processes
    use : sp_who or sp_who2 (undocumented on BOL Rolling Eyes)
  • Displays the current distribution statistics for the specified target on the specified table
    use : DBCC SHOW_STATISTICS
  • Displays fragmentation information for the data and indexes of the specified table
    use : DBCC SHOWCONTIG
  • Provides statistics about how the transaction-log space was used in all databases
    use : DBCC SQLPERF
  • Displays the last statement sent from a client
    use : DBCC INPUTBUFFER

Commands For Database And Server Maintenance

  • Rebuilds one or more indexes for a table in the specified database
    use : DBCC DBREINDEX
  • Defragments indexes of the specified table or view
    use : DBCC INDEXDEFRAG
  • Runs UPDATE STATISTICS against all user-defined and internal tables in the current database
    use : sp_updatestats
  • Creates single-column statistics for all eligible columns for all user tables and internal tables in the current database. The new statistic has the same name as the column where it is created.
    use : sp_createstats
  • Shrinks the size of the data files in the specified database
    use : DBCC SHRINKDATABASE
  • Shrinks the size of the specified data file or log file for the related database
    use : DBCC SHRINKFILE
  • Closes the current error log file and cycles the error log extension numbers just like a server restart. The new error log contains version and copyright information and a line indicating that the new log has been created
    use : sp_cycle_errorlog or DBCC ERRORLOG
Idea We can look into the code of the system SP by using sp_helptext ‘SP name’ on master database. Sometimes we need add some information that not provide on those SP i.e : we can combine sp_who2 and DBCC INPUTBUFFER become more useful command (I will discuss about this later). So just look into on the script, modified it and save as become another SP. Don’t replace current SP.

Create a free website or blog at WordPress.com.