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][1]:

(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] IDENTITY(1,1) NOT NULL,
    	[MessageID] [bigint] NOT NULL,
    	[MessagePlatform] [nvarchar](50) NULL,
    	[AssetNumber] [nvarchar](50) NOT NULL,
    	[MessageState] [int] NULL,
    	[MessageStateLabel] [nvarchar](50) NULL,
    	[MessageType] [int] NULL,
    	[MessageTypeLabel] [nvarchar](50) NULL,
    	[MessageCategory] [nvarchar](50) NULL,
    	[MessageSource] [int] NULL,
    	[MessageSourceLabel] [nvarchar](50) NULL,
    	[MessageSourceSerialNumber] [nvarchar](50) NULL,
    	[MessageCreateDate] [datetime] NULL,
    	[MessageTransmitDate] [datetime] NULL,
    	[MessageReceivedDate] [datetime] NULL,
    	[MessageStoredDate] [datetime] NULL,
    	[XMLPayload] [nvarchar](max) NULL,
    	[JSONPayload] [nvarchar](max) NULL,
    	[SemanticXML] [nvarchar](max) NULL,
    	[SemanticJSON] [nvarchar](max) NULL,
    	[MessageSequenceNumber] [int] NULL,
    	[ERPImportDate] [datetime] NULL,
    	[ERPImportStatus] [int] NULL,
    	[ERPMsg] [nvarchar](max) NULL,
    	[NormalizationDate] [datetime] NULL,
    	[NormalizationStatus] [int] NULL,
    	[NormalizationDesc] [nvarchar](max) NULL,
    	[SemanticDate] [datetime] NULL,
    	[SemanticStatus] [int] NULL,
    	[SemanticDesc] [nvarchar](max) NULL,
    	[CreatedDate] [datetime] NOT NULL DEFAULT (getdate()),
    	[CreatedBy] [nvarchar](50) NOT NULL DEFAULT ('ETL'),
    	[UpdatedDate] [datetime] NOT NULL DEFAULT (getdate()),
    	[UpdatedBy] [nvarchar](50) NOT NULL DEFAULT ('ETL'),
    	[ETL_ID] [uniqueidentifier] NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[MessageQueueId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
     CONSTRAINT [CK_ETL_Unique_MessageID_Platform] UNIQUE NONCLUSTERED 
    (
    	[MessageID] ASC,
    	[MessagePlatform] ASC,
    	[MessageType] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO

This should provide you with enough code to reproduce the problem ... just fill the table with about 11 million records and you can see the problem!

---

Since it was brought up a couple of times, and I had not even thought to check, I looked to see how many 'windows' records there were versus 'linux' records.

    SELECT COUNT(*) 
    FROM BoothComm.UniversalMessageQueue 
    WHERE 
    	MessageCategory = 'Accounting'
    	AND
    	MessagePlatform = 'linux';
    -- returned 1762461
    
    SELECT COUNT(*) 
    FROM BoothComm.UniversalMessageQueue 
    WHERE 
    	MessageCategory = 'Accounting'
    	AND
    	MessagePlatform = 'windows';
    -- returned 11786

So ... I am guessing that recordcount is not the issue?


  [1]: https://www.brentozar.com/pastetheplan/?id=r1rVxaCqZ