Forcing Parallelism (TF 8649)
Published: 8th September 2013
In this first episode of Sheldon Cooper’s Fun with Flags I’m going to be looking at my favourite flag of the moment… Trace Flag 8649.

And yes, before anyone points it out, I know having a favourite trace flag is infinitely more disturbing than having a favourite real life flag but I’ve learnt to live with it and you should too.

So why is it my favourite? Well, basically it causes the SQL Server optimizer to attempt to provide you with a parallel plan for your queries. It’s that simple.

Now, I’m not saying that this should ever be used in production (mainly because it’s undocumented and therefore likely unsupported), or that parallel plans are always the best choice for a query, but when they are a good choice but the optimizer won’t provide you with one then this can be a great method to establish whether a parallel plan would actually be better, or in fact possible, and whether you should therefore try to coax one out of the optimizer.

First things first, I’m going to be using the AdventureWorks2012 database, but this is utterly useless for parallelism demos as it’s simply too small. Therefore I’m using an artificially enlarged version which I created using Jonathan Kehayais’ script which you can find here.

So, there are times in which I’ve found a serial plan being provided by the optimizer because it believes that a parallel plan would be too expensive. However, on a few occasions this has turned out not to be the case. But how do we know this when we can’t force a parallel plan to be chosen?

Here’s a simple example of a query which my optimizer has determined should be serial (correctly as it happens, but this is just for demonstration purposes)…

select *
from sales.SalesOrderHeaderEnlarged
where orderDate = '2010-07-01'
and SalesOrderID >= 2200000


So… what if we would like to run this as a parallel plan to see if that’s any faster? Well we add the flag to the query and see what happens:

select *
from sales.SalesOrderHeaderEnlarged
where orderDate = '2010-07-01'
and SalesOrderID >= 2200000
option (querytraceon 8649)


As you can see… it now provides us with a parallel plan. Success.

Now, it’s worth noting that even with this flag, you still can’t get a parallel plan to be produced if the query is so lightweight that it’s simply not worth considering. I’ve found a few examples where this is the case… but then those queries would likely never perform well with parallelism anyway, so it’s no bad thing.

A second use for this flag is to see if you have any parallel inhibiting operators in your query. If you have a query with a large cost that you think should be running in parallel, then use this flag against the query and see what you get. If you get a parallel plan offered (and execution is MUCH faster) then look to tweak your code to force a parallel plan (use “optimize for” or other hints and tricks).

However, if you don’t get a parallel plan then maybe it’s due to inhibitors. A thorough list of these full or partial inhibitors can be seen on Paul White’s brilliant blog about parallelism here

So, taking one of these inhibitors (the table variable) you can see how, despite the trace flag, no parallel plan can be considered:

declare @table table
(
  
orderDate datetime
)
insert into @table
select OrderDate
from sales.SalesOrderHeaderEnlarged
where orderDate = '2010-07-01'
and SalesOrderID >= 2200000
option (querytraceon 8649)


However, no such issues occur when using a temp table:

create table #table
(
  
orderDate datetime
)
insert into #table
select OrderDate
from sales.SalesOrderHeaderEnlarged
where orderDate = '2010-07-01'
and SalesOrderID >= 2200000
option (querytraceon 8649)


Hopefully this allows you to see what this is my favourite trace flag as it can be incredibly useful to reduce query times by allowing you to make the most of parallelism and also to spot and so remove any detrimental inhibitors from large and expensive queries.
Comments:
NB: Comments will only appear once they have been moderated.