Tuesday, June 02, 2009

What's The Hold-up?

You know you are having one of those days when you're writing code like this:

select top 5
(select substring(text, (statement_start_offset / 2 ) + 1, ((case when statement_end_offset = -1 then len(convert(nvarchar(max),text)) * 2 else statement_end_offset end ) - statement_start_offset ) / 2 )
from sys.dm_exec_sql_text(sql_handle) ) AS statement
,last_worker_time
,total_worker_time
,last_elapsed_time
,total_elapsed_time
,execution_count
from sys.dm_exec_query_stats
order by last_elapsed_time desc


This technique of pulling from query stats is great for finding those nasty performing culprits, especially in environments when you can't just attach a profiler.

In case you were wondering, I use queries like this instead of sp_who or sp_who2 because it's specifically grabbing the statement that is executing not just the batch.