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

我有一个要优化的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)

当我完全按照上面的描述执行查询时，SQL Server会扫描第一个索引，从而导致189,703次逻辑读取和2-3秒的持续时间。

当我内联@Id变量并再次执行查询时，SQL Server将查找第二个索引，从而导致仅104次逻辑读取和0.001秒的持续时间（基本上是即时的）。

我需要变量，但是我希望SQL使用好的计划。作为临时解决方案，我在查询上添加了索引提示，查询基本上是即时的。但是，我尝试尽可能避免使用索引提示。我通常以为如果查询优化器无法完成其工作，那么我可以做一些事情（或停止做些事情）来帮助它，而无需明确告诉它该做什么。

那么，为什么要做内联变量时，SQL Server提出了一个更好的计划？

#1 楼

在SQL 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;

结果
当您使用文字值，并且您的计划不是a）琐碎的和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值具有合理的选择性，这将为您提供一个很好的计划，并通过给它一个“显而易见的”数据访问方法来帮助优化器。
更多阅读
您可以在此处阅读有关参数嵌入的更多信息：

Paul White的参数嗅探，嵌入和RECOMPILE选项

为什么您要调整存储过程错误（局部变量问题），Kendra Little

#2 楼

我将假设您偏斜了数据，不想使用查询提示来强制优化器执行该操作，并且需要为@Id的所有可能输入值获得良好的性能。如果您愿意创建以下一对索引（或它们的等效索引），则可以确保查询计划对任何可能的输入值只需要进行少量逻辑读操作：

CREATE INDEX GetMinSomeTimestamp ON dbo.MyTable (Id, SomeTimestamp) WHERE SomeBit = 1;
CREATE INDEX GetMaxSomeInt ON dbo.MyTable (Id, SomeInt) WHERE SomeBit = 1;

下面是我的测试数据。我在表中放入了1300万行，并使其中的一半的'3A35EA17-CE7E-4637-8319-4C517B6E48CA'列的值分别为Id。

DROP TABLE IF EXISTS dbo.MyTable;

CREATE TABLE dbo.MyTable (
    Id uniqueidentifier,
    SomeTimestamp DATETIME2,
    SomeInt INT,
    SomeBit BIT,
    FILLER VARCHAR(100)
);

INSERT INTO dbo.MyTable WITH (TABLOCK)
SELECT NEWID(), CURRENT_TIMESTAMP, 0, 1, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

INSERT INTO dbo.MyTable WITH (TABLOCK)
SELECT '3A35EA17-CE7E-4637-8319-4C517B6E48CA', CURRENT_TIMESTAMP, 0, 1, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

此查询起初可能看起来有些奇怪：

DECLARE @Id UNIQUEIDENTIFIER = '3A35EA17-CE7E-4637-8319-4C517B6E48CA'

SELECT
  @Id,
  st.SomeTimestamp,
  si.SomeInt
FROM (
    SELECT TOP (1) SomeInt, Id
    FROM dbo.MyTable
    WHERE Id = @Id
    AND SomeBit = 1
    ORDER BY SomeInt DESC
) si
CROSS JOIN (
    SELECT TOP (1) SomeTimestamp, Id
    FROM dbo.MyTable
    WHERE Id = @Id
    AND SomeBit = 1
    ORDER BY SomeTimestamp ASC
) st;

其设计目的是利用索引的顺序来通过几次逻辑读取来找到最小值或最大值。当没有任何匹配的行作为CROSS JOIN值时，@Id在那里可以得到正确的结果。即使我过滤表中最流行的值（匹配650万行），我也只会得到8个逻辑读值：

表'MyTable'。扫描计数2，逻辑读取8

这是查询计划：

两个索引都查找0或1行。这非常高效，但是创建两个索引对于您的情况可能会显得过大。您可以考虑使用以下索引：

CREATE INDEX CoveringIndex ON dbo.MyTable (Id) INCLUDE (SomeTimestamp, SomeInt) WHERE SomeBit = 1;

现在原始查询的查询计划（带有可选的MAXDOP 1提示）看起来有些不同：

不再需要键查找。有了一个更好的访问路径，该路径应适用于所有输入，因此您不必担心优化器由于密度矢量而选择了错误的查询计划。但是，如果您查询流行的@Id值，则此查询和索引的效率将不如另一个。

表'MyTable'。扫描计数1，逻辑读取为33757

#3 楼

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

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

这会带来风险该表或索引将来可能会更改，从而使此优化功能失灵，但是如果需要，它可以使用。希望有人可以按照您的要求为您提供根本原因的答案，而不是这种解决方法。

编程黑洞网

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

#1 楼

#2 楼

#3 楼