55 lines
1.1 KiB
Markdown
Executable File
55 lines
1.1 KiB
Markdown
Executable File
# CPU 占用过高排查
|
|
|
|
> SQL Server 查询最耗 CPU 的 SQL 语句
|
|
|
|
## 查询最耗 CPU 的语句
|
|
|
|
```sql
|
|
SELECT TOP 10
|
|
total_worker_time / execution_count AS avg_cpu_cost,
|
|
plan_handle,
|
|
execution_count,
|
|
(SELECT SUBSTRING(
|
|
text,
|
|
statement_start_offset / 2 + 1,
|
|
(CASE
|
|
WHEN statement_end_offset = -1
|
|
THEN LEN(CONVERT(nvarchar(max), text)) * 2
|
|
ELSE statement_end_offset
|
|
END - statement_start_offset) / 2
|
|
) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
|
|
FROM sys.dm_exec_query_stats
|
|
ORDER BY [avg_cpu_cost] DESC
|
|
```
|
|
|
|
## 常用排查命令
|
|
|
|
```sql
|
|
-- 查看当前会话
|
|
EXEC sp_who2
|
|
|
|
-- 查看锁
|
|
EXEC sp_lock
|
|
|
|
-- 查看 CPU 使用
|
|
SELECT
|
|
session_id,
|
|
status,
|
|
cpu_time
|
|
FROM sys.dm_exec_requests
|
|
ORDER BY cpu_time DESC
|
|
```
|
|
|
|
## 常见原因
|
|
|
|
| 原因 | 解决方法 |
|
|
|------|----------|
|
|
| 缺少索引 | 添加适当索引 |
|
|
| 统计信息过时 | UPDATE STATISTICS |
|
|
| 低效查询 | 优化 SQL |
|
|
| 过多编译 | 使用参数化查询 |
|
|
|
|
---
|
|
|
|
> 💡 定期检查慢查询，持续优化。
|