View performance-poor statements in SQL Server 2008

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

SELECTCreation_time N'Statement Compilation Time', Last_execution_time N'Last Execution Time', Total_physical_reads N'total number of physical reads', Total_logical_reads/Execution_count N'each logical read Count', Total_logical_reads N'total number of logical reads', Total_logical_writes N'total number of logical writes', Execution_count N'Number of executions', Total_worker_time/ +N'total CPU time used by Ms', Total_elapsed_time/ +N'Total time spent Ms', (Total_elapsed_time/Execution_count)/ +N'Average Time Ms'        ,SUBSTRING(St.text, (Qs.statement_start_offset/2)+ 1,         (( CaseStatement_end_offset when -1  Then datalength(St.text)          ELSEQs.statement_end_offsetEND            -Qs.statement_start_offset)/2)+ 1) N'EXECUTE Statement' fromSys.dm_exec_query_stats asQS CrossAPPLY sys.dm_exec_sql_text (qs.sql_handle) Stwhere SUBSTRING(St.text, (Qs.statement_start_offset/2)+ 1,         (( CaseStatement_end_offset when -1  Then datalength(St.text)          ELSEQs.statement_end_offsetEND            -Qs.statement_start_offset)/2)+ 1) not  like '%fetch%'              andQs.execution_count>10000  and(Total_elapsed_time/Execution_count)/ +> MaxORDER  byTotal_elapsed_time/Execution_countDESC;

Transferred from: http://blog.csdn.net/yangzhawen/article/details/7239392

View performance-poor statements in SQL Server 2008