一、为什么我的SqlServer跑得比蜗牛还慢？
相信很多DBA和开发人员都遇到过这样的场景：明明服务器配置不错，SQL语句也写得规规矩矩，但数据库就是慢得像老牛拉破车。这时候，你可能需要检查一下SqlServer的默认配置了。
SqlServer安装完成后，微软为了确保兼容性，给出的默认配置往往非常保守。就像买新车时默认的"经济模式"，虽然省油但动力不足。比如：
- 默认的内存分配可能只给了系统总内存的一小部分
- 并行度设置可能不适合你的CPU核心数
- 统计信息更新频率可能跟不上数据变化速度
-- 技术栈：SQL Server 2019
-- 查看当前内存配置
SELECT
physical_memory_kb/1024 AS '物理内存(MB)',
committed_target_kb/1024 AS '可用内存(MB)',
committed_kb/1024 AS '已用内存(MB)'
FROM sys.dm_os_sys_memory;
-- 查看最大并行度设置
SELECT value_in_use
FROM sys.configurations
WHERE name = 'max degree of parallelism';
二、必须调整的5个关键配置参数
1. 内存配置：别让数据库饿着肚子干活
SqlServer默认只会使用系统内存的一小部分，这就像让一个壮汉只吃半碗饭干活。我们可以通过以下命令调整：
-- 设置最大服务器内存(单位MB)，建议留出2-4GB给操作系统
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 24000; -- 假设服务器有32GB内存
RECONFIGURE;
-- 设置最小服务器内存，防止内存被其他应用抢占
EXEC sp_configure 'min server memory', 16000;
RECONFIGURE;
2. 并行度设置：让多核CPU真正发挥作用
现代服务器都是多核CPU，但SqlServer默认的并行度设置可能无法充分利用：
-- 查看CPU核心数
SELECT cpu_count FROM sys.dm_os_sys_info;
-- 设置最大并行度，通常建议设置为物理核心数的1/2到3/4
EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;
-- 设置并行度阈值，超过这个成本值的查询才会使用并行
-- 默认值是5，对于OLTP系统可以提高到20-30
EXEC sp_configure 'cost threshold for parallelism', 25;
RECONFIGURE;
3. 统计信息更新：别让优化器"瞎猜"
统计信息过期会导致执行计划不准确，就像用十年前的地图导航：
-- 查看统计信息最后更新时间
SELECT
OBJECT_NAME(object_id) AS 表名,
name AS 统计信息名,
STATS_DATE(object_id, stats_id) AS 最后更新时间
FROM sys.stats
WHERE OBJECT_NAME(object_id) IN ('你的表名');
-- 手动更新统计信息
UPDATE STATISTICS 表名 WITH FULLSCAN;
-- 开启自动异步更新统计信息(对大型数据库很实用)
ALTER DATABASE 你的数据库名
SET AUTO_UPDATE_STATISTICS_ASYNC ON;
三、高级调优：索引和查询优化
1. 缺失索引建议：SqlServer其实会告诉你缺什么
SqlServer会默默记录哪些查询缺少索引：
-- 查看缺失索引建议
SELECT
mid.statement AS 表名,
mid.equality_columns AS 等值列,
mid.inequality_columns AS 不等值列,
mid.included_columns AS 包含列,
migs.user_seeks AS 用户查找次数,
migs.avg_total_user_cost AS 平均成本,
migs.avg_user_impact AS 平均影响百分比
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact DESC;
2. 查询存储：性能问题的时光机
SQL Server 2016引入的查询存储功能就像数据库的"黑匣子"：
-- 启用查询存储
ALTER DATABASE 你的数据库名
SET QUERY_STORE = ON (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900
);
-- 查看最耗资源的查询
SELECT
qt.query_sql_text AS SQL文本,
qrs.count_executions AS 执行次数,
qrs.avg_duration/1000 AS 平均耗时(ms),
qrs.avg_logical_io_reads AS 平均逻辑读,
qp.query_plan AS 执行计划
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan qp ON q.query_id = qp.query_id
JOIN sys.query_store_runtime_stats qrs ON qp.plan_id = qrs.plan_id
ORDER BY qrs.avg_duration DESC;
四、日常维护：预防胜于治疗
1. 定期索引维护
就像汽车需要定期保养，索引也需要维护：
-- 查看索引碎片情况
SELECT
OBJECT_NAME(ind.object_id) AS 表名,
ind.name AS 索引名,
ips.avg_fragmentation_in_percent AS 碎片率,
ips.page_count AS 页数
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes ind ON ips.object_id = ind.object_id AND ips.index_id = ind.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;
-- 重建碎片严重的索引
ALTER INDEX 索引名 ON 表名 REBUILD;
-- 对于大型表，可以使用在线重建(企业版功能)
ALTER INDEX 索引名 ON 表名 REBUILD WITH (ONLINE = ON);
2. 自动化的维护计划
可以创建自动化作业来处理日常维护：
-- 创建每周维护计划
USE msdb;
GO
EXEC sp_add_maintenance_plan N'每周维护计划';
GO
EXEC sp_add_maintenance_plan_job
N'每周维护计划',
N'每周索引重建',
N'每周日凌晨2点执行';
GO
EXEC sp_add_maintenance_plan_db
N'每周维护计划',
N'你的数据库名';
GO
五、应用场景与技术选型
适用场景
- OLTP系统响应缓慢
- 报表查询超时
- 批量作业执行时间过长
- 高并发下性能下降明显
技术优缺点
优点：
- 无需修改应用代码即可获得性能提升
- 配置调整立即生效
- 大部分设置可以在线修改
缺点：
- 需要重启实例的配置变更会影响可用性
- 过度调优可能导致资源争用
- 需要持续监控调整效果
注意事项
- 生产环境变更前务必在测试环境验证
- 记录变更前的配置以便回滚
- 性能调优是一个持续过程，不是一劳永逸的
- 监控系统资源使用情况，避免过度分配
六、总结
SqlServer的默认配置就像出厂设置的智能手机，虽然能用但未必适合你的需求。通过合理调整内存、并行度和统计信息等关键参数，配合定期的索引维护和查询优化，可以显著提升数据库性能。记住，调优不是一次性工作，而是需要持续监控和调整的过程。当你的数据库开始"气喘吁吁"时，不妨回头检查这些基础配置，也许问题就迎刃而解了。
评论