SQL Server 性能问题排查指南

在 SQL Server 进行性能问题排查时，通常涉及 死锁（Deadlock）、慢查询（Slow Query）、CPU/内存瓶颈、索引优化、IO 性能问题 等。本文总结了常见的性能问题排查思路，并提供了相应的 SQL 语句，帮助 DBA 快速定位和解决问题。

1. 死锁（Deadlock）排查

排查思路

启用死锁跟踪，记录详细的死锁日志：
```
DBCC TRACEON (1222, -1);
```
查看 SQL Server 错误日志，查找死锁信息：
```
EXEC sp_readerrorlog;
```
查询当前锁状态：
```
SELECT * FROM sys.dm_tran_locks;
```

使用 Extended Events 捕获死锁：

CREATE EVENT SESSION Deadlock_Monitor
ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (SET filename='C:\DeadlockTrace.xel')
WITH (STARTUP_STATE=ON);
ALTER EVENT SESSION Deadlock_Monitor ON SERVER STATE = START;

优化方案

优化事务：缩短事务时间，减少锁争用。
索引优化：确保查询使用适当的索引，减少锁等待。
避免交叉锁定资源：按固定顺序访问表，减少死锁概率。
调整隔离级别：使用 NOLOCK 或 READ COMMITTED SNAPSHOT 适用于读操作。

2. 慢查询（Slow Query）排查

排查思路

查找执行时间较长的 SQL：

SELECT TOP 10 total_elapsed_time / 1000 AS ElapsedTime_ms,
              total_worker_time / 1000 AS CPUTime_ms,
              execution_count,
              text AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
ORDER BY total_elapsed_time DESC;

查看最近执行过的慢 SQL：

SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count / 1000 AS AvgTime_ms,
    qs.execution_count,
    qt.text AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)
ORDER BY AvgTime_ms DESC;

查询等待事件（Wait Statistics）：

SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

优化方案

优化 SQL 语句：避免 SELECT *，减少不必要的计算。
优化索引：确保查询使用索引，避免全表扫描。

更新统计信息：

UPDATE STATISTICS table_name WITH FULLSCAN;
DBCC DBREINDEX ('table_name');

使用 OPTION (RECOMPILE) 解决参数嗅探问题：

SELECT * FROM Orders WHERE OrderDate >= @Date OPTION (RECOMPILE);

3. CPU / 内存瓶颈排查

排查思路

查询 CPU 使用率最高的 SQL：

SELECT TOP 10
    total_worker_time / execution_count / 1000 AS AvgCPUTime_ms,
    execution_count,
    text AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)
ORDER BY AvgCPUTime_ms DESC;

查看 CPU 使用率：

SELECT * FROM sys.dm_os_schedulers WHERE scheduler_id < 255;

检查内存使用情况：

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory%';

优化方案

调整最大并行度（MAXDOP）：

EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

优化索引，减少 CPU 计算开销。
避免 LIKE '%xxx%'，改为 LIKE 'xxx%' 以减少 CPU 负载。

4. 索引优化

排查思路

查找缺失索引：

SELECT * FROM sys.dm_db_missing_index_details;

查找未使用的索引：

SELECT name, user_seeks, user_scans, user_lookups
FROM sys.dm_db_index_usage_stats us
JOIN sys.indexes i ON i.object_id = us.object_id AND i.index_id = us.index_id
WHERE user_seeks = 0 AND user_scans = 0;

查看索引碎片：

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE avg_fragmentation_in_percent > 30;

优化方案

创建缺失索引：

CREATE INDEX idx_column ON table_name (column_name);

删除未使用的索引：
```
DROP INDEX index_name ON table_name;
```

重建或重组索引：

ALTER INDEX index_name ON table_name REBUILD;
ALTER INDEX index_name ON table_name REORGANIZE;

5. IO / 磁盘性能瓶颈

排查思路

查看数据库文件 IO 负载：

SELECT DB_NAME(database_id) AS DatabaseName,
       file_id,
       io_stall_read_ms,
       io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY io_stall_read_ms DESC;

检查 TempDB 磁盘使用情况：
```
DBCC SQLPERF(LOGSPACE);
```

优化方案

优化 IO 负载高的查询（减少 ORDER BY、JOIN、GROUP BY）。

调整 TempDB 文件大小：

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 2GB);

SQL Server Pefermance

2025/03/01 ・ SQL Server

SQL Server 性能问题排查指南

1. 死锁（Deadlock）排查

排查思路

优化方案

2. 慢查询（Slow Query）排查

排查思路

优化方案

3. CPU / 内存瓶颈排查

排查思路

优化方案

4. 索引优化

排查思路

优化方案

5. IO / 磁盘性能瓶颈

排查思路

优化方案