4

Is there something inherently unparallelisable about the table spool operator in SQL Server? I've got several queries that use parallel operators throughout due to the size of the tables, but by tweaking the query slightly to add a table spool (eg adding a second aggregate to the SELECT clause) the entire query suddenly becomes single-threaded.

Is it the table spool that is causing this, or could there be some other reason why SQL Server isn't parallelising operations that it was previously?

1

I am assume you are meaning common sub expression spools here (i.e. calc once, used many). If so then I would expect this behaviour.

A common sub expression spool is effectively a temp table (its a bit more sophisticated than that but you get the idea). The complex action is completed once and then stored to be re-used many times. If you think about it like this then hopefully you can see why it would be breaking parallelism as the "temp" table can't be read by multiple threads simultaneously due to locking etc.

Adam Machanic has a lot of great info on the things that break parallelism from his SQLBits X session:

https://sqlbits.com/Sessions/Event10/Query_Tuning_Mastery_The_Art_of_Manhandling_Parallelism

  • 1
    The reads can't happen in parallel, even though the temp table is written to once and then only read from? – thecoop Apr 23 '12 at 17:08
  • technically you may be correct that it could work reading in parallel however I know that, even in sequential plans spools can only be read from one at a time (i.e. if there are 5 occurrences of a spool being used in a plan they have to be done in one at a time and cannot be read in parallel). I'll be honest, whilst I know query plans quite well I'm not a massive expert on parallelism but I'm sure that spools are one of the operators that break parallelism. Have a look at Adams stuff I linked earlier for more info – user8369 Apr 23 '12 at 21:08

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.