印刻数据库

外观

SQLServer 操作系统优化 ​

操作系统优化概述 ​

SQLServer的性能不仅取决于自身的配置，还受到底层操作系统的影响。通过优化操作系统设置，可以为SQLServer提供更高效、更稳定的运行环境。操作系统优化涉及内存管理、CPU调度、磁盘I/O、网络配置等多个方面。

Windows 系统优化 ​

1. 系统基本配置 ​

电源计划 ​

建议：设置为"高性能"电源计划，确保CPU始终以最高性能运行。

操作步骤：

  1. 打开控制面板 > 硬件和声音 > 电源选项
  2. 选择"高性能"计划
  3. 或使用PowerShell命令：
    powershell
    powercfg -setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c

虚拟内存 ​

建议：

  • 禁用自动管理分页文件大小
  • 设置固定大小的分页文件，通常为物理内存的1.5倍
  • 避免将分页文件放在系统分区或SQLServer数据/日志分区

操作步骤：

  1. 右键点击"此电脑" > 属性 > 高级系统设置 > 性能 > 设置 > 高级 > 虚拟内存 > 更改
  2. 取消勾选"自动管理所有驱动器的分页文件大小"
  3. 选择合适的驱动器，设置"自定义大小"
  4. 初始大小和最大值设置为相同值

系统更新 ​

建议：

  • 启用自动更新，保持系统补丁最新
  • 合理安排更新时间，避免影响业务高峰期
  • 测试环境先验证更新，再应用到生产环境

2. 内存管理 ​

禁用内存压缩 ​

建议：对于专用SQLServer服务器，禁用内存压缩功能，避免额外CPU开销。

操作步骤：

powershell
Disable-MMAgent -mc
Restart-Computer

调整工作集大小 ​

建议：设置SQLServer服务的工作集大小，确保SQLServer能够稳定使用分配的内存。

操作步骤：

  1. 打开注册表编辑器，导航到：HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER
  2. 添加或修改以下键值：
    • ObjectName：确保设置为正确的服务账户
    • Type：确保值为16（SERVICE_WIN32_OWN_PROCESS）

3. CPU 优化 ​

处理器调度 ​

建议：将处理器调度优先级设置为"后台服务"，优化服务器应用性能。

操作步骤：

  1. 右键点击"此电脑" > 属性 > 高级系统设置 > 性能 > 设置 > 高级 > 处理器调度
  2. 选择"后台服务"

禁用超线程（根据情况） ​

建议：

  • 对于OLTP工作负载，可能需要禁用超线程以提高性能
  • 对于分析型工作负载，启用超线程可能更有利
  • 建议通过测试确定最佳配置

操作步骤：

  1. 重启服务器，进入BIOS设置
  2. 找到"Hyper-Threading"或"HT Technology"选项
  3. 根据测试结果启用或禁用

4. 磁盘 I/O 优化 ​

文件系统 ​

建议：

  • 使用NTFS或ReFS文件系统
  • 格式化时选择64KB分配单元大小（尤其是对于数据和日志文件）

操作步骤：

powershell
Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel "SQLData"

磁盘碎片整理 ​

建议：

  • 定期执行磁盘碎片整理，尤其是对于机械硬盘
  • 对于SSD，禁用自动磁盘碎片整理
  • 避免在业务高峰期执行碎片整理

操作步骤：

  1. 打开"优化驱动器"工具
  2. 选择驱动器，点击"优化"
  3. 或使用PowerShell命令：
    powershell
    Optimize-Volume -DriveLetter D -Defrag -Verbose

启用 Instant File Initialization ​

建议：为SQLServer服务账户添加"执行卷维护任务"权限，启用Instant File Initialization，加速数据文件增长。

操作步骤：

  1. 打开本地安全策略 > 本地策略 > 用户权限分配
  2. 双击"执行卷维护任务"，添加SQLServer服务账户
  3. 重启SQLServer服务

5. 网络优化 ​

网络适配器设置 ​

建议：

  • 启用"接收端缩放"（RSS）
  • 启用"TCP Chimney Offload"
  • 启用"大接收卸载"（LRO）
  • 禁用"IPv6"（如果不需要）

操作步骤：

  1. 打开设备管理器 > 网络适配器 > 属性 > 高级
  2. 根据上述建议调整设置

TCP/IP 设置 ​

建议：

  • 调整TCP窗口大小
  • 启用TCP SYN cookies
  • 调整TIME_WAIT延迟

操作步骤：

  1. 打开注册表编辑器，导航到：HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
  2. 添加或修改以下键值：
    • TcpWindowSize：设置为合适的值，如65536
    • SynAttackProtect：设置为2，启用SYN cookies
    • TcpTimedWaitDelay：设置为30，减少TIME_WAIT连接的等待时间

Linux 系统优化 ​

1. 系统基本配置 ​

禁用不必要的服务 ​

建议：禁用不需要的系统服务，减少资源消耗。

操作步骤：

bash
# 查看正在运行的服务
systemctl list-units --type=service --state=running

# 禁用服务
systemctl disable --now [service-name]

时区和时间同步 ​

建议：

  • 配置正确的时区
  • 启用NTP时间同步，确保所有节点时间一致

操作步骤：

bash
# 设置时区
timedatectl set-timezone Asia/Shanghai

# 启用NTP
timedatectl set-ntp on

2. 内存管理 ​

调整透明大页（THP） ​

建议：禁用透明大页，避免性能波动。

操作步骤：

bash
# 临时禁用
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

# 永久禁用，添加到/etc/rc.local或systemd服务
cat << EOF > /etc/systemd/system/disable-thp.service
[Unit]
Description=Disable Transparent Huge Pages

[Service]
Type=oneshot
ExecStart=/bin/sh -c "echo never > /sys/kernel/mm/transparent_hugepage/enabled && echo never > /sys/kernel/mm/transparent_hugepage/defrag"

[Install]
WantedBy=multi-user.target
EOF

systemctl enable --now disable-thp.service

调整交换空间 ​

建议：

  • 对于专用SQLServer服务器，设置较小的交换空间（如2-4GB）
  • 调整swappiness参数，减少交换频率

操作步骤：

bash
# 调整swappiness为10
echo 10 > /proc/sys/vm/swappiness

# 永久设置，添加到/etc/sysctl.conf
cat << EOF >> /etc/sysctl.conf
vm.swappiness=10
EOF

sysctl -p

3. CPU 优化 ​

CPU 亲和性 ​

建议：根据SQLServer实例数量和CPU核心数，配置CPU亲和性，提高资源利用率。

操作步骤：

bash
# 使用taskset命令设置进程亲和性
taskset -cp 0-3 [pid]

# 或在SQLServer配置中设置
/opt/mssql/bin/mssql-conf set cpuaffinity "0-3"

调整进程调度策略 ​

建议：将SQLServer进程的调度策略设置为实时或批处理，优化CPU调度。

操作步骤：

bash
# 使用chrt命令设置实时优先级
chrt -f -p 99 [pid]

4. 磁盘 I/O 优化 ​

文件系统 ​

建议：

  • 使用XFS文件系统（推荐）或EXT4
  • 格式化时选择合适的块大小，如64KB

操作步骤：

bash
# 格式化分区为XFS
mkfs.xfs -f -s size=4096 /dev/sdb1

# 挂载时添加优化选项
cat << EOF >> /etc/fstab
/dev/sdb1 /sql/data xfs defaults,noatime,nodiratime,nobarrier 0 2
EOF

I/O 调度器 ​

建议：

  • 对于SSD，使用none或mq-deadline调度器
  • 对于机械硬盘，使用cfq或mq-deadline调度器

操作步骤：

bash
# 临时设置
echo mq-deadline > /sys/block/sdb/queue/scheduler

# 永久设置，添加到/etc/udev/rules.d/60-scheduler.rules
cat << EOF > /etc/udev/rules.d/60-scheduler.rules
ACTION=="add|change", KERNEL=="sd*", ATTR{queue/rotational}=="0", ATTR{queue/scheduler}="none"
ACTION=="add|change", KERNEL=="sd*", ATTR{queue/rotational}=="1", ATTR{queue/scheduler}="mq-deadline"
EOF

5. 网络优化 ​

网络适配器设置 ​

建议：

  • 启用RSS、VLAN卸载等高级功能
  • 调整网络缓冲区大小

操作步骤：

bash
# 查看网络适配器高级设置
ethtool -k eth0

# 启用RSS
ethtool -K eth0 rx-vlan-offload on tx-vlan-offload on

TCP/IP 设置 ​

建议：

  • 调整TCP缓冲区大小
  • 启用TCP快速打开
  • 调整TIME_WAIT延迟

操作步骤：

bash
# 添加到/etc/sysctl.conf
cat << EOF >> /etc/sysctl.conf
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.ipv4.tcp_fastopen = 3
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 30
EOF

sysctl -p

操作系统优化最佳实践 ​

1. 基于工作负载优化 ​

  • OLTP系统：

    • 优先优化磁盘I/O和内存管理
    • 调整CPU调度以降低延迟
    • 优化网络配置以提高并发连接数

  • 数据仓库系统：

    • 优先优化CPU和内存配置
    • 调整磁盘I/O以提高吞吐量
    • 优化并行度设置

2. 监控和调整 ​

  • 定期监控操作系统性能指标：

    • CPU使用率
    • 内存使用率
    • 磁盘I/O延迟和吞吐量
    • 网络吞吐量和延迟

  • 使用性能监控工具：

    • Windows：Performance Monitor, Resource Monitor
    • Linux：top, vmstat, iostat, sar, netstat
    • 第三方工具：SolarWinds, Datadog, New Relic

3. 版本特定优化 ​

  • 关注不同Windows Server和Linux发行版的特定优化建议
  • 定期更新操作系统，获取最新性能改进
  • 参考微软官方文档中的操作系统优化指南

4. 测试和验证 ​

  • 在测试环境中验证所有优化设置
  • 比较优化前后的性能指标
  • 避免在生产环境中进行未经测试的优化

常见问题与解决方案 ​

1. 如何确定最佳的分页文件大小？ ​

解决方案：

  • 对于专用SQLServer服务器，设置固定大小的分页文件，通常为物理内存的1.5倍
  • 监控分页文件使用率，根据实际情况调整
  • 避免将分页文件放在系统分区或SQLServer数据/日志分区

2. 如何验证Instant File Initialization是否启用？ ​

解决方案：

  • 查看SQLServer错误日志，寻找"Database Instant File Initialization: enabled"消息
  • 或使用以下PowerShell脚本：
    powershell
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server "localhost"
$server.Information.IsInstantFileInitializationEnabled

3. 如何优化SQLServer在虚拟化环境中的性能？ ​

解决方案：

  • 确保虚拟机分配了足够的CPU和内存资源
  • 启用CPU和内存预留
  • 使用固定内存分配，避免动态内存
  • 配置磁盘I/O优化，如启用VMware Paravirtual SCSI控制器
  • 优化网络配置，如使用VMXNET3网络适配器

4. 如何监控操作系统性能瓶颈？ ​

解决方案：

  • 使用Windows Performance Monitor或Linux sar工具收集性能数据
  • 关注关键性能指标：
    • CPU：% Processor Time
    • 内存：Available MBytes, Page Faults/sec
    • 磁盘：Avg. Disk sec/Read, Avg. Disk sec/Write
    • 网络：Bytes Total/sec, Current Bandwidth
  • 分析等待统计信息，识别主要瓶颈

总结 ​

操作系统优化是SQLServer性能调优的重要组成部分。通过合理配置操作系统的内存管理、CPU调度、磁盘I/O和网络设置，可以为SQLServer提供更高效、更稳定的运行环境。DBA需要根据SQLServer的工作负载类型、硬件配置和业务需求，制定合适的操作系统优化策略，并定期监控和调整，以确保SQLServer的最佳性能。