Michael J. Swart

Takeaway: I describe a technique I use to tune parallel queries. I tune the serial version of the query using MAXDOP 1. It’s a simpler query plan. Faster understanding leads to faster solutions.

How better to explain than by example!

The Example

For this example, I use AdventureWorks2012 and I beef up the sales order tables using Jonathan Kehayias’s script Enlarging the AdventureWorks Sample Databases. For my example, I want to know which salesperson sold the most red items in their last ten orders (looking at orders with at least one red item).

My first crack at the query gives me this:

SELECT P.FirstName, P.LastName, SUM(OrderQty) Items
FROM Person.Person P
CROSS APPLY (
    SELECT TOP 10 SUM(OrderQty) as OrderQty, H.SalesOrderID
    FROM Sales.SalesOrderDetailEnlarged D
    JOIN Sales.SalesOrderHeaderEnlarged H
        ON D.SalesOrderID = H.SalesOrderID
    JOIN Production.Product Pr
        ON D.ProductID = Pr.ProductID
    WHERE H.SalesPersonID = P.BusinessEntityID
        AND Pr.Color = 'red'
    GROUP BY H.SalesOrderID, H.ShipDate
    ORDER BY H.ShipDate DESC
) AS LastTenOrdersWithRedItems
GROUP BY P.BusinessEntityID, P.FirstName, P.LastName
ORDER BY SUM(OrderQty) DESC

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:

SELECT P.FirstName, P.LastName, SUM(OrderQty) Items
FROM Person.Person P
CROSS APPLY (
    SELECT TOP 10 SUM(OrderQty) as OrderQty, H.SalesOrderID
    FROM Sales.SalesOrderDetailEnlarged D
    JOIN Sales.SalesOrderHeaderEnlarged H
        ON D.SalesOrderID = H.SalesOrderID
    JOIN Production.Product Pr
         ON D.ProductID = Pr.ProductID
    WHERE H.SalesPersonID = P.BusinessEntityID
        AND Pr.Color = 'red'
    GROUP BY H.SalesOrderID, H.ShipDate
    ORDER BY H.ShipDate DESC
) AS LastTenOrders
GROUP BY P.BusinessEntityID, P.FirstName, P.LastName
ORDER BY SUM(OrderQty) DESC
OPTION (MAXDOP 1) -- this line has been added

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:

CREATE INDEX IX_SalesOrderHeaderEnlarged_SalesPersonShipDate 
    ON Sales.SalesOrderHeaderEnlarged(SalesPersonId ASC, ShipDate DESC)

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:

SELECT P.FirstName, P.LastName, SUM(OrderQty) Items
FROM Person.Person P
JOIN HumanResources.Employee E
    ON P.BusinessEntityID = E.BusinessEntityID
CROSS APPLY (
    SELECT TOP 10 SUM(OrderQty) as OrderQty, H.SalesOrderID
    FROM Sales.SalesOrderDetailEnlarged D
    JOIN Sales.SalesOrderHeaderEnlarged H
        ON D.SalesOrderID = H.SalesOrderID
    JOIN Production.Product Pr
        ON D.ProductID = Pr.ProductID
    WHERE H.SalesPersonID = P.BusinessEntityID
        AND Pr.Color = 'red'
    GROUP BY H.SalesOrderID, H.ShipDate
    ORDER BY H.ShipDate DESC
) AS LastTenOrders
GROUP BY P.BusinessEntityID, P.FirstName, P.LastName
ORDER BY SUM(OrderQty) DESC

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.
• Parallel execution plans are not dull or useless! They’re fascinating! I’ve learned that the mental effort spent following a Paul White post on parallelism always pays off.