View performance-poor statements in SQL Server 2008

Use this statement frequently to view poorly-performing SQL statements:

SELECT creation_time N ' statement compile time '

, Last_execution_time N ' last Execution time '

, total_physical_reads N ' total number of physical reads '

, Total_logical_reads/execution_count N ' per logical read times '

, the total number of total_logical_reads N ' logical reads '

, Total_logical_writes N ' logical write total number of times '

, Execution_count N ' execution times '

, total_worker_time/1000 N ' CPU total time MS '

, total_elapsed_time/1000 N ' total time spent MS '

, (total_elapsed_time/execution_count)/1000 N ' Average Time MS '

, 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) N ' execute statement '

From Sys.dm_exec_query_stats as Qs

CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) St

where 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) Not like '% fetch% '

and qs.execution_count>10000 and

(Total_elapsed_time/execution_count)/1000>150

Order by Total_elapsed_time/execution_count DESC;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/