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?
Best Answer
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