Run a Trace? Don’t bother!!! | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Run a Trace? Don’t bother!!!

[:p]Someone tells you that the DB is running slow. What is the first thing you do? Run sp_who2? Fire up a trace?
While these are all good things in themselves, a DBA needs a better tool! I have one that I got from some website (so long ago, I dont recall where). I call this proc NOW. It can be run on any server at any time (I used it on both 2000 and 2005). You need to have sysadmin rights to use it, but it will tell you THE EXACT STATEMENTS RUNNING – in a heatbeat! It is great for knowing what procs are killing the server and which are using a lot of IO, etc. The trick is to run it with text results, not in a grid! It will put out an easy to read display that will give you no trouble:)
Just use it once in text mode and let me know what you think of it? Wish I could take credit for it![:p]USE [DBA]
GOSET
ANSI_NULLS ONGOSET
QUOTED_IDENTIFIER ONGOCREATE
PROCEDURE [dbo].[now]as
set nocount on
declare
@handle binary(20), @spid
smallint,@rowcnt
smallint,@output varchar
(500)declare ActiveSpids CURSOR FOR
select
sql_handle, spidfrom master.dbo.sysprocesses
where sql_handle not in (0x0000000000000000000000000000000000000000)
and spid <> @@SPIDand (status not in(‘sleeping’)
or
upper(cmd) not in (
‘AWAITING COMMAND’
,’LAZY WRITER’,’CHECKPOINT SLEEP’)
)order by cpu desc
OPEN
ActiveSpidsFETCH
NEXT FROM ActiveSpidsINTO
@handle,@spidset
@rowcnt = @@CURSOR_ROWSprint
‘====================’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 ‘loginame’ = left(loginame, 30),
‘hostname’ = left(hostname,30),
‘database’ = left(db_name(dbid),30),’spid’ = str(spid,4,0),
‘block’ = str(blocked,5,0), ‘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)
–into #working1
from master..sysprocesses
where spid = @spid–and status <> ‘sleeping’
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,@spidEND
close
ActiveSpidsdeallocate
ActiveSpids

anyone try this?
Not working here on 2000:select * from ::fn_get_sql(@handle)results in an error -> Must declare the variable ‘@handle’.
seems to work good from here.
sorry.. sent that too soon. using 2005, it works good for us.
Glad to hear it:)
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |