Wednesday, April 21, 2010

Gather SQL execution time stat from DMV within performance testing period

after each round of performance testing, i will gather SQL execution time stat from DMV during testing period. it gives me insight on top slowness SQLs which i can focus on first:

SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

where creation_time > '2010-04-20 14:30:57.553' and creation_time <'2010-04-20 15:35:57.553'

ORDER BY total_elapsed_time / execution_count DESC;


thanks for this thread, i take the SQL from here and just add creation_time filter: http://www.sql-server-performance.com/articles/per/tsql_statement_performance_p1.aspx

No comments:

Post a Comment