Ubuntu 上 SQL Server 性能调优实战案例
一 环境与瓶颈定位
iostat -x 1、vmstat 1、pidstat -u -d 1 观察 IOPS、吞吐、await、svctm、CPU steal、内存压力;检查 dmesg 是否有 I/O 超时。sp_BlitzFirst/sp_WhoIsActive 定位高 CPU/逻辑读/持续时间 的查询;检查 PAGEIOLATCH_/WRITELOG 等待;确认 tempdb 争用与版本存储热点。二 存储与文件系统配置
mssql-conf control.writethrough=1、control.alternatewritethrough=0;否则使用 TF 3982 与 control.writethrough=1、control.alternatewritethrough=1。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
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
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
# /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
mssql-conf 的推荐组合。三 内存、调度与资源限制
/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)。/etc/default/grub 的 GRUB_CMDLINE_LINUX 增加):elevator=none
更新 grub 并重启:sudo update-grub && sudo reboot。echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
echo madvise | sudo tee /sys/kernel/mm/transparent_hugepage/defrag
持久化可写入 /etc/rc.local 或 systemd 服务。四 查询与索引优化清单
五 变更验证与回退