Tuesday 21 October 2008

IO - SQL Statements with the heaviest impact

Query I have found really useful in tracking down problem sprocs,could be worth running this at regular intervals and storing the results in a database for further analysis.

Select case when sch.name is null then '' else '['+sch.name+'].' end + s3.name as ObjectName
, (select top 1 substring(text,(s1.statement_start_offset+2)/2, (case when s1.statement_end_offset = -1 then len(convert(nvarchar(max),text))*2 else s1.statement_end_offset end - s1.statement_start_offset) /2 ) from sys.dm_exec_sql_text(s1.sql_handle)) as [SQL Statement]
, execution_count
, plan_generation_num
, last_execution_time
, last_worker_time
, total_logical_reads
, last_logical_reads
, last_physical_reads
, total_logical_writes
, last_logical_writes
, execution_count*last_logical_reads as quotient
from sys.dm_exec_query_stats s1
cross apply sys.dm_exec_sql_text(s1.sql_handle) as s2
inner join sys.objects s3 on ( s2.objectid = s3.object_id )
left outer join sys.schemas sch on(s3.schema_id = sch.schema_id)
where s2.dbid = db_id()
and (select top 1 substring(text,(s1.statement_start_offset+2)/2, (case when s1.statement_end_offset = -1 then len(convert(nvarchar(max),text))*2 else s1.statement_end_offset end - s1.statement_start_offset) /2 ) from sys.dm_exec_sql_text(s1.sql_handle)) like '%TransactionParties%'
and last_execution_time > dateadd(minute,-5, current_timestamp)
order by execution_count*last_logical_reads desc

No comments: