title: Query Performance in SQL Server
body: <p>I have a SQL Server table with over 11 million records. These records are organized by "Category" and "Platform". I am stumped by the following scenario ... </p>
  
  <pre><code>SELECT COUNT(*) FROM TableName WHERE Category = 'session' AND Platform = 'windows';
  -- Returns 1261500
  
  SELECT COUNT(*) FROM TableName WHERE Category = 'session' AND Platform = 'linux';
  -- Returns 1890599
  </code></pre>
  
  <p>So there are over 600K more records associated with 'linux' than 'windows'.</p>
  
  <p>However, this query returns in 6-9 seconds ... </p>
  
  <pre><code>SELECT MAX(id) FROM TableName WHERE Category = 'session' AND Platform = 'linux';
  </code></pre>
  
  <p>Yet this one I have to kill after waiting over 13 minutes for a result ...</p>
  
  <pre><code>SELECT MAX(id) FROM TableName WHERE Category = 'session' AND Platform = 'windows';
  </code></pre>
  
  <p>Oh ... I also have the following index on the table ... </p>
  
  <pre><code>CREATE NONCLUSTERED INDEX [IX_TableName_CategoryPlatform] ON [dbo].[TableName]
  (
      [Platform] ASC,
      [Category] ASC,
      [CreateDate] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  GO
  </code></pre>
  
  <p>Whiskey, Tango, Foxtrot?</p>
  
  <p>Why does the search term make a difference, particularly since there is an index in place?</p>
  
  <h2><strong>UPDATE</strong></h2>
  
  <p>I have just made the following observation ... </p>
  
  <pre><code>SELECT MAX(id) FROM TableName WHERE Platform = 'windows';
  </code></pre>
  
  <p>By dropping the <em>Category</em> from the query, the response is returned very quickly ...</p>
  
  <h2>UPDATE 2</h2>
  
  <p>I have created a couple of execution plans as requested. The thing I noticed, however is that the percentages in the plans generated by the "Paste The Plan" utility and what I am getting in SSMS appear to be different so I am including, below each link, the percentages that I am seeing in Management Studio. </p>
  
  <p>For the following Query (which works) ... </p>
  
  <pre><code>SELECT MAX([MessageID]) [MaxID] FROM [BoothComm].[UniversalMessageQueue] WHERE [MessagePlatform]='windows';
  </code></pre>
  
  <p><a href="https://www.brentozar.com/pastetheplan/?id=Sk9q59CqZ" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=Sk9q59CqZ</a></p>
  
  <ul>
  <li>0% : Select</li>
  <li>0% : Stream Aggregate</li>
  <li>0% : Top</li>
  <li>100% : Index Scan</li>
  </ul>
  
  <p>The next query (which doesn't work) I can only provide an ESTIMATED execution plan.</p>
  
  <pre><code>SELECT 
     MAX(MessageID) AS [MaxID]
  FROM BoothComm.UniversalMessageQueue
  WHERE 
   MessageCategory = 'session'
   AND
   MessagePlatform = 'windows'
  </code></pre>
  
  <p><a href="https://www.brentozar.com/pastetheplan/?id=r1zqnq09-" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=r1zqnq09-</a></p>
  
  <ul>
  <li>0% : Select</li>
  <li>0% : Stream Aggregate</li>
  <li>0% : Top</li>
  <li>0% : Nested Loops (Inner Join) -- Why is this there??</li>
  <li>21% : Index Scan</li>
  <li>79% : Key Lookup -- Also new and seems to want to take up more time than anything else</li>
  </ul>
  
  <p>(thanks for all the help!)</p>
  
  <h2><strong>UPDATE 3</strong></h2>
  
  <p>So after all of the below conversation and changes made I am still left with the question ...</p>
  
  <p>Why does this query return in under 1 second (thanks to adding the ID to the index) ... </p>
  
  <pre><code>SELECT 
        MAX(MessageID) AS [MaxID]
  FROM BoothComm.UniversalMessageQueue
  WHERE 
      MessagePlatform = 'linux'
      AND
      MessageCategory = 'accounting'
  </code></pre>
  
  <p>And this one take 13 -22 seconds to run ... </p>
  
  <pre><code>SELECT 
        MAX(MessageID) AS [MaxID]
  FROM BoothComm.UniversalMessageQueue
  WHERE 
      MessagePlatform = 'windows'
      AND
      MessageCategory = 'accounting'
  </code></pre>
  
  <p>Same table, same indexes, execution plans are the absolute same. Everything is identical except for the <strong>MessagePlatform</strong> value. And the value which is responsible for the latency appears on fewer records than the other. </p>