Search This Blog & Web

Monday, June 1, 2009

Finding Slow Runnig Quries

SELECT top 10
ProcedureName = o.name,
[Procedure] = t.text,
ExecutionCount = s.execution_count,
AvgExecutionTime = isnull( s.total_elapsed_time / s.execution_count, 0 ),
AvgWorkerTime = s.total_worker_time / s.execution_count,
TotalWorkerTime = s.total_worker_time,
MaxLogicalReads = s.max_logical_reads,
LastLogicalReads = s.last_logical_reads,
MaxLogicalWrites = s.max_logical_writes,
LastLogicalWrites = s.last_logical_writes,
CreationDateTime = s.creation_time,
MaxPhysicalReads = s.max_Physical_reads,
LastPhysicalReads = s.last_Physical_reads,
CallsPerSecond = isnull( s.execution_count / datediff( second, s.creation_time, getdate()), 0 )
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
INNER JOIN SYS.objects o on t.objectid = o.object_id
ORDER BY
AvgExecutionTime DESC



-- select * from sys.databases

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text,
st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
Post a Comment