title: Change in query plan and execution time with TOP and ESCAPE
body: <p>One of the query (given below) is taking 90+ seconds to execute. It returns ~500 rows from a rather large table LogMessage. If <code>ESCAPE N'~'</code> is removed from the query it executes within few seconds. Similarly if <code>TOP (1000)</code> is removed, it executes within few seconds. The query plan shows <code>Key Lookup (Clustered) PK_LogMessage, Index Scan (NonClustered) IX_LogMessage and Nested Loops (Inner Join)</code> in the first case. When the clauses <code>ESCAPE N'~'</code> or <code>TOP (1000)</code> are removed the query plan changes and shows <code>Clustered Index Scan (Clustered) PK_LogMessage</code>. While we are looking into adding more indexes (probably on ApplicationName), we would like to understand what is going on currently.</p>
  
  <p>The query is being generated from <code>Entity Framework</code> in case you wonder why it is being written this way. Also the actual query is more complex but this is the shortest possible version that exhibits the same behavior.</p>
  
  <p>Query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Table LogMessage:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>Table LogMessageCategory:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>Query Plan 1 (takes 90+ seconds)</p>
  
  <p><img src="https://i.stack.imgur.com/UVyX2.png" alt="Query Plan 1 (takes 90+ seconds)"></p>
  
  <p>Query Plan 2 (takes ~3 seconds)</p>
  
  <p><img src="https://i.stack.imgur.com/hIy6O.png" alt="Query Plan 2 (takes ~3 seconds)"></p>