Hi to all,
I had faced a problem few days back. One of my stored procedure had a Time out issue. So I wanted to know which query is taking more time and how to minimize the execution time of the same.
So I started to analyze this and I found some interesting facts.
System tables (sys.dm_exec_query_stats) has some data about queries which we are executing.
By using this table I found the Query name,execution time,Maximum elapsed time, Average elapsed time, Log created on and Frequency per sec.
So I hope you get my point.
Here is the sample query
SELECT DISTINCT TOP 100
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO
Cheers to all.
