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
