Ten Ways to Set MAXDOP

Whenever I work with SQL Server, I’m amazed at how many ways there are to influence its behavior. For example, take the maximum degree of parallelism for a query. Just offhand, I thought of ten different ways you can tweak it:

1. At the server level with SSMS. In SSMS, right-click on the server, click Properties, Advanced, scroll down into the Parallelism section, and set MAXDOP to 1. Click OK. (Be aware that this blows your plan cache instantly.)

2. At the server level with sp_configure. Just the command level version of the above, with the same side effect:

3. At the query level with hints. Use OPTION (MAXDOP 1) at the end of your query to tie its hands behind its back. Interestingly, when you view the execution plan, the SELECT operator’s NonParallelPlanReason says “MaxDOPSetToOne” – which it is, but just at the query level, not what you might have expected. Even less expected: your hint can go HIGHER (not just lower) than the server-level setting.

4. By setting Cost Threshold for Parallelism really high. If you play around with this lesser-known parallelism setting and raise it up in the thousands (or millions or whatever), you can effectively set MAXDOP to 1. This comes in handy when some jerk puts a high MAXDOP hint in their query: they’re only picking the max degree of parallelism IF the query goes parallel. By setting CTfP high, you’re making sure it won’t go parallel. Insert diabolical laugh here.

5. By configuring Resource Governor. This Enterprise Edition feature lets you create a workload group with a MAX_DOP hint. (Yes, it has an underscore between MAX and DOP. No, I don’t know why. Yes, this can also override the server-level setting upwards.)

6. By using parallelism-inhibiting T-SQL. Paul White has an excellent rundown of things that will effectively set your entire query (or zones of the plan) to be MAXDOP 1.

7. By using query-level trace flag 8649. In that same post, Paul shows how OPTION (QUERYTRACEON 8649) can force your query to go parallel. Look, I didn’t title this blog post “Great Ideas in Query Tuning.”

8. By using Adam Machanic’s make_parallel() function. Add this adorable little function to your queries, and SQL Server thinks it’s going to be dealing with 1,099,511,627,776 rows – when there’s really only one. Hee hee!

9. By putting scalar functions in computed columns. Erik discovered that just by having these nasty little devils in your database, you’re not just causing user queries to go single-threaded, but even index maintenance and CHECKDB operations miss out on the joy of parallelism.

10. By setting it at the database level in SQL Server 2016. Right-click on your database and click Properties, and in the Options pane, there’s a handful of nifty new options including Max DOP and Max DOP For Secondary.

10a. By creating a different database just for parallelism. If you fully-qualify your database objects in queries like this:

Then you can get different MAXDOP settings simply by running your query in different databases – depending on their database-level parallelism settings. Here’s a screenshot of my database list that may help explain where I’m going with this:

That’s right – depending on where I run that query, it’ll get different parallelism options.