http://www.cnblogs.com/zerocc/archive/2012/11/30/2796663.html
--- 平均I/O时间长的语句USE tempdbSELECT TOP 10 (total_logical_reads/execution_count) AS avg_logical_reads, (total_logical_writes/execution_count) AS avg_logical_writes, (total_physical_reads/execution_count) AS avg_phys_reads, execution_count, statement_start_offset as stmt_start_offset, SUBSTRING(st.text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) , st.text --FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats AS qsCROSS APPLY SYS.dm_exec_sql_text(qs.sql_handle) AS stORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC /*返回按批执行的 SQL 查询的文本，并提供有关它们的统计信息。摘自msdn , sys.dm_exec_sql_text*/SELECT s2.dbid, s1.sql_handle, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement, execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid is null ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;---------------------------------------/*--CPU 开销较大的 --摘自MSDN*/USE AdventureWorks;GOSELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text"FROM (SELECT QS.*, 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) as ST) as query_statsGROUP BY query_stats.query_hashORDER BY 2 DESC;GO/*另外一种方法计算平均CPU的值 --摘自MSDN sys.dm_exec_sql_text*/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_textFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stORDER BY total_worker_time/execution_count DESC;/*开销较大的查询 ---黄钊吉*/SELECT ss.SUM_execution_count ,t.text ,ss.SUM_total_elapsed_time ,ss.sum_total_worker_time ,ss.sum_total_logical_reads ,ss.sum_total_logical_writesFROM ( SELECT S.plan_handle ,SUM(s.execution_count) SUM_Execution_count ,SUM(s.total_elapsed_time) SUM_total_elapsed_time ,SUM(s.total_worker_time) SUM_total_worker_time ,SUM(s.total_logical_reads) SUM_total_logical_reads ,SUM(s.total_logical_writes) SUM_total_logical_writesFROM sys.dm_exec_query_stats sGROUP BY S.plan_handle) AS ssCROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) tORDER BY sum_total_logical_reads DESC
