21

I've been reading about SQL Server internals and every book or blog mentions this about backward scans.

A backward scan of a clustered index cannot use parallelism

The only post that said something is this one below. The post says that the SQL Server team did not implement the required optimizations for a backward scan. https://www.itprotoday.com/sql-server/descending-indexes

Since the leaf level pages are linked using a doubly linked list, I do not understand why a backward scan is different to a forward scan. Any clarification is really appreciated.

18

The referenced article specifically states the reason backward ordered scans were not parallelized in SQL Server 2008 (as of CU6) isn't technical but because the feature hadn't been requested by customers and the dev team didn't bother to implement it.

Note that the article was written nearly 10 years ago in the context of the now unsupported SQL Server 2008 version. There have been significant changes to the storage engine and optimizer. That said, I still see a parallel plan for the ASC query and a serial plan for the DESC version from the article's demo query on SQL Server 2017:

SELECT *
FROM dbo.Orders
WHERE orderid <= 100000
ORDER BY orderdate ASC;

SELECT *
FROM dbo.Orders
WHERE orderid <= 100000
ORDER BY orderdate DESC;

Running those same queries under SQL 2019 CTP 3.2 shows a serial plan for both unless I changed the query to WHERE orderid <= 50000, where I then observed the same behavior as SQL Server 2017. So it seems that either the parallel backwards scan has still not yet been implemented or a different scenario is needed to observe it.

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.