A Common Misconception about MAXDOP

Out of the box, SQL Server is (and has been) able to take advantage of multiple processors/cores without any effort on behalf of administrators. In fact, for any query with a cost of greater than 5, SQL Server will (by default) use a parallel—meaning that it’ll look into breaking up work against multiple threads of execution.

Most of the time, this is a Good Thing™. For example, suppose users are regularly querying a table with 3 million rows—and neither developers nor DBAs have set up an applicable index (or indexes) for these oft-repeated queries. Then, for the sake of simplicity, assume that it would take 2 seconds for SQL Server to scour all 3 million rows if it executed this query via just a single thread of execution. On the other hand, if SQL Server can break-up the process of scanning 3 million rows into, say, 6 operations (each taking roughly 1/6^th of the table), then this 'scouring process' now takes roughly 1/6^th of the time it did previously (plus a few fractions of a second or less for overhead to re-assemble results/etc) or roughly one-third of a second to execute.

If you’re an end-user, burning up some additional CPU to get your queries to run in less than half a second is a big win over waiting around for 2 seconds while your query runs each time you’re loading a screen or something similar. Yet, for SysAdmins, all that additional CPU comes at a cost—and might eventually add up. (More on this in a bit.)

To me, what’s cool about this situation is that SQL Server automatically does what’s needed to try and get that query ‘out the door’ as quickly as possible. Putting indexes on this table would be a better solution that burning through all of that CPU, but SQL Server can’t make that call. Instead, it looks around, says to itself "Self, you’re sporting some beefy hexa-cores, why not give this user what they want? Quickly? That’ll be awesome." Then it gets busy being awesome and transparently spins up multiple threads of execution to speed up processing.

Constraining Parallelism – the MAXDOP Option

In (what should usually be rare cases) it's possible to tell SQL Server not to be 'so awesome'—or to use less processing power. To do this, you can just execute operations with the MAXDOP query hint as part of the option clause for SQL Statements, as follows:

SELECT 
	SomeColumn, 
	AnotherColumn,
	Etc
FROM dbo.SomeHugeTableWithoutAProperIndex
WHERE
	SomeNonIndexedColumn = 'someValue' 
OPTION (MAXDOP = 1);
GO

Typically, this is only something you'll want to do in cases where the optimizer ends up picking a bad plan (which is usually pretty rare), or where you’re trying to constrain the amount of resources something is using—like an index rebuild (though, note that MAXDOP is set as a different option for index operations) as a means of constraining how much it might otherwise 'hog' system resources. (And, of course, there are some operations that simply can’t be MAXDOP'd—like DBCC CHECKDB—meaning that your only option to constrain concurrency/usage would be to use something like the SQL Server Resource Governor.)

Correcting the Misconception: Regardless of when or where the MAXDOP option is set (and regardless of whether is use is a good fit or not—and it can be a good fit in some situations, and a bad fit in others), a key reminder about MAXDOP is that it does NOT limit the TOTAL number of threads that can be used during processing of the query in question.

Instead, and because SQL Server is so fantastically multi-threaded out of the box, MAXDOP only controls the number of parallel threads PER OPERATOR during query execution. And, since queries can (and very frequently do) have multiple operators, it’s entirely possible for SQL Server to throw multiple threads at a single operation to handle different operators. And a great write-up of the specifics of how that works is found here.

So, stated differently: the misconception I'm trying to clear up is that it’s common to hear (or even say) that MAXDOP limits the number of processors that can be used—which simply isn't true; MAXDOP constrains the number of threads per operator.

Comments

Plain text