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:

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:

USE [master] GO ALTER LOGIN [Maxx1] WITH DEFAULT_DATABASE=[MAXDOP1]; GO ALTER LOGIN [Maxx4] WITH DEFAULT_DATABASE=[MAXDOP4];