一、调参前的诊断：先看服务器到底「缺什么」

很多人调参前不查现状，对着网上抄一份 sp_configure 脚本就开始改——这是典型的「把生产库当试验田」。调参前必须先采集三件事：CPU 核数、内存总量、当前关键参数值。

诊断脚本：一次性看清服务器画像

-- ============================================================-- 1. 服务器硬件画像（CPU / 内存 / 引擎版本）-- ============================================================SELECTSERVERPROPERTY('MachineName')              AS [机器名],     SERVERPROPERTY('Edition')                  AS [版本],     SERVERPROPERTY('ProductVersion')            AS [SQL版本],     cpu_count                                     AS [逻辑CPU核数],     physical_memory_kb / 1024AS [物理内存MB],     CEILING((physical_memory_kb * 1.0) / (1024 * 1024))   AS [物理内存GB] FROM sys.dm_os_sys_info;  -- ============================================================-- 2. 5 个关键参数当前值（一定要记住默认值是「小机配置」）-- ============================================================SELECT     name,     value_in_use,     description FROM sys.configurations WHERE name IN (     'max server memory (MB)',     'min server memory (MB)',     'max degree of parallelism',     'cost threshold for parallelism',     'max worker threads',     'optimize for ad hoc workloads' ) ORDER BY name; 

把这套脚本在生产库执行一次，把结果保存到 ConfigBaseline.csv——后面所有调参都有基线可对比。常见诊断结论是：32 核的服务器，max degree of parallelism 是默认的 0（=所有核并发）；物理内存 128GB，max server memory 是默认的 2147483647 MB（=不限）。这两条默认设置，就是导致并发争抢和内存争用的元凶。

二、内存配置 max server memory：留 4GB 给操作系统

SQL Server 的内存管理是「贪吃蛇」模式——它会用尽一切能用的内存。但内存不是越多越好：必须给操作系统、杀毒软件、备份进程留出余量，否则操作系统开始换页时，整个服务器会一起抽风。

错误示范：max memory 设为不限

-- ❌ 这是默认行为，但生产环境几乎从不推荐EXEC sp_configure 'max server memory (MB)', 2147483647; RECONFIGURE; -- 当设置成 2147483647 时，SQL Server 会试图吃光所有内存，-- 操作系统开始换页 → Page Life Expectancy 暴跌 → 全盘性能雪崩

正确的做法是：物理内存 - 4GB（预留 OS），再给备份和其他服务留 2-4GB。脚本写法是分段分配的：

实战脚本：分层计算 max server memory

-- ============================================================-- 内存预留公式（按物理内存分段）-- ============================================================DECLARE @PhysicalMemMB INT = (     SELECT physical_memory_kb / 1024FROM sys.dm_os_sys_info ); DECLARE @SqlMaxMemMB INT =     CASEWHEN @PhysicalMemMB <=  8192THEN @PhysicalMemMB - 1024-- 8GB 以下留 1GBWHEN @PhysicalMemMB <= 32768THEN @PhysicalMemMB - 4096-- 32GB 以下留 4GBWHEN @PhysicalMemMB <= 65536THEN @PhysicalMemMB - 6144-- 64GB 以下留 6GBELSE                          @PhysicalMemMB - 8192-- 64GB 以上留 8GBEND;  -- 启用 advanced options 才能改 max server memoryEXEC sp_configure 'show advanced options', 1; RECONFIGURE;  EXEC sp_configure 'max server memory (MB)', @SqlMaxMemMB; RECONFIGURE;  -- min server memory 设为 max 的一半，缓冲冷启动内存争抢EXEC sp_configure 'min server memory (MB)', @SqlMaxMemMB / 2; RECONFIGURE;  -- 验证设置SELECT     name,     value_in_use,     @PhysicalMemMB AS [物理内存MB],     @SqlMaxMemMB   AS [本次设置的上限MB] FROM sys.configurations WHERE name LIKE'%server memory%'; 

观察命令：调完参数后跑 SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy' 持续 30 分钟——PLE 应该稳定在 300s 以上，越高越好。 

三、并行度配置：MAXDOP 不是越大越好

SQL Server 默认会让一个大查询把所有 CPU 都用满——这听上去是好事，但 32 个线程并发扫描同一个大表时，每个线程分到的数据少，开销大于收益，反而比单线程慢。max degree of parallelism（MAXDOP）就是控制这个行为的关键参数。

诊断：默认 MAXDOP=0 带来的并行风暴

-- 查看当前并行度与使用频率SELECT     name,     value_in_use AS [当前值],     is_advanced FROM sys.configurations WHERE name LIKE'max degree%';  -- 看最近 7 天有多少查询并行跑过、平均开多少线程SELECTCOUNT(*) AS [并行查询总数],     AVG(cpu_time) / 1000.0AS [平均CPU耗时ms],     SUM(CASEWHEN dop = 0THEN1ELSE0END) AS [默认值DOP=0] FROM (     SELECT cpu_time, dop = NULLIF(0,0)     FROM sys.dm_exec_query_stats CROSSAPPLY sys.dm_exec_plan_attributes(NULL)     WHERE last_execution_time > DATEADD(DAY, -7, GETDATE()) ) t; 

实战脚本：按 CPU 核数自动设置 MAXDOP

-- ============================================================-- MAXDOP 推荐策略（来自 SQL Server 官方 + 实战修正）-- ·  1-8 核：MAXDOP = 核数（NUMA 内）-- · 8-16 核：MAXDOP = 8-- · 16 核以上：MAXDOP = 8（80% OLTP 场景）-- ============================================================DECLARE @LogicalCpu INT = (SELECT cpu_count FROM sys.dm_os_sys_info); DECLARE @MaxDop INT =     CASEWHEN @LogicalCpu <=  8THEN @LogicalCpu         WHEN @LogicalCpu <= 16THEN8ELSE8-- 16核+默认 8END;  EXEC sp_configure 'max degree of parallelism', @MaxDop; RECONFIGURE;  -- cost threshold for parallelism 决定「多大开销」才并行-- 默认 5 太低，几乎任何大表查询都会并行EXEC sp_configure 'cost threshold for parallelism', 50; RECONFIGURE;  -- 验证SELECT     @LogicalCpu AS [逻辑核数],     @MaxDop     AS [本次MAXDOP],     value_in_use FROM sys.configurations WHERE name IN ('max degree of parallelism', 'cost threshold for parallelism'); 

数据仓库特例：如果你的库是 OLAP 报表场景（单查询重、并发少），MAXDOP 应该放开到物理核数；反过来如果是 OLTP 高并发系统，MAXDOP 反而要往小了设。生产上要按场景拍板，别用一套参数走天下。 

四、tempdb 配置：磁盘 I/O 争用的隐形炸弹

tempdb 是 SQL Server 的「公共厕所」——临时表、表变量、排序、哈希 join 都在它身上跑。一个数据库实例只有一个默认 tempdb，所有库抢着用；如果 tempdb 文件数不够、数据文件放在慢盘上，整个系统的并发性能都会被它拖累。

现状诊断：tempdb 是不是瓶颈

-- 查看 tempdb 文件数与读写争用SELECT     files = (         SELECTCOUNT(*) FROM sys.database_files         WHERE type_desc = 'ROWS'ANDDB_NAME(database_id) = 'tempdb'     ),     io_stall_queued_read_ms  = SUM(io_stall_queued_read_ms),     io_stall_queued_write_ms = SUM(io_stall_queued_write_ms) FROM tempdb.sys.dm_io_virtual_file_stats(DB_ID('tempdb'), NULL);  -- 拖得很长的 tempdb 等待通常是 PAGELATCH_* 或 IO_COMPLETIONSELECTTOP10     wait_type,     wait_time_ms / waiting_tasks_count AS [平均等待ms],     waiting_tasks_count                AS [次数] FROM sys.dm_os_wait_stats WHERE wait_type LIKE'PAGELATCH_%'OR wait_type = 'IO_COMPLETION'ORDER BY wait_time_ms DESC; 

实战脚本：重建 tempdb 到 N 个等大文件

-- ============================================================-- tempdb 优化三大原则：-- 1. 数据文件数 == 逻辑 CPU 核数 / 2（常用：4-8 个）-- 2. 所有数据文件等大，初始大小也是同样大小-- 3. 放在最快的 SSD 上，启用自动增长但要按 MB 设（不要按 %）-- ============================================================-- 切换到 master 才能动 tempdbUSE master; GO-- 先决定保留几个文件（手动求余避免浮点）DECLARE @Cpu INT = (SELECT cpu_count FROM sys.dm_os_sys_info); DECLARE @FileCount INT = CASEWHEN @Cpu >= 8THEN8ELSE @Cpu END; DECLARE @FileSizeMB INT = 1024;   -- 每个 1GBDECLARE @FileGrowthMB INT = 256; -- 增量 256MB-- 物理位置先准备好DECLARE @DataPath NVARCHAR(260) =     UPPER(SUBSTRING(physical_name, 1, LEN(physical_name)                   - CHARINDEX('\', REVERSE(physical_name)))) FROM tempdb.sys.database_files WHERE file_id = 1;  -- 1) 设置 tempdb 配置ALTERDATABASE tempdb     MODIFYFILE (         name = tempdev,         size = @FileSizeMB,         filegrowth = @FileGrowthMB     ); -- 2) 增加其它数据文件DECLARE @i INT = 2; WHILE @i <= @FileCount BEGINDECLARE @Sql NVARCHAR(MAX) =         'ALTER DATABASE tempdb ADD FILE (' +         'NAME = tempdev' + CAST(@i ASNVARCHAR(10)) + ',' +         'FILENAME = ''' + @DataPath + '\tempdb_' +         CAST(@i ASNVARCHAR(10)) + '.ndf'',' +         'SIZE = ' + CAST(@FileSizeMB ASNVARCHAR(10)) + 'MB,' +         'FILEGROWTH = ' + CAST(@FileGrowthMB ASNVARCHAR(10)) + 'MB);';     EXEC sp_executesql @Sql;     SET @i += 1; END-- 需要重启 SQL Server 才会真正生效PRINT'已修改 tempdb 配置，请重启 SQL Server 生效'; 

操作禁忌：tempdb 文件数不是越多越好，文件超过 8 个收益递减，徒增管理成本。Microsoft 官方推荐：核心数 ≤ 8 时 = 核心数；核心数 > 8 时 = 8 个，不要超过 32。 

五、追踪标志位 TF 4199 / TF 9481：查询优化器的两把钥匙

SQL Server 自 2005 年起，所有对查询优化器的 bug 修复都被「屏蔽」起来，通过追踪标志位（Trace Flag, TF）按需启用。最重要的两个是：

• TF 4199
  ：启用查询优化器的「选择性」修复，会显著改进复杂查询的预估和执行计划生成质量
• TF 9481
  ：强制使用旧的 CE（Cardinality Estimator），对 2014 之前的数据库迁移特别友好

TF 4199 启用前后性能对比（用一张大表实测）

-- ============================================================-- 启用 TF 4199（启动参数加 -T4199，或下面这种方式动态启用）-- ============================================================DBCC TRACEON(4199, -1) WITH NO_INFOMSGS;  -- -1 表示全局-- 验证已开启DBCC TRACESTATUS(4199);  -- ============================================================-- 建表：模拟生产订单表，500 万行-- ============================================================IFOBJECT_ID('dbo.Sales_TFTest', 'U') IS NOT NULLDROP TABLE dbo.Sales_TFTest; GOCREATE TABLE dbo.Sales_TFTest (     SalesId     BIGINT IDENTITY(1,1) PRIMARY KEY,     OrderDate   DATETIME2(0),     Region      NVARCHAR(10),     Category    NVARCHAR(20),     CustomerId  INT,     Amount      DECIMAL(12,2),     Qty         INT,     Note        NVARCHAR(100) ); GO-- 造 500 万行数据（用 CTE 数字表批量插入，约 30 秒）WITH n AS (     SELECTTOP (5000000) n = ROW_NUMBER() OVER (ORDER BY (SELECTNULL))     FROM sys.all_objects a CROSS JOIN sys.all_objects b ) INSERTINTO dbo.Sales_TFTest (OrderDate, Region, Category, CustomerId, Amount, Qty, Note) SELECTDATEADD(MINUTE, -n, GETDATE()),     CHOOSE(n % 5, 'East','West','North','South','Central'),     CHOOSE(n % 10, 'Book','Phone','TV','Food','Toy','Cloth','Shoe','Pen','Bag','Light'),     n % 200000 + 1,     CAST((n % 1000) * 1.27ASDECIMAL(12,2)),     n % 10 + 1,     'note' + CAST(n ASNVARCHAR(20)) FROM n;  -- 关键索引（没有它，查询将全表扫描）CREATE INDEX IX_Sales_Region_Date     ON dbo.Sales_TFTest (Region, OrderDate)     INCLUDE (Amount, Qty); GO-- ============================================================-- 测试查询：每个 Region 最近 7 天的销售额 Top 100-- 这正是 4199 修复的「非均匀数据分布预估」典型场景-- ============================================================SETSTATISTICS IO ON; SETSTATISTICS TIME ON;  SELECT TOP 100     Region, OrderDate, CustomerId, Amount, Qty FROM dbo.Sales_TFTest WITH(INDEX(IX_Sales_Region_Date)) WHERE OrderDate > DATEADD(DAY, -7, GETDATE())   AND Region = 'East'ORDER BY Amount DESC, OrderDate DESC;  SETSTATISTICS IO OFF; SETSTATISTICS TIME OFF; 

启用 TF 4199：SQL Server 2016 起已被默认开启并打包进数据库兼容性级别，但显式打开仍是保险做法。生产环境务必先在测试库验证再上。 

六、调参后的持续验证：5 个核心监控指标

调参不是一次性的事，必须有可量化的反馈。下列 5 个指标是 SQL Server 调优的「心电图」，任何一个异常都要回头排查：

SQL Server 调优心电图脚本

-- ============================================================-- 一键收集 5 个核心指标-- ============================================================-- 1. Page Life Expectancy（页面生命周期，应该 > 300s）SELECTCONVERT(NUMERIC(10,1),     cntr_value / 65535.0 / 60.0) AS [PLE_分钟] FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy'AND object_name = 'SQLServer:Buffer Manager';  -- 2. Buffer Cache Hit Ratio（应该 > 99%）SELECTCONVERT(NUMERIC(5,2),         cntr_value * 1.0 / (SELECT cntr_value FROM sys.dm_os_performance_counters                                      WHERE counter_name = 'Buffer cache hit ratio base')     ) * 100AS [命中率%] FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'AND object_name = 'SQLServer:Buffer Manager';  -- 3. 批请求数 / 秒（CPU 压力大时会高）SELECT     cntr_value AS [BatchReqPerSec] FROM sys.dm_os_performance_counters WHERE counter_name = 'Batch Requests/sec'AND object_name = 'SQLServer:SQL Statistics';  -- 4. 当前等待 TOP 5SELECTTOP5     wait_type,     wait_time_ms / waiting_tasks_count AS [平均等待ms],     waiting_tasks_count FROM sys.dm_os_wait_stats WHERE waiting_tasks_count > 0AND wait_type NOT IN ('SLEEP_TASK', 'SLEEP_BATCH_OPEN', 'BROKER_EVENTHANDLER', 'BROKER_RECEIVE_WAITFOR') ORDER BY wait_time_ms DESC;  -- 5. 综合健康度评估IFOBJECT_ID('tempdb..#Health') IS NOT NULLDROP TABLE #Health; CREATE TABLE #Health (CheckItem NVARCHAR(50), ActualValue NVARCHAR(50), IsHealthy BIT); DECLARE @PLE NUMERIC(10,1) = (     SELECT cntr_value / 65535.0 / 60.0FROM sys.dm_os_performance_counters     WHERE counter_name = 'Page life expectancy'AND object_name = 'SQLServer:Buffer Manager'); INSERT INTO #Health VALUES ('Page Life Expectancy',     CAST(@PLE ASNVARCHAR) + ' min',     CASEWHEN @PLE > 5THEN1ELSE0END); SELECT * FROM #Health; 

总结：5 个参数搞定 80% 的 SQL Server 性能问题

✅ max server memory：物理内存 - 4~8GB，留出系统空间

✅ max degree of parallelism：8 核以下 = 核数；8 核以上 = 8

✅ cost threshold for parallelism：默认 5 太低，调到 50~100

✅ tempdb 文件数：4-8 个等大文件，放最快的 SSD 上

✅ TF 4199：让查询优化器修复全部生效

调参的核心思路是：先采集基线（运行诊断 SQL）→ 小步修改（每次改 1-2 个参数）→ 持续观察（5 个监控指标）→ 出问题快速回滚（sp_configure 历史 SQL 保留）。不要在一个事务里把所有参数同时修改，那会让我们无法定位是哪个改动影响了性能。

​阅读原文：https://mp.weixin.qq.com/s/j7C2ZJPTdVlIz_bauPr5hQ