Analyzing Long-Running SQL Queries without Profiler

by jerod.venema 17. February 2012 22:48

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=, 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 (
    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
                  dateadd(ms, datediff(ms, P.last_batch, 
                  '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!