查询SQL Server 2008运行效率情况(在master数据库下执行)

最新推荐文章于 2025-12-03 13:04:04 发布

最新推荐文章于 2025-12-03 13:04:04 发布 · 151 阅读

文章标签：

#数据库

本文介绍了一个具体的SQL查询优化案例，通过使用TOP 200、GROUP BY和其他聚合函数来分析和优化数据库查询性能。该查询从dm_exec_query_stats中提取前200条最消耗资源的SQL语句，并按CPU时间降序排列。

SELECT TOP 200 query_stats.query_hash AS "Query Hash",
Sum(Query_Stats.total_logical_Reads) as Total_Logical_Reads,
Sum(Query_Stats.total_physical_Reads) as Total_physical_Reads,
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
SUM(query_stats.execution_count) as CNT, SUM(query_stats.total_worker_time) as "Total 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_stats
GROUP BY query_stats.query_hash
ORDER BY 4 DESC;