CentOS 上 SQL Server 性能优化方法
一 系统层优化
- 存储与文件系统:优先使用 SSD/NVMe,为数据/日志分别放置在不同物理盘或 LVM 卷,避免 I/O 争用;使用 XFS/ext4 并合理设置挂载选项(如 noatime、barrier 按场景权衡),确保充足的 IOPS 与吞吐。
- 内存与调度:为 SQL Server 预留足够内存,避免与同机其他大内存服务争抢;保持 透明大页(THP)关闭 或设置为 madvise,减少内存管理抖动;使用 noop/deadline I/O 调度器(SSD 推荐 deadline)。
- 网络:优先 10GbE/25GbE 或更高带宽;启用 Jumbo Frame(MTU 9000);优化 RFS/RPS 与中断绑核,降低软中断热点;减少 TCP 延迟与重传。
- 资源隔离:通过 cgroups/cpu,cpuacct,blkio 限制与隔离实例资源,避免互相影响;关键实例可绑定 NUMA 节点 与 CPU 亲和性。
- 内核与安全:合理设置 ulimit -n(打开文件数)、vm.swappiness、dirty_ratio/background_ratio;关闭不必要的服务与端口,减少上下文切换与攻击面。
二 SQL Server 实例层配置
- 内存上限:限制 SQL Server 最大服务器内存,保留 2–4 GB 给操作系统与其他进程;示例(按实际内存调整):
EXEC sp_configure ‘show advanced options’, 1; RECONFIGURE;
EXEC sp_configure ‘max server memory (MB)’, 16384; RECONFIGURE;
- 并行度控制:结合 CPU 核数与负载设置 Max Degree of Parallelism(MAXDOP) 与 Cost Threshold for Parallelism(CTP),避免小查询过早并行与过度并行。
- TempDB 优化:将 tempdb 数据文件数量设置为 CPU 核数(或核数-1,最多 8 个),所有文件 等大小 并放在 高速 SSD;按需设置合理的 初始大小与自动增长,避免频繁自动增长。
- 跟踪标志与高级项:在 /var/opt/mssql/mssql.conf 中通过 traceflags 启用必要的跟踪标志(需重启);谨慎启用,变更前评估影响。
- 端口与加密:如需变更监听端口,使用 mssql-conf set network.tcpport 并重启;启用 TLS/SSL 保护传输安全。
- 代理与维护:启用 SQL Server Agent 执行索引/统计信息维护与备份任务,减少人工窗口与峰值抖动。
三 索引与查询优化
- 索引策略:为高频 WHERE/JOIN/ORDER BY/GROUP BY 列建立合适索引;避免 低基数列 与 过度索引(增维护成本、降写入吞吐);定期清理 未使用/重复 索引。
- 统计信息:保持 统计信息自动更新 开启;对大表/数据倾斜场景定期 手动更新统计信息,减少错误计划。
- 执行计划质量:避免 **SELECT ***、减少 非 SARG 表达式与 隐式转换(如 WHERE 中对列做函数运算);关注 参数嗅探 导致的计划退化,必要时使用 OPTION(RECOMPILE)、OPTIMIZE FOR 或计划指南。
- 语句与事务:优先 集合操作 替代 游标;合理拆分 长事务、缩短事务持有时间;减少 锁升级 与 阻塞(合理索引、行版本控制等)。
四 监控与瓶颈定位
- 内存压力:
SELECT total_physical_memory_kb/1024 AS Total_MB, available_physical_memory_kb/1024 AS Available_MB
FROM sys.dm_os_sys_memory;
- CPU 压力:
- 观察 sys.dm_os_schedulers 的 runnable_task_count,若长期大于 0 表示存在可运行任务排队,CPU 可能成为瓶颈。
- 结合 sys.dm_os_wait_stats 与 sys.dm_exec_query_stats 定位高 CPU 查询与等待类型(如 CXPACKET、SOS_SCHEDULER_YIELD)。
- 查询与计划:利用 动态管理视图(DMV) 与执行计划分析,识别 编译/重编译频繁、预估行数偏差大、缺失索引 等问题;必要时使用 Profiler/Extended Events 做细粒度跟踪。
五 维护与高可用
- 例行维护:定期 重建/重组索引、更新统计信息、收缩日志(避免频繁与激进收缩);监控 错误日志/阻塞/死锁,设置 告警。
- 备份恢复:制定 全量+差异+日志 的备份策略,定期 恢复演练 验证可用性与恢复时间目标(RTO/RPO);对关键库启用 TDE 加密静态数据。
- 高可用与更新:根据业务选择 可用性组/日志传送/镜像 等方案;保持 SQL Server 与 CentOS 的安全补丁与驱动更新,减少已知问题带来的性能与稳定性风险。