Why Parallelism Increases the Logical Reads

Question

I'm using StackOverflow2010 10GB version from this link: https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent/

Currently, I only have 1 clustered index in the Users table.

I found that when I select Id from Users table, it went single-threaded, it consumed 7405 logical pages.

SELECT Id
FROM Users;

Execution Plan: https://www.brentozar.com/pastetheplan/?id=ByMAsHjUi

However, if I added 1 WHERE clause which filtered LastAccessDate, it was parallel and logical reads went up to 7778 logical reads.

SELECT Id
FROM Users
WHERE LastAccessDate > '20150101';

Execution Plan: https://www.brentozar.com/pastetheplan/?id=Syg4nBjUo

What I think is, if both queries are going to SELECT Id and using clustered index only, the logical reads should be the same, but why it is different now?

Answer 1

When you look into logical reads distribution across multiple threads, if you add up all the numbers, you will get 7,778 (Right-click on "Clustered Index Scan" operator, click "Properties", look for "Actual Logical Reads")

Actual Logical Reads	7778
Thread 0	55
Thread 1	2402
Thread 2	2452
Thread 3	2340
Thread 4	529

Thread 0 is used in synchronization of the other threads.

Thread 0 will just give different 8K pages to different threads to process. It does not do actual data fetching so it has Actual Rows always set to zero. It may gives more pages to one thread compared to another (that's why you see the distribution is not even in above table). Different threads may get same pages as well.

The "Parallelism" operator is showing "(Gather Streams)" actually shows quite clearly what it does, it will gather all the worker threads and their output data as the result.