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

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

-- 查询性能分析
SELECT TOP 50qs.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 列）