MAXDOP is a Lie

Let’s start with STATISTICS TIME output from a query:

How many cores is it using? 124ms of cpu over 42ms… = an average of 2.95 cores per second.

Now what if I told you this was a MAXDOP 2 query?

It’s true, this was a MAXDOP 2 query, and it was definitely using more than 2 cores at once (though admittedly STATISTICS TIME can be imprecise). A friend of mine noticed this situation happening, and Paul White provided the explanation: it’s only the parallel branches that are limited to MAXDOP. The coordinator worker (the serial section at the far left of the plan) runs at the same time. Usually it doesn’t have much to do, and the parallel zones don’t run full speed, so you don’t see more than MAXDOP CPU usage.

But now that we know this is possible, how would you design a query to deliberately use more than MAXDOP? My own solution was to find a plan with minimal blocking, and force computation on the serial thread with TOP and some column weirdness.

And here’s the monstrosity I used (StackOverflow2010):

;WITH CTE AS (
SELECT TOP 100000 'a' Val
FROM dbo.Posts p1
JOIN dbo.Posts p2
	ON p1.Id = p2.Id
	)

SELECT 
COUNT(CHECKSUM(CHECKSUM(CHECKSUM(CHECKSUM(CHECKSUM(CHECKSUM(CHECKSUM(CHECKSUM(CHECKSUM(CHECKSUM(
CASE WHEN CONVERT(nchar(1),Val) = N'a' THEN 1 END
)+1)+1)+1)+1)+1)+1)+1)+1)+1+1.1))
FROM CTE
OPTION(
QUERYTRACEON 8649, MERGE JOIN,
MAXDOP 2)

It’s…not elegant, so I’m curious what examples others can find. But hey, this is a query that uses more than MAXDOP!

Edit: it seems that I accidentally trolled Pedro Lopes with the title. No, MAXDOP is not a lie, technically it’s in the documentation (and here, links courtesy of Pedro). Sorry Pedro! However, if you really want to learn how things work, I recommend reading Paul White’s article instead, which happens to be *both* accurate and readable.