Ubuntu SQL Server性能调优实战案例

Ubuntu 上 SQL Server 性能调优实战案例

一 环境与瓶颈定位

• 目标环境：Ubuntu 20.04/22.04 LTS，SQL Server 2019/2022，OLTP 为主，存储为本地 NVMe 或云盘。
• 快速定位路径：
  • 系统层：用 iostat -x 1、vmstat 1、pidstat -u -d 1 观察 IOPS、吞吐、await、svctm、CPU steal、内存压力；检查 dmesg 是否有 I/O 超时。
  • SQL 层：用 sp_BlitzFirst/sp_WhoIsActive 定位高 CPU/逻辑读/持续时间 的查询；检查 PAGEIOLATCH_/WRITELOG 等待；确认 tempdb 争用与版本存储热点。
  • 存储层：确认数据/日志/tempdb 是否分离、条带与对齐是否合理、文件系统与对齐是否匹配 RAID 几何。

二 存储与文件系统配置

• 推荐配置要点（适用于 Ubuntu 18.04+ 与 SQL Server 2017 CU6+）：
  • 文件系统：优先使用 XFS 托管数据/日志；ext4 仅在 Linux 内核 ≥ 5.6 时考虑。
  • 条带与对齐：RAID 条带单元（stripe unit）与 XFS 的 sunit/swidth 对齐；例如 64 KB 条带应对应 sunit=16 blks（16×4096=64 KB），swidth 等于“数据盘数量”（不含校验盘）。
  • FUA（强制单位访问）：满足内核与存储支持时，启用 TF 3979 并设置 mssql-conf control.writethrough=1、control.alternatewritethrough=0；否则使用 TF 3982 与 control.writethrough=1、control.alternatewritethrough=1。
• 示例：在 Ubuntu 22.04 上创建对齐的 XFS 日志卷（RAID-10，64 KB 条带）
  • 假设日志由 6 盘 RAID-10 组成，条带 64 KB：

    sudo mdadm --create --verbose /dev/md3 --level=raid10 --chunk=64K --raid-devices=6 \
      /dev/sda /dev/sdb /dev/sdc /dev/sdd /dev/sde /dev/sdf
    
    sudo mkfs.xfs /dev/md3 -f -L log
    

  • 检查对齐（应见 sunit=16，swidth=48 对应 3 数据盘）：

    sudo xfs_info /dev/md3 | egrep 'sunit|swidth'
    

  • 挂载到数据/日志目录（示例）：

    sudo mkdir -p /var/opt/mssql/data /var/opt/mssql/log
    echo '/dev/md3 /var/opt/mssql/log xfs defaults,noatime 0 0' | sudo tee -a /etc/fstab
    sudo mount -a
    

  • 将新数据库默认目录指向高速卷（SQL Server 2019/2022 示例）：

    sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /var/opt/mssql/data
    sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir  /var/opt/mssql/log
    sudo systemctl restart mssql-server
    

  • FUA 启用（满足支持条件时）：

    # /etc/default/msodbcsql17 或 /etc/default/mssql-server 中加入
    MSSQL_STARTUP_ARGS="-T3979"
    sudo systemctl restart mssql-server
    
    sudo /opt/mssql/bin/mssql-conf set control.writethrough 1
    sudo /opt/mssql/bin/mssql-conf set control.alternatewritethrough 0
    

  以上要点与示例基于 Linux 上 SQL Server 的性能与 FUA 配置指南，包含 XFS/ext4 选择、sunit/swidth 对齐、以及 TF 3979/3982 与 mssql-conf 的推荐组合。

三 内存、调度与资源限制

• 内存：SQL Server on Linux 默认会占用“几乎所有可用内存”用于缓冲池。为系统保留 至少 4–8 GB（视监控/备份代理而定），避免 OOM 与系统抖动。
• 资源限制：提升 文件描述符与进程数，编辑 /etc/security/limits.d/mssql-server.conf（示例）：

  mssql soft nofile 65536
  mssql hard nofile 65536
  mssql soft nproc  65536
  mssql hard nproc  65536
  

  重启后生效（systemctl restart mssql-server）。
• I/O 调度与调度器：对 NVMe 优先使用 none/mq-deadline；对 SSD/SAS 可用 mq-deadline 或 cfq（视内核版本与负载而定）。示例（在 /etc/default/grub 的 GRUB_CMDLINE_LINUX 增加）：

  elevator=none
  

  更新 grub 并重启：sudo update-grub && sudo reboot。
• 透明大页（THP）：数据库负载建议禁用或设置为 madvise：

  echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
  echo madvise | sudo tee /sys/kernel/mm/transparent_hugepage/defrag
  

  持久化可写入 /etc/rc.local 或 systemd 服务。
• 电源与 NUMA：BIOS 开启 Performance 电源策略；NUMA 绑定与中断亲和可按需细化（生产变更需评估与灰度）。

四 查询与索引优化清单

• 执行计划稳定性：
  • 使用 参数嗅探友好 的写法，必要时使用 OPTIMIZE FOR 或 RECOMPILE。
  • 避免 **SELECT ***，只取必要列；减少 TOP N + OFFSET/FETCH 的无谓排序。
• 连接与子查询：
  • 优先 JOIN 替代相关子查询；减少不必要的 JOIN 数量。
  • 小结果集驱动大表；分页查询合理使用 OFFSET/FETCH。
• 统计信息与索引：
  • 保持 统计信息自动更新 开启；对大表/高变更表定期重组或重建索引。
  • 为高频 WHERE/JOIN/ORDER BY/GROUP BY 列建立合适索引；结合 覆盖索引 减少键查找。
• 临时表与版本存储：
  • 避免频繁创建/销毁大对象；为 tempdb 预分配足够文件数（按 CPU 核数，通常 8/16 起），统一大小，避免自动增长抖动。
• T‑SQL 习惯：
  • 在存储过程与批处理中减少往返与消息开销（如减少不必要的信息返回），优先集合操作替代逐行处理。 以上为在 Ubuntu 上提升 SQL 效率与存储/执行优化的通用实践要点，可作为现场优化的检查清单。

五 变更验证与回退

• 基准与压测：在变更前后使用相同工作负载进行压测，记录 P95/P99 延迟、QPS、平均逻辑读、PAGEIOLATCH/WRITELOG、CPU/IO 利用率。
• 监控告警：对 WRITELOG、PAGEIOLATCH_SH、LCK_M_* 等设置阈值告警；对 tempdb 空间与版本存储增长设置监控。
• 回退预案：对 FUA、调度器、THP、文件描述符 等变更，保留当前配置与命令，出现稳定性或性能退化时按相反顺序回滚；变更窗口内保持 回滚时间 < 业务容忍阈值。