SQL Server 查询计划

Question

我有下面列出的 3 个表

CREATE TABLE dbo.RootTransaction
    (
    TransactionID int CONSTRAINT [PK_RootTransaction] PRIMARY KEY NONCLUSTERED (TransactionID ASC)
    )

GO
----------------------------------------------------------------------------------------------------

CREATE TABLE [dbo].[OrderDetails](
    [OrderID] int identity(1,1) not null,
    TransactionID int,
    OrderDate datetime,
    [Status] varchar(50)
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED ([OrderID] ASC),
 CONSTRAINT [FK_TransactionID] FOREIGN KEY ([TransactionID]) REFERENCES [dbo].[RootTransaction] ([TransactionID]),
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [ix_OrderDetails_TransactionID]
    ON [dbo].[OrderDetails](TransactionID ASC, [OrderID] ASC);

GO
----------------------------------------------------------------------------------------------------
CREATE TABLE dbo.OrderItems
(
    ItemID int identity(1,1) not null,
    [OrderID] int,
    [Name]  VARCHAR (50)  NOT NULL,
    [Code]  VARCHAR (9)   NULL, 
    CONSTRAINT [PK_OrderItems] PRIMARY KEY NONCLUSTERED ([ItemID] ASC),
    CONSTRAINT [FK_OrderID] FOREIGN KEY ([OrderID]) REFERENCES [dbo].[OrderDetails] ([OrderID])
)
Go
CREATE CLUSTERED INDEX OrderItems
    ON [dbo].OrderItems([OrderID] ASC, ItemID ASC) WITH (FILLFACTOR = 90);
GO
CREATE NONCLUSTERED INDEX [IX_Code]
    ON [dbo].[OrderItems]([Code] ASC) WITH (FILLFACTOR = 90)
----------------------------------------------------------------------------------------------------

Populated sample data in each table
select COUNT(*) from RootTransaction -- 45851
select COUNT(*) from [OrderDetails] -- 50201
select COUNT(*) from OrderItems --63850
-- Query 1
SELECT  o.TransactionID 
FROM [OrderDetails] o
JOIN dbo.OrderItems i ON o.OrderID = i.OrderID
WHERE i.Code like '1067461841%'


declare @SearchKeyword  varchar(200) = '1067461841'

-- Query 2
SELECT  o.TransactionID 
FROM [OrderDetails] o
JOIN dbo.OrderItems i ON o.OrderID = i.OrderID
WHERE i.Code like @SearchKeyword + '%'  

当运行以上 2 个查询时，我可以看到查询 1 在 OrderDetails、OrderItems 上使用索引查找，这是预期的，然而，在查询 2 中，查询计划在 OrderItems 上使用索引查找，但在 OrderDetails 上使用索引扫描。两个查询的唯一区别是在 LIKE 中使用直接值与变量，并且两者都返回相同的结果。为什么查询执行计划在使用直接值与变量之间发生变化？

Answer 1

我相信这个问题很可能是通过参数嗅探来解释的。 SQL Server 通常会识别并缓存常用查询的查询计划。作为缓存的一部分，它会“嗅探”您在最常见的查询中使用的参数，以优化计划的创建。

查询 1 显示直接字符串，因此 SQL 创建特定计划。查询 2 使用中间变量，这是实际上防止参数嗅探的技术之一(通常用于为参数有显着差异的存储过程或查询提供更可预测的性能。尽管明显，这些被认为是 2 个完全不同的 SQL 查询相似之处。观察到的差异本质上只是优化。

此外，如果您的表具有不同的行数分布，则基于现有索引和潜在优化，这两种方案可能存在潜在差异。在我的没有加载示例数据的服务器上，查询 1 和查询 2 具有相同的执行计划，因为优化器无法找到任何更好的参数路径。

了解更多信息:http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx