3 of 5 deleted 112 characters in body; edited tags

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:

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

(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?