Change query plan and runtime using TOP and ESCAPE

Question

One of the queries (below) takes 90 seconds. It returns ~ 500 rows from a fairly large LogMessage table. If ESCAPE N'~' is removed from the request, it is executed within a few seconds. Similarly, if TOP (1000) is deleted, it runs in a few seconds. The query plan shows Key Lookup (Clustered) PK_LogMessage, Index Scan (NonClustered) IX_LogMessage and Nested Loops (Inner Join) in the first case. When the ESCAPE N'~' or TOP (1000) items are deleted, the query plan changes and displays the Clustered Index Scan (Clustered) PK_LogMessage . Although we are exploring the addition of additional indexes (possibly in the ApplicationName), we would like to understand what is currently happening.

The request is generated from the Entity Framework in case you are wondering why it is written this way. Also, the current request is more complex, but this is the shortest possible version that demonstrates the same behavior.

Query:

 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 

LogMessage table:

 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] 

LogMessageCategory table:

 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] 

Request Plan 1 (takes 90 seconds)

Request Plan 2 (takes ~ 3 seconds)

Answer 1

This looks like a problem with the primary parameter for sniffing.

How do you want the TOP 1000 ordered by TimeGenerated SQL Server to be able to either scan the index on TimeGenerated or search the base table to evaluate the predicate on ApplicationName and stop when a row 1000 is found or it can perform a clustered index scan to find all rows matching predicate ApplicationName , and then make them TOP N

SQL Server supports row column statistics. The first plan is most likely to be selected if it believes that many rows will correspond to the ApplicationName predicate, however, this plan is not suitable for reuse as a parameterized query, since it can be catastrophically ineffective if several rows coincide. If less than 1000 matches match, you will definitely need to make as many key queries as there are rows in the table.

From testing this goal, I could not find a situation where adding or removing redundant ESCAPE modified SQL Server power ratings. Of course, changing the text of a parameterized query means that the original plan cannot be used, however, it must compile another one, which is likely to be more suitable for the particular value that is currently being considered.

Answer 2

Why all these subqueries? The code below does the same job

  SELECT TOP(1000) [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'~') ORDER BY [Extent1].[TimeGenerated] DESC 

I also agree that ESCAPE N '~' can be excluded because I cannot find a reason to use it.

Answer 3

For starters, I would simplify the request as indicated by @niktrs. Although the execution plan seems to ignore subqueries, it makes it more human-friendly and thus easier to manipulate and understand.

Then you have an INNER JOIN, which seems to me that he can leave. Is there a “real” need for an INNER JOIN LogMessage for a LogMessageCategory? You can perform a quick check using the following.

 SELECT LM.CategoryID AS FromLogMessage, LMC.CategoryID AS FromLogMessageCategory FROM dbo.LogMessage AS LM FULL OUTER JOIN dbo.LogMessageCategory AS LMC ON LMC.CategoryID = LM.CategoryID WHERE LM.CategoryID IS NULL OR LMC.CategoryID IS NULL 

Answer 4

How does it work if you do it?

 Select * FROM (your whole scary framework query with the escape N) a LIMIT 1000 (or the mssql alternative if mssql does not support the correct syntax -- ) 

Because if these are rolls .. there is a chance that you can continue to use this framework and get decent performance from really bad sql (for example ... it would mean that you create full rs and then select only 1k from it .. )