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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE DATABASE [MAXDOP4] GO USE [MAXDOP4] GO ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4; GO CREATE DATABASE [MAXDOP1] GO USE [MAXDOP1] GO ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1; GO |
Yes, MAXDOP has been a database-scoped configuration since SQL Server 2016.
Then set your reporting users’ default databases:
|
1 2 3 4 5 6 |
USE [master] GO ALTER LOGIN [Maxx1] WITH DEFAULT_DATABASE=[MAXDOP1]; GO ALTER LOGIN [Maxx4] WITH DEFAULT_DATABASE=[MAXDOP4]; GO |
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
4 Comments. Leave new
To quote Becky’s anonymous friend in Baby Got Back – “Ewww. Gross.”
I love it.
Oh, and another bullet for your list of “problems this solution creates” is ownership chaining.
Once you start using synonyms to move objects to another database, you lose ownership chaining & may need to do explicit grants on tables directly. Of course, now you can layer in enabling cross-database ownership chaining, and make your environment a real train wreck……
I had similar idea, but for compatibility level. In 2014 to change optimizer used on a query level you needed to use trace flags. Since they require sysadmin, my idea was to run queries from database that has compatibility level that I wanted. I didn’t implement it, but now I see I could make it into a blog post at least 🙂
Amazing article. I always thought MAXDOP is a server level setting.
Thanks