主页 / dba / 问题 / 206815
Accepted
Rainbolt
Asked: 2018-05-16 13:45:56 +0800 CST

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

  • 772

我有一个要优化的 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 会提出更好的计划呢？

sql-server performance

3 个回答

  1. Best Answer

    在 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值具有合理的选择性，这将为您提供一个很好的计划，并通过为优化器提供一个“明显的”数据访问方法来帮助优化器。

    更多阅读

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

    • 47

  2. 我将假设您有倾斜的数据，您不想使用查询提示来强制优化器做什么，并且您需要为所有可能的输入值获得良好的性能@Id。如果您愿意创建以下一对索引（或它们的等价物），您可以获得一个保证对任何可能的输入值只需要少量逻辑读取的查询计划：

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

    下面是我的测试数据。我将 13 M 行放入表中，并使其中一半的'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 个逻辑读取：

    表“我的表”。扫描计数 2，逻辑读取 8

    这是查询计划：

    在此处输入图像描述

    两个索引查找都找到 0 或 1 行。它非常有效，但创建两个索引对于您的场景来说可能是多余的。您可以考虑使用以下索引：

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

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

    在此处输入图像描述

    不再需要密钥查找。有了更好的访问路径，它应该适用于所有输入，您不必担心优化器会因为密度向量而选择错误的查询计划。@Id但是，如果您寻找流行的值，则此查询和索引不会像其他查询和索引那样有效。

    表“我的表”。扫描计数 1，逻辑读取 33757

    • 13

  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

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

    • 2

相关问题