4 added 14 characters in body
source | link

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=r1rVxaCqZexecution plans are exactly the same:

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

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:

3 deleted 112 characters in body; edited tags
source | link

This query runs in under one second:

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 … 

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 … 

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?

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 ... :

UPDATE

 

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

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 ...

UPDATE

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

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:

 

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

    Tweeted twitter.com/StackDBAs/status/910453981834604544
2 Updated to record further actions taken
source | link

UPDATE

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

UPDATE

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
source | link