SQLServer教程FG029-SQLServer慢查询优化实战
目录大纲
内容简介
本文档基于SQLServer官方文档的查询优化内容，结合生产环境实际情况，详细讲解SQLServer慢查询的识别、分析、优化等内容。风哥教程参考SQLServer官方文档Query Optimization、Execution Plans等相关章节。
Part01-基础概念与理论知识
1.1 SQLServer慢查询概念
SQLServer慢查询的概念：
- 慢查询是指执行时间超过预设阈值的SQL查询
- 通常阈值设置为1秒或5秒，根据业务需求而定
- 慢查询会占用大量系统资源，影响数据库性能
- 慢查询是数据库性能问题的主要原因之一
更多视频教程www.fgedu.net.cn
1.2 SQLServer慢查询原因
SQLServer慢查询的原因：
- 索引问题：缺少索引、索引失效或索引设计不合理
- SQL语句问题：SQL语句编写不当，如全表扫描、笛卡尔积等
- 统计信息问题：统计信息过时或不准确
- 参数嗅探问题：参数值导致执行计划选择不当
- 资源问题：CPU、内存、IO等资源不足
- 锁竞争问题：并发访问导致锁等待
学习交流加群风哥微信: itpux-com
1.3 SQLServer慢查询影响
SQLServer慢查询的影响：
- 增加系统资源消耗
- 降低数据库响应速度
- 影响其他查询的执行
- 可能导致数据库宕机
- 影响业务正常运行
学习交流加群风哥QQ113257174
Part02-生产环境规划与建议
2.1 SQLServer慢查询监控规划
慢查询监控规划：
- 设置合理的慢查询阈值
- 配置慢查询日志
- 使用SQL Server Profiler或Extended Events监控
- 定期分析慢查询日志
- 建立慢查询预警机制
风哥提示：生产环境应设置合理的慢查询阈值，避免过多的日志产生
2.2 SQLServer慢查询预防措施
慢查询预防措施：
- 建立SQL语句审核机制
- 使用参数化查询
- 定期更新统计信息
- 合理设计索引
- 优化数据库结构
更多学习教程公众号风哥教程itpux_com
2.3 SQLServer慢查询优化策略
慢查询优化策略：
- 优化SQL语句
- 创建适当的索引
- 更新统计信息
- 调整查询计划
- 优化数据库配置
from SQLServer视频:www.itpux.com
Part03-生产环境项目实施方案
3.1 SQLServer慢查询识别
慢查询识别：
— 1. 打开SQL Server Profiler
— 2. 创建新的跟踪
— 3. 选择事件：RPC:Completed, SQL:BatchCompleted
— 4. 添加列筛选：Duration > 1000（1秒）
— 5. 启动跟踪
— 方法2：使用Extended Events
— 1. 创建Extended Events会话
CREATE EVENT SESSION [SlowQueries] ON SERVER
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([duration]>(1000000))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([duration]>(1000000)))
ADD TARGET package0.event_file(
SET filename=N’C:\SQLServer\XEvents\SlowQueries.xel’,max_file_size=(50),max_rollover_files=(5))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO
— 启动会话
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;
GO
— 方法3：使用动态管理视图
— 查看当前正在执行的慢查询
SELECT
session_id,
database_id,
db_name(database_id) AS database_name,
user_id,
login_name,
host_name,
program_name,
status,
command,
sql_handle,
statement_start_offset,
statement_end_offset,
cpu_time,
total_elapsed_time,
logical_reads,
physical_reads,
writes,
last_wait_type,
wait_time,
blocking_session_id
FROM sys.dm_exec_requests
WHERE status = ‘running’ AND total_elapsed_time > 1000;
GO
— 查看历史慢查询
SELECT
top 100
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
qs.total_worker_time / qs.execution_count AS avg_worker_time,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.total_physical_reads / qs.execution_count AS avg_physical_reads,
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 statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_elapsed_time DESC;
GO
执行结果：
creation_time last_execution_time execution_count avg_elapsed_time avg_worker_time avg_logical_reads avg_physical_reads statement_text
————————- ————————- ————— —————- —————- —————– —————— ————–
2025-04-08 10:00:00.000 2025-04-08 10:30:00.000 10 5000 4000 100000 5000 SELECT * FROM fgedu.large_table WHERE col1 = ‘test’
2025-04-08 09:00:00.000 2025-04-08 10:00:00.000 5 3000 2000 50000 2000 SELECT * FROM fgedu.orders WHERE customer_id = 123
(2 rows affected)
3.2 SQLServer慢查询分析
慢查询分析：
— 1. 使用SET SHOWPLAN_XML
SET SHOWPLAN_XML ON;
GO
SELECT * FROM fgedu.large_table WHERE col1 = ‘test’;
GO
SET SHOWPLAN_XML OFF;
GO
— 2. 使用SQL Server Management Studio
— 选中SQL语句，按Ctrl+M，然后执行
— 方法2：分析执行计划
— 查看执行计划中的操作符
— 常见的性能瓶颈操作符：
— – Table Scan（全表扫描）
— – Clustered Index Scan（聚集索引扫描）
— – Sort（排序）
— – Hash Match（哈希匹配）
— 方法3：使用动态管理视图分析
— 查看缓存的执行计划
SELECT
plan_handle,
query_plan,
creation_time,
last_execution_time,
execution_count,
total_elapsed_time,
total_worker_time,
total_logical_reads,
total_physical_reads,
total_writes
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
WHERE qs.sql_handle IN (
SELECT sql_handle FROM sys.dm_exec_requests WHERE status = ‘running’
);
GO
— 方法4：分析索引使用情况
— 查看未使用的索引
SELECT
OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
i.type_desc,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID() AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0
ORDER BY s.user_updates DESC;
GO
— 查看缺失的索引
SELECT
db_name(mid.database_id) AS database_name,
OBJECT_NAME(mid.object_id) AS table_name,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.avg_total_user_cost,
migs.avg_user_impact,
migs.user_seeks,
migs.user_scans
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY migs.avg_user_impact DESC;
GO
执行结果：
database_name table_name equality_columns inequality_columns included_columns avg_total_user_cost avg_user_impact user_seeks user_scans
————- ———— —————- —————— —————- —————— —————- ———– ———-
fgedudb large_table col1 NULL col2,col3 100.0 95.0 100 50
(1 row affected)
— 未使用的索引结果：
table_name index_name type_desc user_seeks user_scans user_lookups user_updates
———— —————- ————- ———- ———- ———— ————
fgedu_table IX_fgedu_table_col4 NONCLUSTERED 0 0 0 1000
(1 row affected)
3.3 SQLServer慢查询优化
慢查询优化：
— 1. 避免使用SELECT *
— 优化前
SELECT * FROM fgedu.large_table WHERE col1 = ‘test’;
— 优化后
SELECT col1, col2, col3 FROM fgedu.large_table WHERE col1 = ‘test’;
— 2. 使用索引覆盖查询
— 优化前
SELECT col1, col2, col3 FROM fgedu.large_table WHERE col1 = ‘test’;
— 优化后（创建覆盖索引）
CREATE INDEX IX_large_table_col1_col2_col3 ON fgedu.large_table(col1) INCLUDE (col2, col3);
— 3. 避免使用函数
— 优化前
SELECT * FROM fgedu.large_table WHERE YEAR(create_date) = 2025;
— 优化后
SELECT * FROM fgedu.large_table WHERE create_date >= ‘2025-01-01’ AND create_date < '2026-01-01';
-- 4. 避免使用OR
-- 优化前
SELECT * FROM fgedu.large_table WHERE col1 = 'test' OR col2 = 'test';
-- 优化后
SELECT * FROM fgedu.large_table WHERE col1 = 'test'
UNION ALL
SELECT * FROM fgedu.large_table WHERE col2 = 'test';
-- 方法2：创建适当的索引
-- 1. 创建聚集索引
CREATE CLUSTERED INDEX PK_large_table ON fgedu.large_table(id);
-- 2. 创建非聚集索引
CREATE NONCLUSTERED INDEX IX_large_table_col1 ON fgedu.large_table(col1);
-- 3. 创建包含列索引
CREATE NONCLUSTERED INDEX IX_large_table_col1_include ON fgedu.large_table(col1) INCLUDE (col2, col3);
-- 4. 创建复合索引
CREATE NONCLUSTERED INDEX IX_large_table_col1_col2 ON fgedu.large_table(col1, col2);
-- 方法3：更新统计信息
-- 更新单个表的统计信息
UPDATE STATISTICS fgedu.large_table;
-- 更新所有表的统计信息
EXEC sp_updatestats;
-- 方法4：调整查询计划
-- 1. 使用查询提示
SELECT * FROM fgedu.large_table WITH (INDEX(IX_large_table_col1)) WHERE col1 = 'test';
-- 2. 清除计划缓存
DBCC FREEPROCCACHE;
-- 3. 使用参数化查询
DECLARE @col1 VARCHAR(50) = 'test';
SELECT * FROM fgedu.large_table WHERE col1 = @col1;
-- 方法5：优化数据库配置
-- 1. 调整内存配置
EXEC sp_configure 'max server memory', 16384;
RECONFIGURE;
-- 2. 调整并行度
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;
-- 3. 调整查询超时
EXEC sp_configure 'query wait', 60000;
RECONFIGURE;
执行结果：
Commands completed successfully.
— 统计信息更新结果：
Commands completed successfully.
— 配置调整结果：
Configuration option ‘max server memory (MB)’ changed from 8192 to 16384. Run the RECONFIGURE statement to install.
Configuration option ‘max degree of parallelism’ changed from 0 to 4. Run the RECONFIGURE statement to install.
Configuration option ‘query wait (seconds)’ changed from -1 to 60. Run the RECONFIGURE statement to install.
Part04-生产案例与实战讲解
4.1 SQLServer慢查询识别案例
慢查询识别实战：
— 环境准备：
— SQLServer实例：fgedu-server
— 数据库：fgedudb
— 步骤1：配置Extended Events监控慢查询
— 创建Extended Events会话
CREATE EVENT SESSION [SlowQueries] ON SERVER
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([duration]>(1000000))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([duration]>(1000000)))
ADD TARGET package0.event_file(
SET filename=N’C:\SQLServer\XEvents\SlowQueries.xel’,max_file_size=(50),max_rollover_files=(5))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON);
GO
— 启动会话
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;
GO
— 步骤2：使用动态管理视图识别慢查询
— 查看当前正在执行的慢查询
SELECT
session_id,
database_id,
db_name(database_id) AS database_name,
user_id,
login_name,
host_name,
program_name,
status,
command,
sql_handle,
statement_start_offset,
statement_end_offset,
cpu_time,
total_elapsed_time,
logical_reads,
physical_reads,
writes,
last_wait_type,
wait_time,
blocking_session_id,
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 statement_text
FROM sys.dm_exec_requests qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE status = ‘running’ AND total_elapsed_time > 1000;
GO
— 查看历史慢查询
SELECT
top 10
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
qs.total_worker_time / qs.execution_count AS avg_worker_time,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.total_physical_reads / qs.execution_count AS avg_physical_reads,
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 statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE dbid = DB_ID(‘fgedudb’)
ORDER BY avg_elapsed_time DESC;
GO
执行结果：
creation_time last_execution_time execution_count avg_elapsed_time avg_worker_time avg_logical_reads avg_physical_reads statement_text
————————- ————————- ————— —————- —————- —————– —————— ————–
2025-04-08 10:00:00.000 2025-04-08 10:30:00.000 10 5000 4000 100000 5000 SELECT * FROM fgedu.large_table WHERE col1 = ‘test’
2025-04-08 09:00:00.000 2025-04-08 10:00:00.000 5 3000 2000 50000 2000 SELECT * FROM fgedu.orders WHERE customer_id = 123
2025-04-08 08:00:00.000 2025-04-08 09:00:00.000 3 2000 1500 30000 1000 SELECT * FROM fgedu.customers WHERE name LIKE ‘%test%’
(3 rows affected)
4.2 SQLServer慢查询分析案例
慢查询分析实战：
— 环境准备：
— SQLServer实例：fgedu-server
— 数据库：fgedudb
— 慢查询：SELECT * FROM fgedu.large_table WHERE col1 = ‘test’
— 步骤1：查看执行计划
— 使用SQL Server Management Studio
— 1. 选中SQL语句：SELECT * FROM fgedu.large_table WHERE col1 = ‘test’
— 2. 按Ctrl+M，显示实际执行计划
— 3. 执行查询
— 步骤2：分析执行计划
— 执行计划显示：
— – 表扫描（Table Scan）：扫描了100000行
— – 逻辑读取：100000次
— – 物理读取：5000次
— – 执行时间：5秒
— 步骤3：分析索引使用情况
— 查看表的索引
SELECT
name,
type_desc,
is_primary_key
FROM sys.indexes
WHERE object_id = OBJECT_ID(‘fgedu.large_table’);
GO
— 查看缺失的索引
SELECT
db_name(mid.database_id) AS database_name,
OBJECT_NAME(mid.object_id) AS table_name,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.avg_total_user_cost,
migs.avg_user_impact,
migs.user_seeks,
migs.user_scans
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID(‘fgedudb’) AND mid.object_id = OBJECT_ID(‘fgedu.large_table’);
GO
— 步骤4：分析统计信息
— 查看统计信息
DBCC SHOW_STATISTICS(‘fgedu.large_table’, PK_large_table);
GO
— 查看统计信息更新时间
SELECT
name,
stats_id,
last_updated,
rows,
rows_sampled,
steps,
unfiltered_rows
FROM sys.stats
CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id)
WHERE object_id = OBJECT_ID(‘fgedu.large_table’);
GO
执行结果：
name type_desc is_primary_key
————— ————- —————
PK_large_table CLUSTERED 1
(1 row affected)
— 缺失索引结果：
database_name table_name equality_columns inequality_columns included_columns avg_total_user_cost avg_user_impact user_seeks user_scans
————- ———— —————- —————— —————- —————— —————- ———– ———-
fgedudb large_table col1 NULL NULL 100.0 95.0 100 50
(1 row affected)
— 统计信息结果：
Name Updated Rows Rows Sampled Steps Density Average Key Length String Index Filter Expression Unfiltered Rows
————— ——————– ——— ————- —– ——– —————— ———— —————— —————
PK_large_table 2025-04-01 10:00:00 100000 100000 100 0.00001 4 NO NULL 100000
— 统计信息更新时间：
name stats_id last_updated rows rows_sampled steps unfiltered_rows
————— ——– ——————– —— ————- —– —————
PK_large_table 1 2025-04-01 10:00:00 100000 100000 100 100000
(1 row affected)
4.3 SQLServer慢查询优化案例
慢查询优化实战：
— 环境准备：
— SQLServer实例：fgedu-server
— 数据库：fgedudb
— 慢查询：SELECT * FROM fgedu.large_table WHERE col1 = ‘test’
— 步骤1：创建索引
— 创建非聚集索引
CREATE NONCLUSTERED INDEX IX_large_table_col1 ON fgedu.large_table(col1);
GO
— 步骤2：优化SQL语句
— 优化前
SELECT * FROM fgedu.large_table WHERE col1 = ‘test’;
— 优化后
SELECT col1, col2, col3 FROM fgedu.large_table WHERE col1 = ‘test’;
— 创建覆盖索引
CREATE NONCLUSTERED INDEX IX_large_table_col1_include ON fgedu.large_table(col1) INCLUDE (col2, col3);
GO
— 步骤3：更新统计信息
UPDATE STATISTICS fgedu.large_table;
GO
— 步骤4：验证优化结果
— 执行优化后的查询
SELECT col1, col2, col3 FROM fgedu.large_table WHERE col1 = ‘test’;
GO
— 查看执行计划
SET SHOWPLAN_XML ON;
GO
SELECT col1, col2, col3 FROM fgedu.large_table WHERE col1 = ‘test’;
GO
SET SHOWPLAN_XML OFF;
GO
— 查看查询性能
SELECT
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
qs.total_worker_time / qs.execution_count AS avg_worker_time,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.total_physical_reads / qs.execution_count AS avg_physical_reads,
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 statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE ‘%SELECT col1, col2, col3 FROM fgedu.large_table WHERE col1 = ”test”%’
ORDER BY qs.last_execution_time DESC;
GO
执行结果：
Commands completed successfully.
— 统计信息更新结果：
Commands completed successfully.
— 执行计划显示：
— – 索引查找（Index Seek）：扫描了100行
— – 逻辑读取：100次
— – 物理读取：0次
— – 执行时间：0.1秒
— 查询性能结果：
creation_time last_execution_time execution_count avg_elapsed_time avg_worker_time avg_logical_reads avg_physical_reads statement_text
————————- ————————- ————— —————- —————- —————– —————— ————–
2025-04-08 11:00:00.000 2025-04-08 11:00:00.000 5 100 50 100 0 SELECT col1, col2, col3 FROM fgedu.large_table WHERE col1 = ‘test’
(1 row affected)
Part05-风哥经验总结与分享
5.1 SQLServer慢查询最佳实践
- 建立慢查询监控机制，及时发现慢查询
- 使用参数化查询，避免参数嗅探问题
- 合理设计索引，避免过度索引
- 定期更新统计信息，确保查询计划准确
- 优化SQL语句，避免全表扫描和复杂操作
- 使用执行计划分析工具，识别性能瓶颈
- 建立SQL语句审核机制，预防慢查询
- 定期清理缓存，避免过时的执行计划
5.2 SQLServer慢查询常见问题
- 索引缺失：解决方案是创建适当的索引
- 索引失效：解决方案是优化SQL语句，避免使用函数
- 统计信息过时：解决方案是定期更新统计信息
- 参数嗅探：解决方案是使用参数化查询或强制参数化
- 全表扫描：解决方案是创建索引或优化SQL语句
- 锁竞争：解决方案是优化并发控制或使用快照隔离级别
5.3 SQLServer慢查询调优技巧
- 使用索引覆盖查询，减少回表操作
- 使用分页查询，减少数据传输量
- 使用临时表或表变量，存储中间结果
- 使用分区表，提高查询性能
- 使用并行查询，提高处理速度
- 优化JOIN操作，使用适当的连接类型
- 使用CTE（公共表表达式），提高查询可读性
- 使用索引提示，强制使用特定索引
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
