Recently, I had an issue where a server I maintain was randomly "freezing" momentarily. Not for long, but for 20-30 seconds, I would get no response. This was highly noticeable, because the application is a real-time communications application, and the users would momentarily be unable to communicate.
A quick review of the log files showed occasional SQL timeouts. I immediately figured we had some sort of blocking issue, so I decided to fire up SQL Profiler to find the culprit...only to find out that this installation didn't have profiler.
No problem, I figured; I'll just grab that nice standalone profiler from AnjLab. Download, install, run...System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.SqlClrProvider, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. Ouch. Probably not AnjLab's fault - the installation on this server obviouisly has issues.
So, back to the basics. Pure SQL. A few looks on Google and a couple minor adjustments, and I came up with this:
-- get the longest running sql statement
declare @sqltext varbinary(128)
select @sqltext = sql_handle
from sys.sysprocesses
where spid in (
select
top 5 p.spid
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
-- this ridiculous statement orders by the
-- length of processing time, so we get the
-- longest running query first
right(convert(varchar,
dateadd(ms, datediff(ms, P.last_batch,
getdate()),
'1900-01-01'), 121), 12) desc)
select [text] from ::fn_get_sql(@sqltext)
This is a very handy, raw SQL statement that'll get you the top 5 queries currently running, ordered by the length of time they've been running. Pretty basic, but it'll get the job done!