SQL Server复用小型公司缓存查询计划导致大型公司查询性能下降的优化方案咨询

这个问题本质是参数嗅探（Parameter Sniffing）——SQL Server会缓存第一次执行查询时基于特定参数生成的执行计划，后续复用这个计划，完全没考虑新参数对应的数据集大小差异。除了你提到的索引提示和覆盖索引，还有几个更优雅的解决方案，按推荐优先级排序：

1. 给查询添加 OPTION (RECOMPILE) 选项 #

这是最直接的解决方案，让SQL Server每次执行查询时都重新生成针对当前参数的最优执行计划，不会复用旧计划。

示例代码： #

SELECT SUM(SalesAmount) 
FROM SalesTable 
WHERE Company = @P1 AND Account = @P2
OPTION (RECOMPILE);

原理与优缺点： #

• 原理：每次执行都会触发查询优化器重新分析当前参数对应的数据集大小、统计信息，选择最适合的索引（大公司用非聚集索引，小公司用聚集索引）。
• 优点：针对性强，能保证每次都用最优计划；不需要修改索引或全局设置。
• 缺点：每次编译执行计划会有微小的开销，适合查询执行频率不是特别高的场景（如果是每秒执行几百次的高频查询，这个开销可能累加，需要权衡）。

2. 使用局部变量规避参数嗅探 #

在存储过程或者批处理中，先把输入参数赋值给局部变量，再用局部变量执行查询。SQL Server对局部变量不会做参数嗅探，会基于表的整体统计信息生成一个“通用”执行计划，或者选择更均衡的索引策略。

示例代码： #

DECLARE @LocalCompany NVARCHAR(100) = @P1; -- 类型要和表字段匹配
DECLARE @LocalAccount NVARCHAR(100) = @P2;

SELECT SUM(SalesAmount) 
FROM SalesTable 
WHERE Company = @LocalCompany AND Account = @LocalAccount;

原理与优缺点： #

• 原理：局部变量的参数值在编译阶段是未知的，查询优化器会基于表的平均数据分布生成计划，不会偏向某一个极端参数（比如小公司的小数据集）。
• 优点：只需要修改查询逻辑，不需要全局设置；执行计划只会编译一次，开销小。
• 缺点：生成的通用计划可能不是某类极端场景的最优解（比如超大公司的数据集），但如果你的业务中大部分公司的数据量处于中间范围，这个方案足够好用。

3. 使用计划指南（Plan Guides） #

如果无法修改查询代码（比如查询是由应用程序硬编码的），可以用计划指南给目标查询强制添加RECOMPILE选项，或者指定特定的执行计划，不需要改动应用代码。

示例代码（创建强制重编译的计划指南）： #

EXEC sp_create_plan_guide 
    @name = N'PlanGuide_SalesSum_Recompile',
    @stmt = N'SELECT SUM(SalesAmount) FROM SalesTable WHERE Company = @P1 AND Account = @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 NVARCHAR(100), @P2 NVARCHAR(100)', -- 参数类型要和实际一致
    @hints = N'OPTION (RECOMPILE)';

原理与优缺点： #

• 原理：计划指南相当于在SQL Server层面给指定查询附加执行提示，让优化器按你指定的规则生成计划，完全不用改应用端的代码。
• 优点：对应用透明，适合无法修改查询的场景；可以精准控制单个查询的行为。
• 缺点：需要维护计划指南，如果查询语句发生变化（比如字段名、条件修改），计划指南会失效，需要同步更新。

4. 调整数据库参数化设置（谨慎使用） #

可以把数据库的参数化模式从默认的SIMPLE改为FORCED，强制SQL Server对所有符合条件的查询生成通用执行计划，减少参数嗅探的发生。不过这是全局设置，会影响整个数据库的所有查询，需要谨慎评估。

示例代码： #

ALTER DATABASE [YourDatabaseName] SET PARAMETERIZATION FORCED;

原理与优缺点： #

• 原理：FORCED参数化会让SQL Server把更多的即席查询转化为参数化查询，生成通用计划，避免针对特定参数的计划缓存。
• 优点：全局生效，一次设置解决所有类似的参数嗅探问题。
• 缺点：通用计划可能不适合某些查询的极端场景，导致部分查询性能下降；需要全面测试整个数据库的查询性能后再启用。

内容的提问来源于stack exchange，提问作者user3278315