SQL Server Performance

Run a Trace? Don't bother!!!

Discussion in 'Michael Berry' started by MichaelB, Jan 28, 2008.

  1. MichaelB Member

    [: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
  2. MichaelB Member

    anyone try this?
  3. wimel New Member

    Not working here on 2000:select * from ::fn_get_sql(@handle)results in an error -> Must declare the variable '@handle'.
  4. doniet New Member

    seems to work good from here.
  5. doniet New Member

    sorry.. sent that too soon. using 2005, it works good for us.
  6. MichaelB Member

    Glad to hear it:)

Share This Page