BackgroundHere’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
 Microsoft 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 |
. Just download from 
. Now I use this tool to defrag my PC and my laptop too.
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.
.
)