2

While i was reading and per understanding one needs to avoid unwanted ORDER BY in SQL queries if matching index to support query has key columns sorted the same way.

For below DB test schema--

CREATE PARTITION FUNCTION DemoPartitionFunction (datetime)
AS RANGE RIGHT
FOR VALUES (DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -7),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -6),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -5),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -4),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -3),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -2),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -1),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 0),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 1),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 2),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 3),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 4),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 5),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 6),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 7));

CREATE PARTITION SCHEME DemoPartitionScheme
AS PARTITION DemoPartitionFunction
ALL TO ([DEFAULT]);

CREATE TABLE [dbo].[DemoPartitionedTable](
    [DemoID] [int] IDENTITY(1,1) NOT NULL,
    [SomeData] [sysname] NOT NULL,
    [Lastseen] [datetime] NULL,
    [DataKey1] [char] NOT NULL,
    [DataKey2] [char] NOT NULL,
    [RandomColumn] [nvarchar] NOT NULL,
    [CaptureDate] [datetime] NULL,
    CONSTRAINT [PK_DemoPartitionedTable] UNIQUE NONCLUSTERED 
    (
        [DemoID] ASC,
        [CaptureDate] ASC
    )
    ON DemoPartitionScheme(CaptureDate)
) ON DemoPartitionScheme(CaptureDate);

If i run the Query with ORDER BY

SELECT [DemoID], [CaptureDate]
FROM [dbo].[DemoPartitionedTable]
WHERE CaptureDate>=CONVERT(datetime, '20190912', 112) AND
CaptureDate < CONVERT(datetime, '20191013', 112)
ORDER BY 
DemoID,
CaptureDate

Below are the stats from IO and Time

(95703 rows affected) Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 117, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'DemoPartitionedTable'. Scan count 16, logical reads 330, physical reads 9, read-ahead reads 262, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

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

enter image description here

As per my understanding in above scenario we do not need order by as index have those columns already sorted, so why do i see a bad plan for below

so if i run removing the sorting above

SELECT [DemoID], [CaptureDate]
FROM 
[dbo].[DemoPartitionedTable]
WHERE CaptureDate>=CONVERT(datetime, '20190912', 112) AND
CaptureDate < CONVERT(datetime, '20191013', 112)
--ORDER BY 
--DemoID,
--CaptureDate

(95703 rows affected) Table 'DemoPartitionedTable'. Scan count 16, logical reads 330, physical reads 134, read-ahead reads 269, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

SQL Server Execution Times: CPU time = 78 ms, elapsed time = 2791 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

enter image description here

Plan does not have an expensive sort but stats got worst, why?

1 Answer 1

5

As per my understanding in above scenario we do not need order by as index have those columns already sorted

That understanding is incorrect, and that plan shows one reason why. A parallel index scan doesn't output rows in index order, as each thread reads at a different location in the sort order. You can't expect rows in any particular order without an ORDER BY clause.

And the index does not have the rows already sorted. The ORDER BY is on (DemoID, CaptureDate), but the index is partitioned by CaptureDate. So if the query crosses a partition bondary, the DemoID values will start over.

eg the rows in (DemoID, CaptureDate)-order:

(1,'20190912'),(2,'20190913'),(3,'20190912'),(4,'20190913')

may be stored over more than one partition:

partition N
--------------
(1,'20190912')
(3,'20190912')

partition N+1
--------------
(2,'20190913')
(4,'20190913')

So even if the plan used a single thread to scan the index, a downstream sort would be required.

Plan does not have an expensive sort but stats got worst, why?

No. The stats got better. The second query has the same 330 logical reads and only 78ms of CPU time vs 297ms of CPU time for the first query. The difference in elapsed time is related to more parallelism and the physical IO, which is not an attribute of the query plan. Rather it's dependent on the state of the page cache when you run the query.

7
  • Browne, thanks for the answer. But now i am sort of more confused. Then what is the case where its applicable to not use order by if i have indexes created for same columns used in sorting? When i compare the results both were sorted in asc for demo id, so i thought they actually got ordered even after removing order by. Moreover i said worst thinking 3 sec vs 1sec. Oct 9, 2019 at 1:17
  • @BeginnerDBA, order by is required when you need results in a particular order. If you happen to get data back in the desired order, it is by happenstance and not guaranteed.
    – Dan Guzman
    Oct 9, 2019 at 1:21
  • @DanGuzman: Thanks for the link. Then what would be the best bet if one wants to keep Order by in query and get rid of sort operator to enhance the query performance/ Oct 9, 2019 at 2:17
  • @BeginnerDBA You can't. if you want an order by - you are asking for a sort. Period. A relational database is, still, a relational database. Sorting is a foreign concept to a relational database. You are asking it to sort and spit out the output in that order. If you care about avoiding the sort, but care about order, do your sorting where it is likely more efficient - in your app/code. Or just deal with the sort. It's clearly not killing you here. Perhaps an index that leads with CaptureDate may help, or perhaps (depending on a lot ) be a better clustered key.
    – Mike Walsh
    Oct 9, 2019 at 2:59
  • 2
    @BeginnerDBA, it's not always possible (or should I say more efficient) to eliminate the sort operator. It may be more efficient to use an index seek to find a few rows and then sort them than using a sorted index that has to scan every row in the table.
    – Dan Guzman
    Oct 9, 2019 at 3:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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