为什么Server在内联变量时使用更好的执行计划？

Question

我有一个SQL查询正在尝试优化：

DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'

SELECT 
  Id,
  MIN(SomeTimestamp),
  MAX(SomeInt)
FROM dbo.MyTable
WHERE Id = @Id
  AND SomeBit = 1
GROUP BY Id

MyTable有两个索引：

CREATE NONCLUSTERED INDEX IX_MyTable_SomeTimestamp_Includes
ON dbo.MyTable (SomeTimestamp ASC)
INCLUDE(Id, SomeInt)

CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp)

当我按照上面所写的方式执行查询时，Server会扫描第一个索引，导致189,703个逻辑读取和2-3秒的持续时间。

当我内联@Id变量并再次执行查询时，Server将查找第二个索引，结果只有104个逻辑读取和0.001秒的持续时间(基本上是即时的)。

我需要变量，但我希望SQL使用好的计划。作为临时解决方案，我在查询上添加了索引提示，查询基本上是即时的。但是，在可能的情况下，我尽量远离索引提示。我通常假设，如果查询优化器无法完成它的工作，那么我可以做(或停止)一些事情来帮助它，而不必显式地告诉它该做什么。

那么，为什么SQL Server在我内联变量时会想出一个更好的计划呢？

Answer 1

在Server中，有三种常见的非连接谓词形式：

具有文字值的

：

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = 1;

具有参数的

：

CREATE PROCEDURE dbo.SomeProc(@Reputation INT)
AS
BEGIN
    SELECT COUNT(*) AS records
    FROM   dbo.Users AS u
    WHERE  u.Reputation = @Reputation;
END;

带有局部变量的

：

DECLARE @Reputation INT = 1

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = @Reputation;

结果

当您使用一个文字值，并且您的计划不是( 琐碎 )和( b)简单的参数化或c)您没有打开强制参数化时，优化器只为该值创建一个非常特殊的计划。

当您使用参数时，优化器将为该参数创建一个计划(这称为参数嗅探)，然后重用该计划、缺少重新编译提示、计划缓存删除等。

当使用局部变量时，优化器会为.某物。

如果要运行此查询：

DECLARE @Reputation INT = 1

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = @Reputation;

该计划将如下所示：

该局部变量的估计行数如下所示：

即使查询返回的计数为4,744,427。

局部变量在未知的情况下，不要使用直方图中的“好”部分来估计基数。他们使用基于密度矢量的猜测。

SELECT 5.280389E-05 * 7250739 AS [poo]

这将给您382.86722457471，这是优化器所做的猜测。

这些未知的猜测通常是非常糟糕的猜测，往往会导致糟糕的计划和糟糕的索引选择。

修复它？

您的选项通常是：

• 脆性指数提示
• 潜在昂贵的重新编译提示
• 参数化动态SQL
• 存储过程
• 改进当前指数

您的选项具体为：

改进当前索引意味着将其扩展到包括查询所需的所有列：

CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp, SomeTimestamp, SomeInt)
WITH (DROP_EXISTING = ON);

假设Id值具有合理的选择性，这将为您提供一个很好的计划，并通过给它一个“明显的”数据访问方法来帮助优化器。

多读

您可以在这里阅读有关参数嵌入的更多信息：

• 参数嗅探、嵌入和重新编译选项，保罗·怀特著
• 优化存储过程错误的原因(局部变量问题)，肯德拉·利特尔

Answer 2

我无法在这里回答为什么，但确保查询按您希望的方式运行的快速和肮脏的方法是：

DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'
SELECT 
  Id,
  MIN(SomeTimestamp),
  MAX(SomeInt)
FROM dbo.MyTable WITH (INDEX(IX_MyTable_Id_SomeBit_Includes))
WHERE Id = @Id
  AND SomeBit = 1
GROUP BY Id

这可能会导致表或索引在将来发生变化，从而使优化变得不正常，但如果需要的话，它是可用的。希望有人能给你一个根本的原因，如你所要求的答案，而不是这个解决办法。