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