Here is a SQL stored procedure (sp_Now) that determines what processes are currently executing and consuming resources on a database server. It’s helpful in troubleshooting sporadic performance problems, especially in an environment where applications span multiple servers.
If a user calls and complains that the database server is slow, you can run this script to quickly determine what the database server is actually doing. If the database server is under a heavy load due to SQL activity, you will be able to see the exact SQL code that is causing the load.
Perhaps a user submitted a poorly coded query (e.g., creating a Cartesian product of the three biggest tables) or there is a scheduled job running a report during the day which really should only be submitted during off-hours.
If as a result of running the sp_Now script no SQL activity is shown, you can be pretty sure that the database server is not to blame. You can then work with the user to determine if the problem is occurring at the application or network level.
Note: it uses the fn_get_sql function, which Microsoft introduced in SQL Server 2000 Service Pack 3 (SP3).
This function shows the SQL statements that a particular process is currently executing. The sp_Now stored procedure uses fn_get_sql to build a cursor of all currently active processes.
For each active process, sp_Now produces a report that shows summary information such as the process’s cumulative disk reads and writes (phys_io), the process’s CPU usage (cpu), and the application’s name (program_name) from the sysprocesses table. The report also includes the exact SQL statements that the process is executing.
The output displays the total number of active SPIDs (System Process IDs that have open SQL connections and are currently processing data) followed by two lines of ‘x’s which are used to separate the details of what each SPID is doing.
Next we have a summary from the sysprocesses table which shows the loginame, hostname and other details of the SQL connection associated to a particular SPID. Included in this section is a summary of the connection’s CPU usage, the total amount of disk i/o, and the SPID of any other process that is being blocked by this process. (Multiple entries for the same SPID in this section indicates that the SQL Server has divided the query up among multiple CPU’s in an attempt increase the performance via parallel processing)
The next section contains the output of “dbcc inputbuffer ()” for the SPID being examined, which will display the first 255 characters of the query.
The last section contains the output of a call to the system function “fn_get_sql” which shows a much larger portion of the SQL code being executed (usually the entire object in the case of a stored procedure).
When reviewing the output of sp_Now, pay attention to the amount of physical I/O and CPU time the processes are using. Also take note of any blocking.
Remember, if processes are consuming resources they will show up in the results. If nothing shows up in the results you can be pretty sure that the SQL Server is not experiencing performance problems.
Source code:
use master
IF (object_id('sp_Now')) is not null
BEGIN
PRINT 'Dropping: sp_Now'
DROP PROCEDURE sp_Now
END
PRINT 'Creating: sp_Now'
GO
CREATE PROCEDURE sp_Now
as
set nocount on
declare @handle binary(20),
@spid smallint,
@rowcnt smallint,
@output varchar(500),
@blocks int,
@spids int
declare ActiveSpids CURSOR FOR
select sql_handle, spid
from sysprocesses
where sql_handle <> 0x0000000000000000000000000000000000000000
and spid <> @@SPID
order by cpu desc
OPEN ActiveSpids
FETCH NEXT FROM ActiveSpids
INTO @handle,
@spid
set @rowcnt = @@CURSOR_ROWS
print '===================='
print '= CURRENT ACTIVITY ='
print '===================='
print convert(char(19),getdate(),120)
print ' '
print 'Active SPIDs: ' + convert(varchar(8),@rowcnt)
-- Blocking processes summary
select @blocks = count(*) from master..sysprocesses where blocked > 0
print 'Blocked SPIDs: ' + convert(varchar(8),@blocks)
select @spids = count(*) from master..sysprocesses
print 'Total SPIDs: ' + convert(varchar(8),@spids)
IF (@blocks > 0)
BEGIN
print ' '
print ' '
print 'Blocked Process Summary'
print '-----------------------'
print ' '
select 'loginame' = left(loginame, 30),
'hostname' = left(hostname,30),
'database' = left(db_name(dbid),30),
'spid' = str(spid,4,0),
'block' = str(blocked,5,0),
'waittime' = waittime,
'phys_io' = str(physical_io,8,0),
'cpu(mm:ss)' = str((cpu/1000/60),6) + ':' +
case
when left((str(((cpu/1000) % 60),2)),1) = ' '
then stuff(str(((cpu/1000) % 60),2),1,1,'0')
else str(((cpu/1000) % 60),2)
END ,
'mem(MB)' = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2),
'program_name' = left(program_name,50),
'command' = cmd,
'lastwaittype' = left(lastwaittype,15),
'login_time' = convert(char(19),login_time,120),
'last_batch' = convert(char(19),last_batch,120),
'status' = left(status, 10),
'nt_username' = left(nt_username,20)
from master..sysprocesses
where blocked > 0
print ' '
print ' '
END
WHILE (@@FETCH_STATUS = 0)
BEGIN
print ' '
print ' '
print 'O' + replicate('x',120) + 'O'
print 'O' + replicate('x',120) + 'O'
print ' '
print ' '
print ' '
select 'loginame' = left(loginame, 30),
'hostname' = left(hostname,30),
'database' = left(db_name(dbid),30),
'spid' = str(spid,4,0),
'block' = str(blocked,5,0),
'waittime' = waittime,
'phys_io' = str(physical_io,8,0),
'cpu(mm:ss)' = str((cpu/1000/60),6) + ':' +
case
when left((str(((cpu/1000) % 60),2)),1) = ' '
then stuff(str(((cpu/1000) % 60),2),1,1,'0')
else str(((cpu/1000) % 60),2)
END ,
'mem(MB)' = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2),
'program_name' = left(program_name,50),
'command' = cmd,
'lastwaittype' = left(lastwaittype,15),
'login_time' = convert(char(19),login_time,120),
'last_batch' = convert(char(19),last_batch,120),
'status' = left(status, 10),
'nt_username' = left(nt_username,20)
from master..sysprocesses
where spid = @spid
print ' '
print ' '
-- Dump the inputbuffer to get an idea of what the spid is doing
dbcc inputbuffer(@spid)
print ' '
print ' '
-- Use the built-in function to show the exact SQL that the spid is running
select * from ::fn_get_sql(@handle)
FETCH NEXT FROM ActiveSpids
INTO @handle,
@spid
END
close ActiveSpids
deallocate ActiveSpids
GO
IF (object_id('sp_Now')) is not null
PRINT 'Procedure created.'
ELSE
PRINT 'Procedure NOT created.'
GO
