SQL Server 的提示Hint在不少 DBA 眼里是个爱恨交织的东西用好了是外科手术刀用不好就是拆家的锤子。特别在存储过程优化那一摊事里很多人一遇到慢查询条件反射就是往语句末尾塞OPTION(RECOMPILE)再不行就加表提示好像不加点 Hint 就不算做过优化。实际上这种习惯往往掩盖了真正的瓶颈甚至会让本来还行的计划变得更糟。这篇是 Hint 使用系列的第九篇也是“常用 Hint”的第四部分。前面几篇已经拆过表提示、索引提示和一些基础查询提示这篇我聚焦在四个绕不开的查询级提示上OPTIMIZE FOR与OPTIMIZE FOR UNKNOWN、MAXDOP、FAST n、FORCE ORDER最后聊一下 SQL Server 2016 之后逐步普及的USE HINT语法。它们不会像NOLOCK、INDEX、FORCESEEK那样直接改访问路径和锁行为而是通过影响基数估计、并行度、优化目标和连接顺序这四个维度来改变计划。理解这些差异才知道什么时候该出手什么时候该收手。1. OPTIMIZE FOR 与 OPTIMIZE FOR UNKNOWN别让第一次执行的参数“绑架”计划1.1 参数嗅探先复习三分钟SQL Server 在执行带参数的查询或存储过程时默认会“嗅探”第一次传入的参数值把它作为基数估计的输入生成一个执行计划并缓存下来。后续即使参数值变了只要计划还在缓存里就会继续沿用这个计划。这个机制本身没毛病问题出在参数值的分布上。假设有一张千万行的订单表某存储过程按Status过滤订单状态CREATE PROCEDURE dbo.usp_GetOrderList Status TINYINT AS BEGIN SET NOCOUNT ON; SELECT OrderID, OrderTime, TotalAmount FROM dbo.Orders WHERE Status Status; END如果第一次执行时传入的是Status 3已完成而这张表里“已完成”的订单占比高达 80%优化器会基于 80% 的选择性选择一个扫描路线可下一次调用传入Status 1新建只占 0.1%本该走索引查找但缓存里的旧计划还在于是查询继续用扫描性能自然就崩了。这是参数嗅探最典型的翻车现场。之前几篇我们聊过不少处理手段这篇的主角OPTIMIZE FOR就是其中一种比较精细的干预方式。1.2 OPTIMIZE FOR给优化器一个“标准答案”OPTIMIZE FOR的用法很直接你告诉优化器编译时就按我给定的参数值做基数估计。CREATE PROCEDURE dbo.usp_GetOrderList Status TINYINT, CustomerID INT AS BEGIN SET NOCOUNT ON; SELECT OrderID, OrderTime, TotalAmount FROM dbo.Orders WHERE Status Status AND CustomerID CustomerID OPTION (OPTIMIZE FOR (Status 3, CustomerID 10086)); END这里的意思不是强制过滤Status 3、CustomerID 10086而是告诉优化器基数估计时把这两个参数当成 3 和 10086 来算。实际运行时不管调用方传什么值过滤条件还是按真实参数走只有计划生成时用的估算是按你给定的值来的。什么时候值得这么干一种典型场景是参数值分布极度不均业务上又有一个高频值。比如这个电商系统的订单状态“已完成”占了绝大多数查询流量其余状态查询量很小但也不能慢。此时用OPTIMIZE FOR (Status 3)把计划引导到适合“大多数查询”的形状上剩下少部分冷门状态即使计划不完美可接受度也高。用OPTIMIZE FOR时硬编码的值一定要慎重。我见过有人随手写了个开发环境里的 ID部署到生产后由于数据分布完全不同计划反而比原来更差。建议把这个值的选取理由写到注释里例如-- 生产订单约 70% 为已完成状态因此基数估计固定按已完成处理 OPTION (OPTIMIZE FOR (Status 3));1.3 OPTIMIZE FOR UNKNOWN用“平均值”代替“某一个值”如果说OPTIMIZE FOR是给优化器指定一个演员那OPTIMIZE FOR UNKNOWN就是让优化器不要依赖具体某个演员改用统计信息里的“平均密度”去估算。SELECT OrderID, OrderTime, TotalAmount FROM dbo.Orders WHERE Status Status OPTION (OPTIMIZE FOR UNKNOWN);这种方式特别适合参数值本身无法预测、没有明显典型值、且各值分布相对均衡的场景。SQL Server 会基于列统计信息中的密度即“平均选择性”来生成计划尽量让计划在大多数取值下都“不算太差”而不是偏袒某一个具体值。OPTIMIZE FOR UNKNOWN不等于“不嗅探”。它仍然走参数嗅探的流程但最终用于基数估计的是统计密度而不是实际传入参数。这一点和后面要讲的DISABLE_PARAMETER_SNIFFING有细微差别我会在第 5 节里细说。OPTIMIZE FOR UNKNOWN也支持混合写法比如对部分参数指定值、部分参数用 UNKNOWNOPTION (OPTIMIZE FOR (Status 3, CustomerID UNKNOWN));这在某些复杂查询里很实用状态字段有明确高频值客户维度却无法预测那就分开处理。1.4 两个容易翻车的组合用法有人喜欢把OPTIMIZE FOR和RECOMPILE一起用理由很朴素“每次重编译时用固定值评估总比随便嗅探强吧”这个思路部分成立但代价是每次执行都要重新编译CPU 开销和编译锁等待都会明显上升。对于高并发 OLTP 系统来说这往往得不偿失。我的建议是如果能通过固定值把计划稳住就优先只加OPTIMIZE FOR保留计划缓存复用只有当你确实需要每次基于最新参数值和最新统计信息重新生成计划时才考虑组合。另一个坑是OPTIMIZE FOR并不会绕过统计信息过期的问题。如果列统计本身已经严重失真你指定一个值也无济于事——统计信息是根提示只是枝叶。排查问题顺序永远是先更新统计、补索引再考虑上提示。2. MAXDOP 提示单条查询的并行度控制阀2.1 查询级 MAXDOP 和全局配置不是一回事大部分人对 MAXDOP 的认识停留在“服务器最大并行度”这个配置选项上也就是实例级别。实际上查询级MAXDOP提示允许你在单条语句里覆盖这个限制语法很简单SELECT OrderID, SUM(Quantity * UnitPrice) AS TotalAmount FROM dbo.OrderDetails GROUP BY OrderID OPTION (MAXDOP 4);这里的 4 表示该查询最多只能使用 4 个 CPU 来执行并行操作。如果写成OPTION (MAXDOP 1)就是禁用并行强制走串行计划。很多团队陷入一个误区只要看到 CXPACKET 等待就觉得是并行惹的祸然后一把梭给所有查询都加上MAXDOP 1。这种做法在特定场景下确实有效但本质上是用“阉割并行”去掩盖问题。一条正常的决策分析查询在 8 核机器上串行执行可能要 8 分钟并行 4 线程可能 2 分多就出结果了你这么一改响应时间反而更难看。2.2 什么时候我真的需要在查询上加 MAXDOP查询级MAXDOP最常见的适用场景是“混合负载下的局部保护”。比如一个在线交易系统绝大多数查询应该是轻量级的 OLTP 操作但不巧有一条报表类查询每隔几分钟跑一次每次都要占用所有 CPU 核心做并行哈希连接把正常的订单查询拖得喘不过气。这时给这条重查询单独加OPTION (MAXDOP 2)或OPTION (MAXDOP 4)给核心交易链路留出 CPU 余量比全局调低服务器 MAXDOP 更精准。另一种场景是并行计划的额外开销大于收益。有些查询本身数据量不大但优化器低估或高估了并行带来的加速生成一个动辄 8 线程的夸张并行计划。用查询级 MAXDOP 把并行度限制在合理范围往往能让 CPU 总消耗下降查询时间不升反降。我建议在加提示之前先抓一次实际执行计划看并行分支里到底有几个交换运算符、每个线程实际处理多少行。如果明明只有几万行数据还分成 8 个线程去处理那确实应该限制 MAXDOP如果数据量很大并行度已经帮你在省时间就别动它。2.3 MAXDOP 提示的边界它控制的是并行算子不是并发压力要特别澄清一个认知误区MAXDOP提示控制的是单个查询内部可以使用的并行度它不管系统里同时有多少个查询在跑。换句话说你给一条查询加MAXDOP 2只是限制它自己最多用两个 CPU 核心并不会阻止其他查询并行执行。所以如果生产环境的瓶颈是“同时并发的查询太多CPU 整体被打满”靠查询级 MAXDOP 解决不了本质问题那是容量规划、资源池或语句并发量控制的范畴。遇到这种情况先数清楚活跃会话和整体 CPU 使用率再决定要不要动 MAXDOP。很多人把这个顺序搞反了折腾半天提示最后发现是其他原因。3. FAST n 提示首屏变快了整体可真不一定更快3.1 这个 Hint 到底干了什么FAST n是查询提示里比较容易被忽略的一个因为它的效果不像其他提示那么直观。语法很简单SELECT OrderID, CustomerName, OrderTime, TotalAmount FROM dbo.Orders o INNER JOIN dbo.Customers c ON o.CustomerID c.CustomerID ORDER BY OrderTime DESC OPTION (FAST 25);它的含义是告诉优化器我希望尽快返回前 25 行。优化器会因此调整优化目标在“返回前 25 行的速度”和“返回完整结果集的总成本”之间做重新权衡。它不会限制语句只读 25 行也不会把结果集截断只是影响执行计划的选择。典型场景是分页列表。前端用户点开一个订单列表页只需要先看到第一屏 25 条记录后面的数据慢慢翻。此时计划如果偏向于先快速产出前 25 行用户体验会明显好于那种“必须把全部结果处理完才一次性返回”的经典计划。3.2 但代价往往藏在你看不见的地方FAST n的本质是用“总执行时间”换“首行响应时间”。优化器可能为此选择嵌套循环连接来代替哈希连接或者把排序操作改成更适合提前输出的算子。假设底层数据是千万级订单表按时间倒序排列使用FAST 25后SQL Server 可能会为了“尽早给出前 25 行”设计出一个先走索引、快速抓取部分行的路线这个计划在执行到后半段时可能会退化成反复回表总耗时比普通计划还要长。如果你的报表查询本来就要取全部数据做汇总比如后台导出、批量计算、ETL 抽取那加FAST n不但没有意义还可能让整体执行时间恶化。凡是要“跑完整结果集”的任务都不应该被“首屏快”这个目标带偏。还有一个容易混淆的点FAST n和TOP n是两回事。TOP n是逻辑上只返回 n 行FAST n是逻辑上返回所有行只是优化目标偏向前 n 行。别指望用FAST 25去代替分页里的TOP 25这俩不能互相替换。3.3 什么场景下值得用它我的经验是交互式 Web 应用里的“查询后立即展示第一屏”场景最适合特别是后台无法轻易重写分页 SQL、但又希望改善用户等待时间的处境。比如某些老系统里的报表页面前端框架会一次性请求大量数据页面加载缓慢。在 SQL 末尾加一个FAST 30或FAST 50让数据库先把前面的记录吐出来前端能更快渲染首屏等后续数据慢慢到了再补充。实测中有些查询首行返回时间能从 4 秒压到 0.5 秒以内但总跑完时间可能会增加两三秒。这种取舍是否划算完全取决于业务场景。用FAST n前也建议先量两句 SQL 的时间差一句加提示一句不加分别看“首次返回时间”和“总执行时间”。如果首行优势明显、总耗时可接受那就保留如果两者差别不大说明优化器根本没法从这句 SQL 里挤出更好的形状加了纯属浪费阅读理解成本。4. FORCE ORDER强制连接顺序用好了是绝活用歪了是灾难4.1 连接顺序是如何影响计划质量的多表连接时优化器会决定先连哪张表、后连哪张表。在内连接里这个顺序不是从FROM子句的表顺序简单照搬的——优化器会基于代价模型在多个候选连接顺序里挑一个总成本最低的。由于基数估计不可能永远精确一旦某个表的预估行数偏差太大选出的连接顺序就可能与实际数据分布严重不符。举个例子SELECT c.CustomerName, o.OrderID, d.ProductName, d.Quantity FROM dbo.Customers c INNER JOIN dbo.Orders o ON c.CustomerID o.CustomerID INNER JOIN dbo.OrderDetails d ON o.OrderID d.OrderID WHERE c.City N上海 OPTION (FORCE ORDER);FORCE ORDER会要求优化器按照Customers - Orders - OrderDetails的顺序来连接即先拿“上海客户”这个小集合再逐个去Orders上匹配订单最后匹配明细。如果优化器原本打算从OrderDetails开始做哈希连接那显然就是“生成的计划顺序不符合我们的预期”。这种强制手段的价值在于当你已经通过业务知识确认某个表经过过滤后行数极少而优化器却因为统计信息问题把它放到了后面当驱动表导致大量随机 I/O 时FORCE ORDER可以替你“拍板”。我自己在一个分页订单查询里遇到过类似情况客户表过滤后只剩几十行优化器却因为估算偏差把订单表当驱动表跑出十几秒。临时加FORCE ORDER后稳定在几百毫秒后来靠更新统计信息又去掉了这个提示。4.2 强制顺序的副作用必须提前想清楚FORCE ORDER一个容易被忽略的问题它同时限制了优化器在后续阶段的某些重排能力。例如当查询里存在视图、子查询或者自动参数化时连接顺序不一定按你肉眼看到的表名排列经常会出现“你以为已经强制了其实优化器看到的树形结构和你想的完全不一样”的情况。这时加FORCE ORDER不但无效还可能迫使优化器保留一个本来就不合理的中间结果。更危险的是另一个副作用它关闭了优化器基于代价模型进行的平衡搜索。一旦统计信息更新或索引发生变更FORCE ORDER强制出来的计划可能从“救命恩人”变成“性能杀手”而且这种劣化是静默发生的——你很难从字面上看出哪里错了。我在生产环境处理过一个惨痛教训某团队为了修一条慢查询加上FORCE ORDER上线后确实快了。两周后例行索引维护这条查询突然从几百毫秒变成 20 多秒最后排查发现就是因为强制顺序锁死了一个已经不再最优的连接路径。索引维护后统计信息发生了大变化原本应该用哈希连接的场景被强制顺序按嵌套循环一条条猛查。4.3 可以和其他连接提示组合但暴力程度要心里有数FORCE ORDER只强制顺序不强制连接类型。如果你连连接类型也想定死就得配合LOOP、HASH、MERGE这些连接提示SELECT ... FROM dbo.Customers c INNER LOOP JOIN dbo.Orders o ON c.CustomerID o.CustomerID INNER LOOP JOIN dbo.OrderDetails d ON o.OrderID d.OrderID OPTION (FORCE ORDER);这样“顺序 连接类型”都被钉死优化器的活动空间极小。除非索引结构已经确定长时间不变、数据分布也比较稳定否则我通常不建议同时锁定两者。提示用得越暴力后续可维护性就越差。真正的做法是先用执行计划和统计信息把根因找到再用最小必要的提示做干预。5. USE HINT 与 DISABLE_PARAMETER_SNIFFING更现代、可读性更好的选择5.1 USE HINT 的基本用法SQL Server 2016 引入了USE HINT这个查询提示语法它把一些原本分散在不同功能里的开关统一起来了读起来也更直白。最常用的一条是DISABLE_PARAMETER_SNIFFINGSELECT OrderID, OrderTime, TotalAmount FROM dbo.Orders WHERE Status Status AND CustomerID CustomerID OPTION (USE HINT(DISABLE_PARAMETER_SNIFFING));从语义上说这等于告诉优化器编译时不采样实际参数值改用统计密度做基数估计。和OPTIMIZE FOR UNKNOWN的效果很接近但USE HINT的表达更像一个明确的“开关”语义清晰注释成本低。如果你团队里有新人接手老代码读USE HINT(DISABLE_PARAMETER_SNIFFING)比读OPTIMIZE FOR UNKNOWN更容易理解意图。当然USE HINT不止这一条。像FORCE_DEFAULT_CARDINALITY_ESTIMATION_MODEL、DISABLE_OPTIMIZED_NESTED_LOOP、ENABLE_QUERY_OPTIMIZER_HOTFIXES等都有各自的使用场景不过本文只围绕最常见的DISABLE_PARAMETER_SNIFFING展开。如果你们生产环境版本比较老建议先确认兼容性再决定是否上这个语法。5.2 几个参数嗅探处理方案放在一起对比很多人在面对存储过程参数嗅探问题时不知道OPTIMIZE FOR、OPTIMIZE FOR UNKNOWN、DISABLE_PARAMETER_SNIFFING到底该怎么选。我整理了一张简单的对照表处理方式基数估计依据计划缓存行为典型适用场景不干预第一次执行的实际参数正常复用参数分布稳定、首次参数有代表性OPTIMIZE FOR(p 固定值)指定的硬编码值正常复用参数值有明显高频典型值OPTIMIZE FOR UNKNOWN统计密度平均选择性正常复用参数值不可预测、分布均匀USE HINT(DISABLE_PARAMETER_SNIFFING)统计密度忽略实际参数正常复用想明确关闭嗅探、提升代码可读性从效果上看DISABLE_PARAMETER_SNIFFING和OPTIMIZE FOR UNKNOWN有很高的相似度但OPTIMIZE FOR UNKNOWN支持在同一个提示里混合“指定值”和“未知”粒度更细。比如某个过程里状态参数有高频值、客户参数完全不可控用混合写法比全局关嗅探更精准。5.3 提示不是银弹先用查询存储和计划指南做事后管理版本允许的前提下我建议把“加提示”当成最靠后的手段来考虑而不是首选。SQL Server 的查询存储Query Store提供了计划强制、计划回归检测这些能力比在代码里硬写 Hint 更灵活改动代码也没那么大心理负担。计划指南Plan Guide也能在不改应用代码的情况下把提示附加到特定查询上。举个实际场景某报表系统里一条 SQL 无法改源码但又确实需要加OPTIMIZE FOR来稳定计划。完全可以通过计划指南把提示注入进去后续要回滚也只是删掉计划指南不需要重新发版。这比在业务代码里焊死一个提示要安全得多。我在日常工作中给自己定了一条原则任何 Hint 被写进源码之前先在开发环境用带真实数据分布的表验证两遍并记录下“为什么加”的业务理由。没有理由的提示将来就是别人排查问题时的路障。最后分享一点实操习惯做了一个季度的慢查询治理我发现一个规律真正需要长期保留在代码里的 Hint 少之又少更多时候提示只是临时止血根本治疗还是统计信息维护、索引设计和查询改写。如果你不得不用我建议用sys.dm_exec_query_stats和sys.dm_exec_query_plan抓一下加提示前后的 CPU、逻辑读、执行次数留一组可对比的数据再决定是否在正式代码里长期保留。另外无论是OPTIMIZE FOR、MAXDOP还是FAST n请在注释里写清楚三件事这条查询为什么需要 Hint、预期的计划形状是什么、如果哪天统计信息更新后此暗示失效该找谁看。数据库是会进化的数据分布也不是一尘不变的一个当年救命的 Hint很有可能在未来的某天变成拖累性能的罪魁祸首。