SQL Server在Ubuntu上的性能调优指南

一、硬件基础优化

内存配置：SQL Server对内存需求较高，建议至少分配3.25GB内存（生产环境推荐8GB及以上），并通过mssql-conf工具设置memory_target参数（如sudo /opt/mssql/bin/mssql-conf setup memory_target=4G），避免默认动态分配导致的资源浪费。
存储选择：优先使用XFS或EXT4文件系统（BTRFS不推荐），两者对数据库的随机读写性能支持更好；必须使用SSD（而非HDD），以显著提升I/O吞吐量和降低延迟。
CPU配置：选择x64架构的多核心处理器（推荐2核及以上，主频≥2GHz），OLTP系统建议配置较低并行度（如maxdop=2），OLAP系统可适当提高并行度以提升批量处理性能。

二、系统配置优化

内核参数调优：通过sysctl命令调整关键内核参数，减少磁盘I/O争用和内存交换：

• vm.swappiness=10（降低页面交换概率，保留更多内存给SQL Server）；
• vm.dirty_background_ratio=10（控制脏页写入后台的阈值，避免频繁刷盘）；
• vm.dirty_ratio=20（限制脏页占用的最大内存比例）。修改后执行sudo sysctl -p使配置生效。
  文件描述符限制：编辑/etc/security/limits.d/mssql-server.conf文件，增加SQL Server进程的文件描述符和进程数限制（如mssql soft nofile 1048576、mssql hard nofile 1048576、mssql soft nproc 131072、mssql hard nproc 131072），避免高并发下出现“Too many open files”错误。

三、SQL Server自身配置优化

内存分配：通过mssql-conf设置memory_limit（SQL Server可使用的内存上限，如sudo /opt/mssql/bin/mssql-conf setup memory_limit=6G），避免占用全部系统内存导致其他服务异常；启用“锁定内存页”（sudo setcap cap_sys_nice+ep /opt/mssql/bin/sqlservr），防止内存被交换到磁盘。
并行度调整：根据工作负载类型设置max degree of parallelism (maxdop)：OLTP系统建议设为2-4（减少并行查询对CPU的争抢），OLAP系统可设为8-16（提升批量查询效率）。通过sp_configure命令修改：EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE;。
TempDB优化：TempDB是临时表和排序操作的共享资源，需放置在专用卷上（与数据、日志文件分离）；创建与CPU核心数相等的数据文件（如4核则创建4个TempDB数据文件），避免多线程竞争导致的性能瓶颈。

四、索引与查询优化

索引策略：

• 创建合适索引：为WHERE、JOIN、ORDER BY子句中频繁使用的列创建索引（如聚集索引用于主键，非聚集索引用于查询字段）；避免过度索引（过多索引会增加插入、更新、删除操作的开销）；
• 维护索引：定期执行UPDATE STATISTICS（如UPDATE STATISTICS table_name;）更新统计信息，确保查询优化器生成高效执行计划；通过sys.dm_db_index_usage_stats视图监控索引使用情况，删除未使用或冗余索引。

查询优化：

• 避免全表扫描：在查询条件列上创建索引，使用EXPLAIN或SET SHOWPLAN_ALL ON;分析执行计划，找出“Table Scan”等低效操作；
• 优化SQL语句：避免使用SELECT *（仅查询所需字段），减少数据传输量；用UNION ALL替代OR条件（OR可能导致索引失效），用EXISTS替代IN子查询（减少嵌套查询的开销）；模糊匹配时优先使用前缀匹配（如LIKE 'abc%'，而非LIKE '%abc'）。

五、日常维护与监控

定期维护任务：

• 更新统计信息：每周执行一次UPDATE STATISTICS，确保查询优化器基于最新数据分布生成执行计划；
• 重建/重组索引：每月对碎片率超过30%的索引执行ALTER INDEX ... REBUILD（碎片率高时），或ALTER INDEX ... REORGANIZE（碎片率中等时）；
• 收缩文件：仅在日志文件过大（如超过10GB）时执行DBCC SHRINKFILE（如DBCC SHRINKFILE (database_name_log, 1)），避免频繁收缩导致性能下降。

性能监控工具：

• 系统层面：使用top/htop（实时查看CPU、内存占用）、vmstat 1（监控系统整体性能）、iostat -x 1（查看磁盘I/O负载）、netstat -an | grep mssql（监控网络连接）；
• SQL Server层面：使用SQL Server Profiler捕获慢查询，通过sys.dm_exec_query_stats视图分析查询性能（如执行时间、逻辑读、物理读），使用sys.dm_os_wait_stats查看等待类型（如I/O等待、锁等待）。

六、其他优化建议

日志管理：将数据文件（.mdf）和日志文件（.ldf）放置在不同的物理磁盘上（如/data目录放数据文件，/logs目录放日志文件），减少I/O争用；启用SQL Server代理，定期执行备份任务（如每日全量备份+每小时增量备份），避免日志文件无限增长。
网络优化：调整防火墙规则，开放SQL Server默认端口1433（sudo ufw allow 1433）；使用iperf工具测试网络带宽，确保客户端与服务器之间的网络延迟低于50ms；启用SSL加密（mssql-conf setup时选择“加密连接”），保障数据传输安全。