sql-server – 当我内联变量时,为什么SQL Server使用更好的执行

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会提出更好的计划呢？

解决方法

与字面值比较：

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)Trivial和b)简单参数化或c)您没有打开Forced Parameterization时,优化器会为该值创建一个非常特殊的计划.

当您使用参数时,优化器将为该参数创建一个计划(这称为parameter sniffing),然后重用该计划,缺少重新编译提示,计划缓存驱逐等.

当您使用局部变量时,优化程序会为… Something制定计划.

如果您要运行此查询：

DECLARE @Reputation INT = 1

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

该计划看起来像这样：

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

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

未知的局部变量不使用直方图的“好”部分进行基数估计.他们使用基于密度向量的猜测.

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

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

> Parameter Sniffing,Embedding,and the RECOMPILE Options,Paul White
> Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables),Kendra Little

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!