Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Forcing Parallelism (TF 8649)
Published: Nov 28, 2014
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.