SQL优化 之 服务器配置优化

SQL Server 的性能很大程度上取决于其配置设置。以下是一些关键的服务器配置优化建议：

内存配置

1. 最大服务器内存(Max Server Memory)

   • 设置为物理内存的70-80%（专用于SQL Server时）

   • 保留足够内存给操作系统和其他应用

   • 配置示例：sp_configure 'show advanced options', 1; RECONFIGURE; sp_configure 'max server memory', 8192; RECONFIGURE;

2. 最小服务器内存(Min Server Memory)

   • 为SQL Server保留最小内存量

   • 适用于多实例环境或与其他内存密集型应用共享服务器

处理器配置

1. 最大并行度(MAXDOP)

   • 通常设置为NUMA节点中逻辑处理器数量的一半

   • 对于OLTP系统通常设为4-8

   • 配置示例：sp_configure 'show advanced options', 1; RECONFIGURE; sp_configure 'max degree of parallelism', 4; RECONFIGURE;

2. 成本阈值并行度(Cost Threshold for Parallelism)

   • 默认值5通常太低，建议设置为25-50

   • 防止小查询使用并行计划

I/O 配置

1. 恢复间隔(Recovery Interval)

   • 控制检查点频率

   • 默认60秒，可调整为基于I/O子系统性能

   • sp_configure 'recovery interval', 10; RECONFIGURE;

2. 备份压缩

   • 启用备份压缩减少I/O和存储需求

   • sp_configure 'backup compression default', 1; RECONFIGURE;

数据库引擎配置

1. 填充因子(Fill Factor)

   • 控制索引页的填充程度

   • OLTP系统通常设为80-90

   • ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR = 90);

2. 自动统计更新

   • 确保自动创建和更新统计信息

   • ALTER DATABASE YourDB SET AUTO_CREATE_STATISTICS ON;

   • ALTER DATABASE YourDB SET AUTO_UPDATE_STATISTICS ON;

TempDB 优化

1. 文件数量

   • 每个CPU核心1个文件，最多8个文件

   • 文件大小应相同

2. 位置

   • 将TempDB放在高性能存储上

   • 与其他数据库文件分离

监控和维护

1. 定期维护

   • 索引重建/重组

   • 统计信息更新

   • 完整性检查

2. 性能监控

   • 使用DMVs (Dynamic Management Views)

   • 设置性能基线

   • 监控等待统计信息

其他重要设置

1. 优化临时工作负载

   • ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

2. 即时文件初始化

   • 启用Windows权限以加速数据文件增长

记住，优化设置应根据您的特定工作负载、硬件配置和性能要求进行调整。在更改生产环境前，始终在测试环境中验证配置更改。