嵌套子查询本身不触发参数嗅探，但会放大其危害：当主查询因参数值差异大而缓存低效计划时，in/exists类嵌套结构导致内层子查询被重复执行n次（n为外层行数），使io和cpu开销呈指数级增长。
嵌套子查询本身不触发参数嗅探，但它是参数嗅探问题的“放大器”——一旦主查询因参数值差异大而缓存了低效计划，嵌套结构（尤其是 IN 或 EXISTS）会让执行代价呈指数级增长。
为什么嵌套子查询会让参数嗅探更致命
SQL Server 对 WHERE col IN (SELECT ...) 这类结构默认生成嵌套循环连接（Nested Loops），驱动表行数直接决定内层子查询执行次数。第一次传入高选择性参数（如 @status = 0，仅返回 10 行），优化器选了索引查找；但若第一次是低选择性参数（@status = 1，返回 50 万行），计划就固化为全表扫描+50 万次子查询重复执行。
- 执行计划缓存后，后续所有调用都复用这个“最差路径”，哪怕参数已变小
-
sys.dm_exec_query_stats中total_logical_reads异常高、last_execution_time波动剧烈，是典型信号 - 执行计划 XML 里查
<relop logicalop="Nested Loops"> 和 <code>EstimateRows与ActualRows差异超 10 倍，基本坐实
局部变量屏蔽必须覆盖全部嵌套层级
只在主查询 WHERE 里用局部变量，对子查询里的参数无效——子查询仍能“穿透”嗅探到原始参数值。
- 错误写法：
DECLARE @status_local INT = @status; SELECT * FROM a WHERE id IN (SELECT id FROM b WHERE status = @status);→ 子查询仍用@status - 正确写法：子查询中也要用新变量，且类型长度完全一致：
DECLARE @status_local INT = @status; SELECT * FROM a WHERE id IN (SELECT id FROM b WHERE status = @status_local); - 多层嵌套时，每一层子查询涉及的参数都要声明对应局部变量，漏一层就可能失效
比局部变量更稳的替代方案：改写为 JOIN
对大多数 IN/EXISTS 嵌套，JOIN 不仅绕过参数嗅探，还能让优化器基于统计信息重新评估连接顺序和索引选择。
- 原语句：
SELECT * FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Region = @region) - 改写后：
SELECT o.* FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID WHERE c.Region = @region - 必须同步检查：
Orders.CustomerID和Customers.Region是否有索引；若常联合查询，考虑复合索引INDEX idx_customers_region_cid (Region, CustomerID) - 注意 NULL 行为差异：
JOIN自动过滤 NULL，IN遇到子查询返回 NULL 会整体判为 UNKNOWN，结果为空——需验证业务逻辑是否兼容
OPTION (RECOMPILE) 只加在嵌套语句末尾，别加错位置
加在存储过程头部或 EXEC 调用处，对嵌套子查询完全无效；它必须精确落在那个实际慢的子查询语句之后。
- 错误：
CREATE PROCEDURE sp_x AS BEGIN SELECT * FROM a WHERE id IN (SELECT id FROM b WHERE status = @s); END WITH RECOMPILE→ 整个过程重编译，但子查询仍走缓存计划 - 正确：
SELECT * FROM a WHERE id IN (SELECT id FROM b WHERE status = @s OPTION (RECOMPILE))→ 仅该子查询每次重编译 - 适用前提：该子查询执行频次低（如报表）、参数差异极大（如分页 offset 从 10 到 1000000）、且单次编译开销远小于执行耗时
最容易被忽略的是：改完后不查 sys.dm_exec_cached_plans 确认旧计划是否已清除，也不对比 ParameterCompiledValue 和 ParameterRuntimeValue 是否真正脱钩——代码改了，缓存没清，等于白干。
