In this lesson, you will learn why adding more CPU power to a database scan doesn't always make your query run faster and where that performance "ceiling" actually sits.
The Parallelism Paradox
When a database performs a large scan—like reading millions of rows to calculate an aggregate—it can split the work across multiple CPU cores. This is called a Parallel Scan. In theory, if you have 8 cores, your query should run 8 times faster. In practice, however, performance often plateaus or even degrades as you approach that limit.
The primary culprit is CPU Context Switching.
When a database engine manages parallel threads, it must constantly coordinate tasks, manage memory buffers shared between threads, and synchronize access to data pages. As you increase the number of worker threads to scan a table, the CPU spends more time "context switching"—the overhead of saving the state of one thread and loading another—than it does actually processing the query rows.
The Threshold of Diminishing Returns
Imagine you have a single library book (your data) and you want to read it as fast as possible.
- One reader: Very efficient, no overhead.
- Two readers: Faster, they divide the chapters.
- Fifty readers: You now have fifty people standing in the same aisle, bumping into each other, waiting for their turn to look at a page, and arguing over who has the bookmark.
In SQL, this manifests when the MAXDOP (Maximum Degree of Parallelism) setting is tuned too high. If the cost of coordinating 32 threads outweighs the time saved on the actual scan, the query duration increases.
Identifying the Plateau
If you are running a query like this on a massive table:
SELECT SUM(total_sales)
FROM Sales_Archive
WHERE transaction_date > '2020-01-01';
If your server has 16 physical cores, you might notice that performance is nearly identical between a MAXDOP setting of 8 and 16. Beyond 8, the overhead of the operating system managing those threads consumes the gains of the parallel processing.
Key Takeaway: Parallel scanning efficiency plateaus when the overhead of CPU context switching and thread synchronization exceeds the time saved by dividing the data processing workload.