Great! That query gives me the Adventureworks salespeople and the number of red items they’ve sold in their last ten orders. But the execution time was a bit slow and so I decide to tune the query. The query plan looks like this:

There’s a couple things that jump out at me right away: Some of the thick lines tell me that there’s a lot of data getting processed. There’s also a warning that tells me that tempdb was used for a sorting operation. And in general, I notice that there are a lot of operators here including parallelism operators.

So now I’m going to try to look at a serial query plan for the same query. I have to tell SQL Server that the maximum degree of parallelism it should consider is one thread. That’s done with the MAXDOP query hint like this:

The hint is in there, but it’s not meant to be a permanent thing. I put it in there in order to look at the serial version of the query plan. Sure enough, it looks simpler and it’s easier to examine:

The information is still the same, but to me it’s a little clearer where the problem is. I see that SalesOrderHeader is scanned completely and then spooled (one iteration per person) and then sorted by date. I also see that there’s a lot of data coming from Person.Person.

So in this case, I may decide to add an index on SalesOrderHeader(SalesPersonId, ShipDate) like this:

A Bonus Optimization
There’s another optimization I’d like to consider. Notice that all 19,972 rows in the Person.Person table are scanned. Well I can do better than that. I can join Person.Person to HumanResources.Employee because I know that all salespeople are employees. The join acts as a filter and it really cuts down on processing. The query now looks like:

With the new index, this query performs about as well as we can hope. The new plan now looks like this:

Why this Works For Me

Adding MAXDOP 1 is a quick way to give me a serial version of the query plan and the serial version of the plan is simpler. This trick saves me time. Or it has often enough that it’s worthwhile to try.

Another reason this works for me is because the queries I write and maintain are often required to run sub-second. You might be in the same boat if you write your queries for high volume OLTP systems. This usually means that there’s a high standard for query performance. Acceptably tuned queries are fast enough that SQL Server will rarely decide to consider parallel query plans. And that means that it’s tempting to look for parallelism to find candidates for poorly tuned queries.

Okay, Here Come The Caveats

Reading the title, some will assume that I’m claiming a parallel query plan is a bad query plan. Absolutely not. Queries that are executed with parallel plans are queries that do a lot of work. That’s not bad in itself. Sometimes queries need to do a lot of work. The question that should be asked is whether these queries are doing too much work.

I’m not claiming MAXDOP 1 is a tuning technique in itself. It’s just a trick I use to look at a query from a different point of view. If your query is a thousand times faster after adding MAXDOP 1 and you don’t know why, then you’re not done yet.