Let’s say you had report queries that were going wildly parallel, and you wanted to put a stop to it, but you couldn’t afford SQL Server Enterprise Edition.
You could do is create databases with different MAXDOP settings:
CREATE DATABASE [MAXDOP4]
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
CREATE DATABASE [MAXDOP1]
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;
Yes, MAXDOP has been a database-scoped configuration since SQL Server 2016.
Then set your reporting users’ default databases:
ALTER LOGIN [Maxx1] WITH DEFAULT_DATABASE=[MAXDOP1];
ALTER LOGIN [Maxx4] WITH DEFAULT_DATABASE=[MAXDOP4];
And then when they each run the same query, they get different maxdops:
What’s that, you say? Your users aren’t fully prefixing their objects with database names, so this wouldn’t work for you? I hate to give you two bad ideas in a single blog post, but I have one word for you: synonyms.
I need to point out that this “solution” – and I use that term wildly loosely – has all kinds of drawbacks:
- It only throttles CPU – queries can still get giant memory grants
- Each database gets its own query plans, which means you’ve also just doubled your parameter sniffing problems
- It’s painful to deal with lots of synonyms, and really, this only makes sense (and I use that term loosely) for reporting queries, not things that call stored procedures or do inserts