Why You Don’t Get Parallelism When You Ask for The Whole Table

Let’s say you’re working with a big copy of the Stack Overflow database, and you wanna get all the users, like for a data export. SQL Server doesn’t parallelize this query, not even if you use the query hint that asks for a parallel plan:

I’m only asking for the Ids here because in the big copies of the Stack Overflow database, the Users table is over 1GB. It takes a looong time for SQL Server Management Studio to paint result grids with 1GB of data, and demonstrating a slow SSMS isn’t the point here. If you’re going to follow along with me in this demo, make sure you’re using a current version of SQL Server Management Studio, because I don’t want these demos to run your machine out of memory, which old versions of SSMS would do.

 

So, why isn’t it going parallel? The short answer is that SQL Server knows it has to shove all the millions of rows of results out the network, through a single stream going out to the end user’s app (in this case, SSMS). It doesn’t have the ability to send multiple network streams of data down to the client, and furthermore, even if it did, our queries often ask for sorted data, like ORDER BY something. In that case, we need to deliver the results in order – something that isn’t really doable with multi-threaded result sets.

But … how could we force SQL Server into using multiple threads to gather the data, then consolidate the data into a single set going out to the network, just to demonstrate that parallelism on this query would be a bad idea?

To do that, we need to trick SQL Server into thinking that our query has a selective filter that will only return a limited amount of rows, when in reality, it’s still returning all of the rows.

We’ll take advantage of the fact that SQL Server doesn’t have statistics on the output of mathematical formulas, and doesn’t put the slightest bit of thought into what that output could be:

Now, you and I know that if we take any number, and subtract itself, the result will be zero. SQL Server, as smart as it is, does not know that, and it thinks that the filter will be quite selective. Check out the execution plan:

SQL Server estimates that only ONE ROW will come back! Isn’t that awesome? I love these little edge-case hard-coded rules inside the query optimizer. I really wish there was a yellow bang over any query operator that had a hard-coded cardinality estimation, because clearly that estimate of 1 row isn’t based on any kind of advanced heuristic, heh.

So because SQL Server thinks our filter will be so selective, it believes few rows will need to be sent back over the network, and as a result, introduces parallelism into the plan to read all that data.

Let’s compare the actual plans and their statistics for the single-threaded versus parallel plan. Here’s what SET STATISTICS TIME reports:

• Parallel: CPU time = 16297 ms, elapsed time = 46267 ms
• Single-threaded: CPU time = 5703 ms, elapsed time = 43103 ms

The parallel version used about 3X more CPU time. Granted, an unknown chunk of that was caused by subtracting a number from itself, but SQL Server also had to recombine the data across multiple parallel threads in order to ship the data out via a single threaded network stream. (We’re not even sorting the output here.) The parallel query didn’t actually finish any faster, because it takes forever to shove all that data out the network, and have SSMS consume the results, painting them into a grid.

However, here’s where stuff really goes off the rails. Check out the actual query plans, and note the times on their operators:

The parallel plan LOOKS faster – it looks like it only took a few seconds, when the single-threaded plan took a lot more seconds. However, these times are only how long it took for SQL Server to do its work – not how long it took the data to reach the client. To see client delivery time, you’ll need to turn on SET STATISTICS TIME ON to get the numbers I mentioned earlier.

Another way you can see that the parallel plan isn’t “better” in any meaningful sense is to look at their wait statistics. Right-click on each select operator, click Properties, and go down to the wait stats section.

• Parallel plan: 171 seconds of CXPACKET waits, 43 seconds of ASYNC_NETWORK_IO waits
• Single-threaded: 37 seconds of ASYNC_NETWORK_IO waits

So the parallel plan doesn’t run any faster, burns more CPU time, and produces more parallelism overhead. That’s why, when you don’t have a truly selective filter in the where clause, and you’re bringing back a lot of data, introducing parallelism doesn’t speed things up – it makes them worse.

What if we’re not returning the rows?

What if we’re pumping them into a temp table, something that should allow for parallelism?

Now, the situation is different: both query plans go parallel.

 

We don’t need any trickery – even the plain, unfiltered query gets parallelism.

• Tricky WHERE clause: CPU time = 25391 ms, elapsed time = 7834 ms
• No WHERE clause: CPU time = 17703 ms, elapsed time = 5674 ms

This further drives home the fact that when SQL Server needs to pipe that data out via a single-threaded stream of data, it doesn’t make sense to get parallelism involved – but if the data target is something that can handle multiple threads of streamed output at once, then it does.

This post was inspired by Louis Tochon’s post about this same topic, which I was just giddy to read because this is one of my favorite niche topics. This knowledge has never served me any real purpose in client work, since it’s something that SQL Server already does perfectly well and shouldn’t be mucked with, but it’s really fun to muck with and understand why it does things the way it does. I can imagine a situation where this knowledge might come in handy – when SQL Server lowballs an estimate and goes parallel for the wrong reasons – but I’ve never hit this particular edge case in real life. Someday! Fingers crossed, and now someday this knowledge will help you too.