In SQL Server 2016, one of the key management improvement is a new feature that is called Database Scoped Configurations.

This improvement allows you to control a number of parameterisations of the SQL Server, based on the database level.

The Database Scoped Configurations bring a different type of parameter control to our disposal – the one that controls the parameters of only one certain database, and I call it a horizontal control, in the sense, that like on the picture on the left we control the space marked with the red color.

The option that we have had since SQL Server 2008 and significantly improved in later versions is a type that I call a vertical control – we have a certain group of users (default or manually configured) that we control, no matter which database they access on our server. You can see this on the image on the right of this text, with the magenta border marking the control border of the Resource Governor control area.

Notice that the Resource Governor settings still overdride the controllable options we may define within Database Scoped Configurations (this applies to MAXDOP in the current version only), but let us not rush in the hurry and continue with the overview of the current scope of improvements.

With every SQL Server version, Microsoft has enabled more and more different switches on the platform, allowing to combine different types of workloads within the same instance of the SQL Server. A lot of this improvements definitely have to do with the requirements and necessities of the Azure SQL Database, especially given the specifics of the Trace Flags usage (normally, you can’t enable them at all on Azure SQL Database), but those changes do matter a lot, given the possibility of mixing workloads and especially vendor applications that demand certain settings in less then compatible way with anything else.

Database Scoped Configurations

In the first version/iteration of the Database Scoped Configurations, there are 5 functionalities are supported on per Database level:

– Procedure Cache cleaning

– MAXDOP control

– Query Optimizer Cardinalitiy Estimation Model activation control

– Parameter Sniffing control

– Query Optimizer Hotfixes enabling

Another very important part of the database scoped configurations is that for the high availability installations where AlwaysOn Availability Groups are used, we can control the values on the primary as well as the on the secondary replicas, (with the exception of the Procedure Cache Cleaning which is available for the Primary Replica only). This means that we can set separate values for the primary replica where we write the data (let’s say its an OLTP System and we set MAXDOP = 1) and the secondary replicas where do the analytics (there we shall set up the MAXDOP = 8, for example).

Looking at the current functionaries, it is easy to understand how far those improvements do extend – Procedure Cache cleaning (before 2016 you would need to use DBCC FREEROCCACHE), MAXDOP (previously controllable on the instance level through sys.sp_configure), Query Optimizer Cardinalitiy Estimation Model (previously Trace Flags 2312 & 9481), Parameter Sniffing control (previously Trace Flag 4136), Query Optimizer Hotfixes enabling (previously Trace Flags 2312 & 4199). Notice that most of the mentioned trace flags were applyable on the instance level, making mixing of the database with the distinct needs impossible within the same SQL Server instance.

Starting with SQL Server 2016 and the current Azure SQLDatabases we have an additional group of Database Scoped Configurations within the options tab for every single Database. At the picture on the left, you can see that we have a direct possibility to control the 4 of the 5 available options for the Database Scoped Configurations, and the most importantly – we can control them for both primary and secondary replicas.

The only Database Scoped Configuration that is not to be found within SSMS GUI is the Procedure Cache cleaning – which makes a reasonable sense, because this is a command affecting the current memory status and it is not an option which has a number of different values. Do not worry about GUI, the most important is that you can control all Database Scoped Configurations through the principal weapon of choice for anyone working SQL Server: T-SQL.

T-SQL for Database Scoped Configurations:

For Database Scoped Configurations, Microsoft did an outstanding job with the T-SQL, giving us a number of ways to work with them:

– ALTER DATABASE SCOPED CONFIGURATION: running this command within the context of a database will allow us to do modifications to the Database Scoped Configuration.

– the DMV sys.database_scoped_configurations allows us to obtain the configured values of the current Database Scoped Configuration

For example, to change the MAXDOP setting for the Primary Replica, you can use the following command, setting it to 1:

ALTER DATABASE SCOPED CONFIGURATION 
    SET MAXDOP = 1;

You can set the MAXDOP for the secondary replicas with the following command (where I set it to 6):

ALTER DATABASE SCOPED CONFIGURATION 
    FOR SECONDARY 
    SET MAXDOP = 6;

One of the great syntax’s settings in this ALTER DATABASE SCOPED CONFIGURATION command is that we can assign the value to the secondary replicas to be equals to the primaries, which is doable with the following syntax for the MAXDOP, for example:

ALTER DATABASE SCOPED CONFIGURATION 
    FOR SECONDARY 
    SET MAXDOP = PRIMARY;

Switching the Parameter Sniffing off on the primary replica is done with the following command:

ALTER DATABASE SCOPED CONFIGURATION 
    SET PARAMETER_SNIFFING = OFF;

And the command for the Procedure Cache Cleaning (the one that is not available through GUI) is shown below, with the reset for the primary replica:

ALTER DATABASE SCOPED CONFIGURATION 
    CLEAR PROCEDURE_CACHE;

Notice that clearing the procedure cache is only available for the primary replica.

The other commands syntax can be found on the ALTER DATABASE SCOPED CONFIGURATION Documentation page.

For consulting currently used values, one should use the newest DMV: sys.database_scoped_configurations:

select *
from sys.database_scoped_configurations;

You can see the default results of the executed query, and notice one important piece of information – should the value for the secondary replica be set to NULL, that means that the values from the primary replica will be used. This also means that in order to find the right value for the secondary replicas we will need to use the following query:

select *, isnull(value_for_secondary,value) as RealValueForSecondary
from sys.database_scoped_configurations;

The Real MAXDOP

In Azure SQLDatabase and SQL Server 2016 we have now 4 distinct ways of controlling MAXDOP.

– Instance-wide setting (controlled through sys.sp_configure)

– Resource Governor setting (prevails as the maximum value)

– Database Scoped Configuration (prevails over Instance-wide setting, but have to obey to the Resource Governor maximum)

– Query Setting (obeys to Resource Governor and overrides the Database Scoped Configuration).

Let’s check out a following practical example:

I have configured my VM with 4 CPU cores, and I will set distinct settings for each of the possible ways, testing the final results:

Let’s set the instance wide setting to 4 cores:

EXEC sys.sp_configure N'max degree of parallelism', N'4'
GO
RECONFIGURE 
    WITH OVERRIDE

Now, let’s set enable the Resource Governor, and change the MAXDOP for the default Workload Group to 2:

ALTER RESOURCE GOVERNOR 
   RECONFIGURE;
ALTER WORKLOAD GROUP [default] 
   WITH(max_dop=2);
ALTER RESOURCE GOVERNOR 
   RECONFIGURE;

The third configuration to configure is the Database Scoped Configuration, let’s set the MAXDOP to 1:

ALTER DATABASE SCOPED CONFIGURATION 
    SET MAXDOP = 1;

Let’s run the following query agains the free ContosoRetailDW database:

select sum(SalesAmount)
from dbo.FactOnlineSales;

Even though the estimated subtree cost is 60.47, and the Max Threshold for Parallelism on this instance is equals to 20, I do receive a single-threaded execution plan, as you can see below:

This happens because I have configured MAXDOP = 1 for the ContosoRetailDW Database Scoped Configuration in the previous steps.

Let’s try to force the MAXDOP to be equal to 2, trying to override the Database Scoped Configuration:

select sum(SalesAmount)
from dbo.FactOnlineSales
option (maxdop 2);

This time we have a parallel plan being executed on 2 cores, as we have requested in the option clause:

This shows that the Database Scoped Configuration are prevailing on the default queries, and on the single query level we can go beyond the configured values, as currently happens in the relation to the instance-wide setting.

Let’s set the Database Scoped Configuration for MAXDOP to be equals to 4, in order to see if it can override the Resource Governor settings:

ALTER DATABASE SCOPED CONFIGURATION 
    SET MAXDOP = 4;

Let’s re-execute our query without direct specification of the MAXDOP:

select sum(SalesAmount)
from dbo.FactOnlineSales

The execution plan runs in parallel, as expected but the actual number of cores used for processing is limited to 2, the number specified in the Resource Governor workload group:

This result clearly shows that Resource Governor stays the responsible for the maximum values, while Database Scoped Configuration can allow to minimise them further, if needed.

For finding out with a more easy way, the actual number of cores that by default are capping the MAXDOP, please consider using the following query:

declare @effectiveDop smallint  
select @effectiveDop = min(dop)
from
(select effective_max_dop as dop
from sys.dm_resource_governor_workload_groups
where group_id in (select group_id from sys.dm_exec_requests where session_id = @@spid)
union all 
select cast(value as smallint) as dop
from sys.database_scoped_configurations dsc
where dsc.name = 'MAXDOP'
) configs;
select @effectiveDop;

Do not forget to reset the default workload group value for MAXDOP in the end:

ALTER WORKLOAD GROUP [default] 
   WITH(max_dop=0);
ALTER RESOURCE GOVERNOR 
   RECONFIGURE;

Final Thoughts

I absolutely love the direction that Microsoft took with this Database Scoped Configurations – it gives us very accessible ways (and some new ways as well – think of any secondary replica’s control right from the primary) to control a number of options that were accessible only through the trace flags (sysadmin or even system restart would be required), and even more importantly – we can exercise the horizontal control of the settings, on the specific database level.

The possibility of mixing different workloads and applications on the same instance (Sharepoint, CRM, custom analytics and data marts for example), allowing us to consolidate more of the management, should there be a need for it.