One easy thing to check is to see if a resource is getting used up. Use:
sp_monitorconfig "all"
If you're looking for a bad query, you can use mon tables, but there are a lot of them. Here's a basic query I use for live monitoring (ie. for currently running queries):
set nocount on
go
set flushmessage on
go
select * into #old from monProcessActivity
waitfor delay "00:00:05"
select * into #new from monProcessActivity
while(1=1)
begin
select new.SPID, login=suser_name(new.ServerUserID),
CPUTime=new.CPUTime-old.CPUTime,
PhysicalReads=new.PhysicalReads-old.PhysicalReads, LogicalReads=new.LogicalReads-old.LogicalReads,
PhysicalWrites=new.PhysicalWrites-old.PhysicalWrites, new.LocksHeld
from #old old, #new new
where old.SPID=new.SPID and old.KPID=new.KPID
and ( new.CPUTime-old.CPUTime>0 or new.PhysicalReads-old.PhysicalReads>0 or
new.LogicalReads-old.LogicalReads>0 or new.PhysicalWrites-old.PhysicalWrites>0 )
order by new.CPUTime-old.CPUTime desc
truncate table #old
insert into #old select * from #new
truncate table #new
waitfor delay "00:00:05"
-- Whoops, missed this line. Added in a later edit:
insert into #new select * from monProcessActivity
end
go
You can also use monProcessSQLText to see what SQL is running for a given spid (make sure to sort by SPID,BatchID, LineNumber SequenceInLine)
If you want to look at historical data, it's a little more complicated. You can use monCachedProcedures to see accumulated stats for cached procedures (stats go away if the stored proc gets pushed out of cache. There can be multiple cached copies of the same stored proc). There's also monCachedStatement, but you have to have the statement cache enabled for this to work. Cached statements can sometimes have "needs to be recompiled" type side effects (cached statement scans when it shouldn't)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.