SELECT query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "平均CPU时间", SUM(query_stats.total_worker_time) AS "总CPU时间", SUM(query_stats.execution_count) AS "执行次数", SUM(query_stats.total_physical_reads) AS "物理读取总次数", SUM(query_stats.total_logical_reads) AS "逻辑读取总次数", SUM(query_stats.total_logical_writes) AS "逻辑写入总次数", MIN(query_stats.statement_text) AS "SQL语句" 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_stats GROUP BY query_stats.query_hash
这些工具要钱的,免费的,只能查部分用c#读取并分析sql2005日志
http://blog.csdn.net/jinjazz/article/details/2783909
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "平均CPU时间",
SUM(query_stats.total_worker_time) AS "总CPU时间",
SUM(query_stats.execution_count) AS "执行次数",
SUM(query_stats.total_physical_reads) AS "物理读取总次数",
SUM(query_stats.total_logical_reads) AS "逻辑读取总次数",
SUM(query_stats.total_logical_writes) AS "逻辑写入总次数",
MIN(query_stats.statement_text) AS "SQL语句"
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_stats
GROUP BY query_stats.query_hash