[mssql] 分析SQL Server中执行效率较低的SQL语句

查询性能分析较低的SQL语句

-- 查询性能分析
SELECT TOP 50
    qs.creation_time AS [编译时间],
    qs.last_execution_time AS [最后执行时间],
    qs.execution_count AS [执行次数],
    qs.total_worker_time/1000 AS [CPU总时间(ms)],
    qs.total_elapsed_time/1000 AS [总耗时(ms)],
    (qs.total_elapsed_time/qs.execution_count)/1000 AS [平均耗时(ms)],
    qs.total_logical_reads/qs.execution_count AS [平均逻辑读],
    qs.total_physical_reads/qs.execution_count AS [平均物理读],
    qp.query_plan AS [执行计划],
    CASE 
        WHEN qs.total_elapsed_time/qs.execution_count > 1000 THEN '严重'
        WHEN qs.total_elapsed_time/qs.execution_count > 500 THEN '警告'
        ELSE '正常'
    END AS [性能评级],
    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 [执行语句]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE 
    qs.last_execution_time > DATEADD(HOUR, -24, GETDATE())
    AND st.text NOT LIKE '%sp_%'
    AND st.text NOT LIKE '%FETCH%'
ORDER BY 
    [平均耗时(ms)] DESC,
    [执行次数] DESC;

查看 SQL 执行计划

SET SHOWPLAN_XML ON;
GO
-- SQL语句
GO
SET SHOWPLAN_XML OFF;
GO

执行计划关键解读点：

‌索引使用‌

• ✅ Index Seek：高效索引查找
• ⚠️ Index Scan：可能需优化索引
• ❌ Table Scan：全表扫描警告

‌连接类型‌

• Nested Loops：小数据集适用
• Hash Match：大数据连接内存消耗高
• Merge Join：需排序预处理

‌警告标识‌

• 红色惊叹号：缺失索引/统计信息过期
• 高成本百分比：性能瓶颈节点

💡 优化建议：对出现 Key Lookup 的操作创建覆盖索引（INCLUDE 列）