What is using resources...now!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

What is using resources...now!

Author

Topic

datagod
Starting Member

37 Posts

Posted - 2006-05-05 : 16:51:39

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.

[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
-- 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

[/CODE]

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-05-06 : 07:50:04

What do you look for when you run this.

datagod
Starting Member

37 Posts

Posted - 2006-05-06 : 09:52:21

Each process running on the database server that is currently consuming resources (CPU / disk io) will be displayed. You will see a summary of the process, along with SQL code that the process is executin (taken from SQL cache).

Each active process is separated by a line "OxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxO"

Every time you run it, you will notice your own process. There is a line you can un-comment to prevent your own process from displaying.

The next time somebody runs a big transaction, run this code and you will see some cool results. It is a forensic tool that is great for removing the blame from the SQL box.

MANY times I have been told "The Server is slow" when in fact it is the client's network connection, not the server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources