MAXDOP

Question

MAXDOP

aaa121

SSCertifiable

Points: 5902
More actions
April 1, 2010 at 1:38 am

#386620

Recently I was given an urgent problem with a query a report writer had written. The query firstly populated a temp table and then proceded to populate a new temp table joining the 1st one to some physical tables.
The problem was that the query used to run in around 20 mins before all of a sudden never completing.
The challenge for me was that due to the time constraints involved I couldn't go through change management to make any physical changes to the database structure itself. I was therefore left to tune the query.
I did the obvious things first like removing order by's on the insert into table statment and returning only the columns used from derived tables instead of select *.
What I noticed though, that when I initially executed the query the optimiser was running the query in parallel and some threads were blocking each other on the same query - all relating to the same SPID. I remembered I had heard about this before and limiting the query to use 1 processor (using MAXDOP=1) can in cases allow a query to perform more quickly.
I added this hint and hey presto, it now runs in 7 minutes. What I really don't understand is HOW?
How was SQL Server blocking it's own threads within the same query?
How does preventing parallel executing, in some cases, increase performance?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

aaa-322853 (4/1/2010)
How does preventing parallel executing, in some cases, increase performance?

Because it produces a different execution plan. If you want to understand exactly why , you need to understand the execution plan. Here a link to a starter http://www.simple-talk.com/sql/performance/execution-plan-basics/

Clear Sky SQL
My Blog[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

aaa-322853 (4/1/2010)
How was SQL Server blocking it's own threads within the same query?

What happens is that SQL parallels a particular query operator, all the threads have to complete before the threads can be combined. If some are slower than others, the threads that finished their work earlier have to wait for the slower ones to complete. This is called a parallel skew, and is characterised by CXPacket waits.

If you look, you'd have seen at least one thread that didn't have the CXPacket wait, that probably had another wait. That's the one to worry about, not the CXPacket waits.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150468 More actions · Answer 3

Parallelism can be vital to good performance for some types of queries, so it is worthwhile spending some time reading up to appreciate the issues involved. Some of the best information available can be found on Craig Freedman's blog:

http://blogs.msdn.com/craigfr/archive/tags/Parallelism/default.aspx

One of my favourites is his PDF presentation, which you can download here:

http://blogs.msdn.com/craigfr/attachment/2167013.ashx

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions Follow · Answer 4

Thanks for the links Paul.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150468 More actions · Answer 5

CirquedeSQLeil (4/1/2010)
Thanks for the links Paul.

Actually, my advice is to read everything Craig F has ever written!

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions Follow · Answer 6

Paul White NZ (4/1/2010)
CirquedeSQLeil (4/1/2010)
Thanks for the links Paul.
Actually, my advice is to read everything Craig F has ever written!

Thanks - I will look into more of his stuff.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150468 More actions · Answer 7

GilaMonster (4/1/2010)
all the threads have to complete before the threads can be combined. If some are slower than others, the threads that finished their work earlier have to wait for the slower ones to complete.

Surely that's only true for a merging exchange?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

It's true any time the threads have to be synchronised.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150468 More actions · Answer 9

GilaMonster (4/2/2010)
It's true any time the threads have to be synchronised.

Yes that's a much better way of putting it - happy with that.

Otherwise, people might erroneously infer that a Gather Streams operator only ever starts producing rows when the last thread finishes, for example. Cool.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

MAXDOP

Cookies on SQLServerCentral