Useful script for performance troubleshooting

--Show the longest running SPIDs on a SQL 2000 server
select
p.spid
, right(convert(varchar,
dateadd(ms, datediff(ms, p.last_batch, getdate()), '1900-01-01'),
121), 12) as 'batch_duration'
, cast(p.program_name as char(20)) as [Program Name]
, cast(p.hostname as char(10)) as [Hostname]
, cast(p.loginame as char(20)) as [Loginame]
from master.dbo.sysprocesses p
where p.spid > 50
and p.status not in ('background', 'sleeping')
and p.cmd not in ('AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER')
order by batch_duration desc

--SQL running for a given spid
declare
@spid int
, @stmt_start int
, @stmt_end int
, @sql_handle binary(20)

set @spid = 65 -- Fill this in

select top 1
@sql_handle = sql_handle
, @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
, @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from master.dbo.sysprocesses
where spid = @spid
order by ecid

SELECT
SUBSTRING( text,
COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end
WHEN -1
THEN DATALENGTH(text)
ELSE
(@stmt_end - @stmt_start)
END
)
FROM ::fn_get_sql(@sql_handle)


--object name containing the page referred by waitresource in sysprocesses
SET NOCOUNT ON declare @dbid int, @fileid int, @pageid int, @spid int, @sql varchar(128)
--set your spid of interest here:
set @spid = 75
select @dbid = substring(waitresource, 1, charindex (':', waitresource) - 1),
@fileid = substring(waitresource, charindex( ':', waitresource) + 1,
charindex(':', waitresource, charindex(':', waitresource) + 1) -
charindex(':',waitresource) - 1 ),
@pageid = substring(waitresource, charindex(':', waitresource,
charindex(':', waitresource, charindex(':', waitresource) + 1)) + 1,
len(waitresource) - (charindex(':', waitresource,
charindex(':', waitresource, charindex(':', waitresource) + 1)) + 1) )
from master..sysprocesses where spid = @spid and waitresource like '%:%:%'
set @sql = 'dbcc page (' + convert(varchar,@dbid) + ',' +
convert(varchar,@fileid) + ',' + convert(varchar,@pageid) + ') with
no_infomsgs, tableresults'
if exists (select 1 from tempdb..sysobjects where xtype = 'U' and name like
'#pageinfo%')
drop table #pageinfo
create table #pageinfo ( ParentObject varchar(128), Object varchar(128),
Field varchar(128), Value varchar(128) )
dbcc traceon (3604) with no_infomsgs
insert into #pageinfo (ParentObject, Object, Field, Value)
exec (@sql) select object_name(Value) as 'waitresource object name' from
#pageinfo where Field = 'm_objId' dbcc traceoff (3604) with no_infomsgs