5

I have a table which has a clustered index as below:

CREATE CLUSTERED INDEX [IX_MachineryId_DateRecorded]
ON Machinery (MachineryId, DateRecorded)

I'm selecting based on the fields in the clustered index, plus one more:

SELECT DateRecorded, Latitude, Longitude
FROM MachineryReading
WHERE MachineryId = 2127        -- First key in the index
AND DateRecorded > '2017-01-10' -- Second key in the index
AND DateRecorded < '2017-10-16' -- Second key in the index
AND FixStatus >= 2              -- Not a key, resulting in a scan
ORDER BY DateRecorded

I would have expected this to result in a simple clustered index scan. However, looking at the live query statistics, and the actual execution plan, the majority of the query's execution time comes from sorting the results after the index scan. Why is the ordered data being sorted again?

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

  • 4
    Maybe, because your table is partitioned and partitions do not align with the index. The plan has this in Seek Predicates: Seek Keys[1]: Start: PtnId1000 >= Scalar Operator((26)), End: PtnId1000 <= Scalar Operator((35)). If your table is partitioned, please include relevant details about it in the question. – Vladimir Baranov Oct 16 '17 at 4:18
  • I was not aware of partitioning on this table. I'll have to look into that tomorrow and update the question (although if there is partitioning on another field, it would explain why a sort is necessary) – Andrew Williamson Oct 16 '17 at 8:40
6

Your query accesses 10 partitions and you are searching a 10 month range so my guess would be that it is partitioned on month of DateRecorded.

I can reproduce your plan with the sort with the below.

CREATE PARTITION FUNCTION pf1 (DATE) AS RANGE RIGHT FOR VALUES ( 
'2017-01-01', 
'2017-02-01', 
'2017-03-01', 
'2017-04-01', 
'2017-05-01', 
'2017-06-01', 
'2017-07-01', 
'2017-08-01', 
'2017-09-01', 
'2017-10-01', 
'2017-11-01' );

CREATE PARTITION SCHEME ps1 AS PARTITION pf1 ALL TO ([Primary]);

CREATE TABLE MachineryReading
  (
     MachineryId  INT,
     DateRecorded DATE,
     Latitude     FLOAT,
     Longitude    FLOAT,
     FixStatus    INT
  )
ON ps1(DateRecorded)

CREATE CLUSTERED INDEX [IX_MachineryId_DateRecorded]
  ON MachineryReading (MachineryId, DateRecorded)

but technically a sort could be avoided if you could get a plan that processed the partitions in order and just concatenated one ordered result to the next.

If you are happy to assume that the partition numbers will be in order of value (I don't know if this is actually guaranteed but it seems to be the case even after partition splits) then adding a leading column to the sort of the partition number achieves this

SELECT DateRecorded,
       Latitude,
       Longitude
FROM   MachineryReading
WHERE  MachineryId = 2127 
       AND DateRecorded > '2017-01-10' 
       AND DateRecorded < '2017-10-16' 
       AND FixStatus >= 2 
ORDER  BY $PARTITION.pf1(DateRecorded),
          MachineryId, --Not really needed as this is a constant 2127
          DateRecorded 

enter image description here

  • 1
    Yes, this is what we have. Unfortunately the senior developer that set this up never documented it before he left. I'm glad I found this, because the partition function is only set up to go to January 2018... – Andrew Williamson Oct 16 '17 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.