Exact same query - Different performance

Question

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?

Answer 1

Just to offer a couple additional indexing strategies, here's what I came up with.

I'm not sure about the rest of the data distribution in your table. I know that Accounting/Linux as a combo makes up 1,762,461 rows, and Accounting/Windows makes up 11,786 rows. Assuming there are other departments and platforms, I stuck this data in a table.

USE tempdb;

CREATE TABLE dbo.Whatever
(
    Id INT IDENTITY(1,1),
    MessageId INT NOT NULL,
    MessageCategory NVARCHAR(50),
    MessagePlatform NVARCHAR(50)
);

INSERT dbo.Whatever ( MessageId, MessageCategory, MessagePlatform )
SELECT TOP 1762461 x.n, 'Accounting', 'Linux'
FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS n
        FROM sys.messages AS m
        CROSS JOIN sys.messages AS m2) AS x

INSERT dbo.Whatever ( MessageId, MessageCategory, MessagePlatform )
SELECT TOP 11786 x.n, 'Accounting', 'Windows'
FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS n
        FROM sys.messages AS m
        CROSS JOIN sys.messages AS m2) AS x


INSERT dbo.Whatever ( MessageId, MessageCategory, MessagePlatform )
SELECT TOP 5000000 x.n, 'HR', 'Unix'
FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS n
        FROM sys.messages AS m
        CROSS JOIN sys.messages AS m2) AS x


INSERT dbo.Whatever ( MessageId, MessageCategory, MessagePlatform )
SELECT TOP 1000000 x.n, 'Accounting', 'Mac'
FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS n
        FROM sys.messages AS m
        CROSS JOIN sys.messages AS m2) AS x

INSERT dbo.Whatever ( MessageId, MessageCategory, MessagePlatform )
SELECT TOP 1000000 x.n, 'IT', 'Windows'
FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS n
        FROM sys.messages AS m
        CROSS JOIN sys.messages AS m2) AS x

INSERT dbo.Whatever ( MessageId, MessageCategory, MessagePlatform )
SELECT TOP 1000000 x.n, 'IT', 'Linux'
FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS n
        FROM sys.messages AS m
        CROSS JOIN sys.messages AS m2) AS x

INSERT dbo.Whatever ( MessageId, MessageCategory, MessagePlatform )
SELECT TOP 1000000 x.n, 'HR', 'Mac'
FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS n
        FROM sys.messages AS m
        CROSS JOIN sys.messages AS m2) AS x

INSERT dbo.Whatever ( MessageId, MessageCategory, MessagePlatform )
SELECT TOP 1000000 x.n, 'HR', 'Windows'
FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS n
        FROM sys.messages AS m
        CROSS JOIN sys.messages AS m2) AS x

ALTER TABLE dbo.Whatever ADD CONSTRAINT pk_thoughtful PRIMARY KEY CLUSTERED (Id)

First strategy: Since you're querying for a MAX(), it may make more sense to order the MessageId column DESC in the index. This may allow you to avoid an unwanted Sort in the plan.

CREATE INDEX ix_love_and_rockets
    ON dbo.Whatever
(
    MessagePlatform,
    MessageId DESC,
    MessageCategory );

Second Strategy: If you care the most about the MessagePlatforms Windows and Linux, a filtered index on those values may make sense. I'm gonna stick with the descending ordering of MessageId.

CREATE INDEX ix_bauhaus_was_better
    ON dbo.Whatever
(
    MessageCategory,
    MessageId DESC )
    INCLUDE ( MessagePlatform )
    WHERE MessagePlatform IN ( 'Windows', 'Linux' );

Results: When running a few of your example queries, the index usage is mixed.

SELECT MAX(w.MessageId) AS MaxId
FROM   dbo.Whatever AS w
WHERE  w.MessagePlatform = 'Linux'
       AND w.MessageCategory = 'Accounting'
       AND 1 = ( SELECT 1 );

SELECT MAX(w.MessageId) AS MaxId
FROM   dbo.Whatever AS w
WHERE  w.MessagePlatform = 'Windows'
       AND w.MessageCategory = 'Accounting'
       AND 1 = ( SELECT 1 );

SELECT MAX(w.MessageId) AS MaxId
FROM   dbo.Whatever AS w
WHERE  w.MessagePlatform = 'Windows'
       AND 1 = ( SELECT 1 );

SELECT MAX(w.MessageId) AS MaxId
FROM   dbo.Whatever AS w
WHERE  w.MessagePlatform = 'Linux'
       AND 1 = ( SELECT 1 );

Here's the stats time and I/O results:

• Query 1

  SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table 'Whatever'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

• Query 2

  SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table 'Whatever'. Scan count 1, logical reads 10938, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times: CPU time = 625 ms, elapsed time = 614 ms.

• Query 3

  SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table 'Whatever'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

• Query 4

  SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table 'Whatever'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

I blogged a little bit about both filtered and descending indexes here, if you'd like a little more information.

Why does Query 2 take longer? The answer to that is, "The alphabet."

If you create the index with MessagePlatform in DESC order, the order will flip.

Consider just these two indexes:

CREATE INDEX ix_love_and_rockets
    ON dbo.Whatever
(
    MessagePlatform,
    MessageId DESC,
    MessageCategory );


CREATE INDEX ix_rockets_and_love
    ON dbo.Whatever
(
    MessagePlatform DESC,
    MessageId DESC,
    MessageCategory );

We're flipping the ordering of MessagePlatform. Now if we run the same two queries hinting those indexes, the perf difference will not flip.

SELECT MAX(w.MessageId) AS MaxId
FROM   dbo.Whatever AS w WITH (INDEX = ix_love_and_rockets)
WHERE  w.MessagePlatform = 'Linux'
       AND w.MessageCategory = 'Accounting'
       AND 1 = ( SELECT 1 );

SELECT MAX(w.MessageId) AS MaxId
FROM   dbo.Whatever AS w WITH (INDEX = ix_love_and_rockets)
WHERE  w.MessagePlatform = 'Windows'
       AND w.MessageCategory = 'Accounting'
       AND 1 = ( SELECT 1 );

SELECT MAX(w.MessageId) AS MaxId
FROM   dbo.Whatever AS w WITH (INDEX = ix_rockets_and_love)
WHERE  w.MessagePlatform = 'Linux'
       AND w.MessageCategory = 'Accounting'
       AND 1 = ( SELECT 1 );

SELECT MAX(w.MessageId) AS MaxId
FROM   dbo.Whatever AS w WITH (INDEX = ix_rockets_and_love)
WHERE  w.MessagePlatform = 'Windows'
       AND w.MessageCategory = 'Accounting'
       AND 1 = ( SELECT 1 );

• Query 1

  Table 'Whatever'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

• Query 2

  Table 'Whatever'. Scan count 1, logical reads 9338, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times: CPU time = 687 ms, elapsed time = 684 ms.

• Query 3

  Table 'Whatever'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

• Query 4

  SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table 'Whatever'. Scan count 1, logical reads 9337, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times: CPU time = 672 ms, elapsed time = 678 ms.

Root cause: in both queries, we seek to the MessagePlatform, but then we have a residual predicate on MessageCategory.

The difference is in data distribution. In my test data, Windows has a lot more additional rows for MessageCategory to filter out than Linux does — specifically 1mm more rows for the Windows/HR combo.

Hope this helps!

Answer 2

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.