SQL Server在CentOS上的性能调优技巧

SQL Server在CentOS上的性能调优技巧

一环境与实例配置

选择与版本匹配：SQL Server自2017起支持 Linux，生产上建议优先使用CentOS 7/8搭配SQL Server 2019/2022；在CentOS 7上强行运行SQL Server 2022可能因内核/依赖导致功能异常或性能受限，必要时考虑升级至**RHEL 8/CentOS Stream 8+**或迁移至更高版本系统。
内存与存储规划：为缓冲池预留充足内存，避免与系统和其他服务争用；将数据文件与日志文件分别放置在不同物理磁盘或不同 RAID 层级，降低 I/O 争用；优先使用SSD/NVMe。
基础参数与目录：通过 sqlcmd 设置关键参数（如最大内存、并行度、端口），并合理调整默认数据/日志目录到高性能磁盘；必要时启用TLS并修改默认端口，减少暴力扫描风险。
示例（设置最大内存为 16GB、修改端口为 14330）：
EXEC sp_configure ‘show advanced options’, 1; RECONFIGURE;
EXEC sp_configure ‘max server memory (MB)’, 16384; RECONFIGURE;
EXEC sp_configure ‘show advanced options’, 0; RECONFIGURE;
– 修改端口（需重启）
sudo /opt/mssql/bin/mssql-conf set network.tcpport 14330
sudo systemctl restart mssql-server
上述要点涵盖版本匹配、内存/存储规划、参数与目录配置等最佳实践。

二内存与CPU调优

内存管理：设置合理的max server memory (MB)，避免把系统和其他服务“挤”出内存；Linux 层面用free -h、top、/proc/meminfo观察可用内存与压力；不建议在生产环境随意清理 PageCache（如写入 1/2/3 到 /proc/sys/vm/drop_caches），仅在明确测试需要时临时使用。
CPU 并行与调度：并行相关等待常见为CXPACKET与SOS_SCHEDULER_YIELD。通过 DMV 观察是否存在 CPU 压力与调度瓶颈：
- 查看调度器是否拥堵：
  SELECT scheduler_id, runnable_task_count, pending_disk_io_count
  FROM sys.dm_os_schedulers WHERE scheduler_id < 255;
  若runnable_task_count长期大于0且偏高，说明 CPU 饱和。
- 调整并行阈值：结合负载将**Cost Threshold for Parallelism（CTP）设为业务查询的典型“昂贵”阈值；将Max Degree of Parallelism（MDP）**设为“每 NUMA 节点核心数”或略低，避免单个查询过度并行。
- 常见高 CPU 根因：缺失索引、统计信息过时、非 SARG 表达式、隐式转换、参数嗅探等，需配合索引/统计信息/SQL 重写综合治理。
监控要点：结合 SQL Server 计数器（如Batch Requests/sec、SQL Compilations/sec、Plan Cache Hit Ratio）与 DMV（如sys.dm_os_wait_stats、sys.dm_exec_query_stats）定位编译风暴与计划缓存命中率下降问题。

三存储与I O优化

文件布局：将数据文件、日志文件、TempDB分别放在不同磁盘或不同 RAID 层级，避免写放大与日志/数据相互争用；日志优先放在顺序写性能更好的设备上。
临时数据库：为TempDB配置多个数据文件（通常按“每 4–8 个 CPU 核心 1 个文件，最多 8 个”起步），统一文件大小与增长步长，减少 PFS/GAM/SGAM 争用与自动增长抖动。
维护策略：对大表/高频更新表实施定期重建/重组索引与统计信息更新；结合查询工作负载评估表分区以优化扫描与维护窗口。
硬件选择：优先SSD/NVMe与充足 IOPS 的存储后端，必要时使用RAID 10提升吞吐与可靠性。

四查询与索引优化

执行计划稳定性：关注参数嗅探导致的次优计划，必要时使用计划指南、OPTIMIZE FOR提示或重编译策略；对高变异参数分布采用本地变量或拆分存储过程。
索引与统计：为高频WHERE/JOIN/ORDER BY/GROUP BY列建立合适索引；避免*SELECT ，减少不必要列与扫描；定期更新统计信息，避免优化器估算偏差引发的大表扫描与编译风暴。
写法优化：避免游标与逐行处理，优先集合操作；减少隐式转换（统一字段与参数类型）；避免在索引列上使用函数或前导通配（如LIKE ‘%abc’）导致索引失效。
快速定位 Top SQL：
- 按平均 CPU 消耗：
  SELECT TOP 10
  total_worker_time/execution_count AS avg_cpu_ms,
  execution_count,
  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 stmt
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
  ORDER BY avg_cpu_ms DESC;
- 结合等待与编译指标，优先处理“高 CPU + 高编译/高重编译”的语句。

五监控与维护

持续监控：在 Linux 侧用top、vmstat、iostat观察 CPU、内存、I/O；在 SQL Server 侧用 DMV（如sys.dm_os_sys_memory、sys.dm_os_wait_stats、sys.dm_os_schedulers）与性能计数器持续巡检。
容量与配置复核：定期复核最大内存、并行度、TempDB文件数与大小、备份策略与保留周期，确保与业务增长和 SLA 一致。
变更与验证：任何参数/索引/统计信息/分区调整，务必在测试环境验证并通过基准回归确认收益，再灰度上线。

最新问答