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

最新推荐文章于 2023-08-07 10:35:44 发布

jxufewbt

最新推荐文章于 2023-08-07 10:35:44 发布

阅读量2k

点赞数

本文链接：https://blog.youkuaiyun.com/jxufewbt/article/details/5997663

版权

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;