What Is Using Resources...now!

This proc will show you what is using SQL system resources right now. If a process is not currently using any resources, it will not show up on the list. Also, a dump of the input buffer is printed so you can see just what is going on.

I use this code whenever somebody whines that "The server is slow!".

The magic is ::fn_get_sql which came with service pack 3 for sql 2000.





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
-- FULL DETAILS OF WHAT IS RUNNING
set nocount on
declare @handle binary(20),
@spid smallint,
@rowcnt smallint,
@output varchar(500)

DECLARE TEST CURSOR FOR
select sql_handle, spid
from sysprocesses
where sql_handle <> 0x0000000000000000000000000000000000000000
-- and spid <> @@SPID --> Uncomment to stop seeing your own process
order by cpu desc

OPEN TEST
FETCH NEXT FROM TEST
INTO @handle,
@spid


set @rowcnt = @@CURSOR_ROWS

print '===================='
print '= CURRENT ACTIVITY ='
print '===================='
print ' '
set @output = 'ACTIVE SPIDS: ' + convert(varchar(4),@rowcnt)
print @output


WHILE @@FETCH_STATUS = 0
BEGIN
print ' '
print ' '
print 'O' + replicate('x',120) + 'O'
print 'O' + replicate('x',120) + 'O'
print ' '
print ' '
print ' '



select convert(char(15), loginame) as 'loginame',
convert(char(10),hostname) as 'hostname',
convert(char(20),db_name(dbid)) 'database',
str(spid,4,0) 'spid',
str(blocked,5,0) 'block',
-- str(waittime,9,0) 'wait_time',
str(physical_io,8,0) 'phys_io',
-- str((cpu),10,0) 'cpu(ms)',
-- str((cpu/60000.0),9,3) 'cpu(mins)',
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 as 'cpu(mm:ss)',
str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2) 'mem(MB)',
convert(char(30),program_name) as 'program_name',
cmd,
convert(char(15),lastwaittype) 'lastwaittype',
convert(char(20),login_time,120) 'login_time',
convert(char(19),last_batch,120) 'last_batch',
convert(char(10),status) as 'status',
convert(char(15),nt_username) as 'nt_username'
from master..sysprocesses
where spid = @spid
print ' '
print ' '
dbcc inputbuffer(@spid)
print ' '
print ' '
select * from ::fn_get_sql(@handle)
FETCH NEXT FROM TEST
INTO @handle,
@spid
END
close TEST
deallocate TEST
GO
IF (object_id('sp_Now')) is not null
PRINT 'Procedure created.'
ELSE
PRINT 'Procedure NOT created.'
GO