Exact same query - Different performance

Database : SQL Server 12.0.5207

I have several queries which are exactly the same in all ways except for the value of one of the filter terms. Same table (not a copy of the schema on another server), therefor same indexes, resources, etc. Everything is absolutely IDENTICAL.

This query runs in under one second:

SELECT 
         MAX(MessageID) AS [MaxID]
FROM BoothComm.UniversalMessageQueue
WHERE 
       MessagePlatform = 'linux'

This query runs in under one second:

SELECT 
         MAX(MessageID) AS [MaxID]
FROM BoothComm.UniversalMessageQueue
WHERE 
       MessagePlatform = 'linux'
       AND
       MessageCategory = 'accounting'

This query runs in under one second:

SELECT 
         MAX(MessageID) AS [MaxID]
FROM BoothComm.UniversalMessageQueue
WHERE 
       MessagePlatform = 'windows'

So why does THIS one take nearly 30 seconds to run?

SELECT 
         MAX(MessageID) AS [MaxID]
FROM BoothComm.UniversalMessageQueue
WHERE 
       MessagePlatform = 'windows'
       AND
       MessageCategory = 'accounting'

A colleague of mine added another index to the table which resolved the business problem of latency. This index reduced 30 seconds to a FULL second while speeding the other queries up to instantaneous. Again, execution plans are exactly the same:

(Index Scan should be 100%). I have taken advice from other forums and made sure that the column order in the query matched the order that they are stored in the index...

CREATE NONCLUSTERED INDEX [MessageID and Platform and Category] ON [BoothComm].[UniversalMessageQueue]
(
    [MessageID] ASC,
    [MessagePlatform] ASC,
    [MessageCategory] 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

I am also providing the table schema in the event that that is helpful.

```
CREATE TABLE [BoothComm].[UniversalMessageQueue](
[MessageQueueId] [bigint]

Right now you are getting an index scan because all of the columns needed are in the index and it's faster than scanning the table. However, it has to scan, not seek, because the first column on the index isn't in the WHERE statement and doesn't limit the return. You need to rearrange the index columns so that MessagePlatform is first, since it's always in your WHERE statement.

Depending on the the size of your data and insert speed needed, you may want to consider two indexes. If you only want one index, I'd go with the following:

CREATE NONCLUSTERED INDEX [MessageID and Platform and Category] 
ON [BoothComm].[UniversalMessageQueue]
(
    [MessagePlatform] ASC,
    [MessageID] ASC,
    [MessageCategory] 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

But if you can afford two I'd move to:

CREATE NONCLUSTERED INDEX [MessageID and Platform] 
ON [BoothComm].[UniversalMessageQueue]
(
    [MessagePlatform] ASC,
    [MessageID] 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

CREATE NONCLUSTERED INDEX [MessageID and Platform and Category] 
ON [BoothComm].[UniversalMessageQueue]
(
    [MessagePlatform] ASC
    [MessageCategory] ASC,
    [MessageID] 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

Which index it uses will depend on if MessageCategory is in your WHERE statement.

There are more Linux records than Windows records, by a lot (1,762,461 vs 11,786). The way the index is now, the index scan starts with the largest MessageID and moves down the list until it finds a matching MessagePlatform. Since there are many more records that are Linux it will hit one pretty quickly. Since there are a lot less that are Windows it has to scan much further, taking longer.

StackExchange Database Administrators Q#186339, answer score: 8