Parallelism in SQL Server Execution Plans

Parallelism is more than a tough way to park your car! Seriously, one of the big gains we get from using multiple CPU servers is spreading the work across those processors. MVP Grant Fritchey shows you what it a query plan looks like when parallel execution is happening and also explains the repartition operator.ur queries.

I'm currently working for FM Global, an industry leading engineering & insurance company, as a DBA. I've done development of large scale applications in languages such as VB, C# and Java. I've worked in SQL Server from the hoary days of 6.0. My nickname at work is the "The Scary DBA." I even have an official name plate with it. I wear it proudly. I was awarded a Microsoft MVP in April of '09.

Leon on 11/2/2008

Would be nice to tell how to stop if you don't want it and how to tell when it is faster or slower. But still a good topic

Grant Fritchey on 11/3/2008

Either change the max degree of parallelism to something other than 0, 1 to shut it down completely, or change the cost threshold higher than the default value of 5. Another good idea for a video.

Travis on 8/26/2009

Good video Grant. I might also add that this can be set at te server level (Properties|Advanced), Table level (ALTER TABLE) ad also at the query hint level (MAXDOP). But, as Grant points out in this video there is a cost factor involved in doing this so you have to weigh using this for small to medium-sized recordsets against this cost. <ost small to midsized OLTP tables do not tend to benefit as much from using this option (although there is nothing stopping you from using it) as much as Data Warehouse tbles do where recordsets can regularly be extremely enormous in size. This is where using Maximum Degree of parellism reallt shines!

John Torrey on 8/27/2009

The volume was extremely low. It was also very quick in its explanation. I spent most of my time restarting the video.

Michael Lawson on 8/27/2009

First statement is incorrect - MAXDOP=1 - forces all query plans to be sequential (not parallel). Assume you meant MAXDOP=0. I think the key thing to emphasize for parallel queries is that it is intended to deliver the results in a shorter elapsed time, using more resources (cpu, memory) than the sequential plan.

Travis on 8/27/2009

MAXDOP = 0 is the default which uses all available processors. MAXDOP = 1 shuts it down as Grant already stated...

Grant Fritchey on 8/28/2009

My first statement wasn't that the MAXDOP was equal to 1 but that the parallelism threshold was set to 1. That means any query with an estimated cost greater than 1 would result in a parallel plan if the MAXDOP was set to either 0 or greater than 1. I think the sound was a bit off on that one, leading to the confusion.

Justin on 9/4/2009

Excellent video!!! Straight to the point!

Michael on 9/4/2009

I continue to be impressed by the information that Grant provides within these videos as well as his books...His Apress Query Performance book is one of the best I have read.

Wil Sisney on 9/4/2009

The video didn't have much introduction, but the topic was very good.