sql - 使用 TOP 和 ESCAPE 更改查询计划和执行时间

Question

其中一个查询(如下所示)的执行时间超过 90 秒。它从相当大的 LogMessage 表中返回约 500 行。如果从查询中删除 ESCAPE N'~'，它将在几秒钟内执行。同样，如果TOP (1000)被删除，它会在几秒钟内执行。在第一种情况下，查询计划显示了Key Lookup (Clustered) PK_LogMessage、Index Scan (NonClustered) IX_LogMessage 和 Nested Loops (Inner Join)。删除子句 ESCAPE N'~' 或 TOP (1000) 后，查询计划会更改并显示聚集索引扫描(聚集)PK_LogMessage。当我们考虑添加更多索引(可能在 ApplicationName 上)时，我们想了解当前发生的情况。

查询是从 Entity Framework 生成的，以防您想知道为什么要这样编写。此外，实际查询更为复杂，但这是表现出相同行为的最短版本。

查询:

SELECT TOP (1000) 
    [Project1].[MessageID] AS [MessageID], 
    [Project1].[TimeGenerated] AS [TimeGenerated], 
    [Project1].[SystemName] AS [SystemName], 
    [Project1].[ApplicationName] AS [ApplicationName]
FROM
    (
        SELECT
            [Project1].[MessageID] AS [MessageID],
            [Project1].[TimeGenerated] AS [TimeGenerated],
            [Project1].[SystemName] AS [SystemName],
            [Project1].[ApplicationName] AS [ApplicationName]
        FROM
        (
            SELECT 
                [Extent1].[MessageID] AS [MessageID], 
                [Extent1].[TimeGenerated] AS [TimeGenerated], 
                [Extent1].[SystemName] AS [SystemName], 
                [Extent1].[ApplicationName] AS [ApplicationName]
            FROM
                [dbo].[LogMessage] AS [Extent1]
            INNER JOIN
                [dbo].[LogMessageCategory] AS [Extent2]
            ON
                [Extent1].[CategoryID] = [Extent2].[CategoryID]
            WHERE
                ([Extent1].[ApplicationName] LIKE N'%tier%' ESCAPE N'~')
        )  AS [Project1]
    )  AS [Project1]
ORDER BY
    [Project1].[TimeGenerated] DESC

表日志消息:

CREATE TABLE [dbo].[LogMessage](
    [MessageID] [int] IDENTITY(1000001,1) NOT NULL,
    [TimeGenerated] [datetime] NOT NULL,
    [SystemName] [nvarchar](256) NOT NULL,
    [ApplicationName] [nvarchar](512) NOT NULL,
        [CategoryID] [int] NOT NULL,
 CONSTRAINT [PK_LogMessage] PRIMARY KEY CLUSTERED 
(
    [MessageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[LogMessage]  WITH CHECK ADD CONSTRAINT [FK_LogMessage_LogMessageCategory] FOREIGN KEY([CategoryID])
    REFERENCES [dbo].[LogMessageCategory] ([CategoryID])

ALTER TABLE [dbo].[LogMessage] CHECK CONSTRAINT [FK_LogMessage_LogMessageCategory]

ALTER TABLE [dbo].[LogMessage] ADD  DEFAULT ((100)) FOR [CategoryID]

CREATE NONCLUSTERED INDEX [IX_LogMessage] ON [dbo].[LogMessage] 
(
    [TimeGenerated] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

表日志消息类别:

CREATE TABLE [dbo].[LogMessageCategory](
    [CategoryID] [int] NOT NULL,
    [Name] [nvarchar](128) NOT NULL,
    [Description] [nvarchar](256) NULL,
 CONSTRAINT [PK_LogMessageCategory] PRIMARY KEY CLUSTERED 
(
    [CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

查询计划 1(需要 90 秒以上)

查询计划 2(大约需要 3 秒)

Answer 1

对我来说，这看起来像是一个直接的参数嗅探问题。

如果您想要按 TimeGeneerated 排序的 TOP 1000，SQL Server 可以扫描 TimeGenerate 上的索引并针对基表进行查找评估 ApplicationName 上的谓词，并在找到第 1,000 行时停止，或者它可以执行聚集索引扫描，查找与 ApplicationName 谓词匹配的所有行，然后执行 TOP N 就是这些。

SQL Server 维护字符串列的统计信息。如果第一个计划认为许多行最终将与 ApplicationName 谓词匹配，则更有可能选择第一个计划，但是该计划并不真正适合作为参数化查询重复使用，因为它可能会造成灾难性的后果如果很少有行匹配，效率就会很低。如果匹配项少于 1,000 个，则肯定需要执行与表中行数一样多的键查找。

通过这方面的测试，我没有发现添加或删除冗余 ESCAPE 会改变 SQL Server 基数估计的任何情况。当然，更改参数化查询的文本意味着原始计划无法使用，并且需要编译一个不同的计划，该计划可能更适合当前考虑的特定值。