Poor Man’s Resource Governor: Database-Scoped Configurations

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:

Yes, MAXDOP has been a database-scoped configuration since SQL Server 2016.

Then set your reporting users’ default databases:

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
Previous Post
Free Webcast on Wednesday: The New Robot DBAs in SQL Server 2017, 2019, and Azure
Next Post
What Is the SQL Server CEIP Service?

4 Comments. Leave new

  • Robert N. Harris
    October 6, 2020 9:37 am

    To quote Becky’s anonymous friend in Baby Got Back – “Ewww. Gross.”

    I love it.

    Reply
  • 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……

    Reply
  • 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 🙂

    Reply
  • Amazing article. I always thought MAXDOP is a server level setting.
    Thanks

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}