Max Degrees of Parallelism (MAXDOP) tells the database engine, “If you decide to parallelize a query, go parallel with this many worker threads.”

(It’s a little more complex than that – there is also a coordinating thread, plus a single plan might have multiple parallel zones that each consume MAXDOP worker threads, but for the sake of this blog post, let’s keep it simple.)

Microsoft’s recommendations on how to set MAXDOP are a little long-winded, but again, in the interest of brevity, I’m going to summarize it as setting it to the number of cores in each physical processor, up to 8. (Again, the rules are much more complex – but if you want the full story, click on that link. I’m going to keep moving.)

In Azure SQL DB, you set max degrees of parallelism at the database level. You right-click on the database, go into properties, and set the MAXDOP number.

I say “you” because it really is “you” – this is on you, bucko. Microsoft’s magical self-tuning database doesn’t do this for you.

And where this backfires, badly, is that Azure SQL DB has much, much lower caps on the maximum number of worker threads your database can consume before it gets cut off. You’ll get an error like this:

The request limit for the database is 2000 and has been reached.

Thing is, that error message is a vicious lie: you haven’t hit 2,000 requests. You’ve just hit 2,000 worker threads! In the case of my client, on their 20-core Azure SQL DB, queries were going parallel and consuming 20+ worker threads. All it took was ~90 simultaneous parallel queries, and they’d hit the worker thread limits. It was so confusing because they thought there was no way their app could possibly be sending in 2,000 requests – and they were right.

The Azure SQL DB resource limits page explains:

The first fix you should try: check your database’s maxdop setting, and if it’s 0, read how to set MAXDOP, and set it. Don’t waste money upsizing your server, and don’t waste time tuning code until you’ve corrected this terrible default setting first.