Database Scoped Configuration on SQL Server 2016

SQL Server 2016 has released several new features that have enhanced functionality and flexibility to increase database performance.

I’ve been working as a production SQL Server DBA for 12+ years and doing health check analyses on countless SQL servers; I noticed that there were several occasions when my analysis report came up with MAXDOP recommendation as one of the critical suggestions. However, almost half of the time we had to rule it out from our list just because an application connecting to that database only allowed MAXDOP=1 setting. Most of these applications were legacy applications – which don’t give us room to take advantage of parallelism or parallel query execution.

I’ve also seen that to reduce cost on resources, many organizations go for hosting/consolidating multiple databases on a single machine. And, if you have any application connecting to any one of those databases and has SQL Server MAXDOP=1 limitation, it prevents other databases on that same SQL instance from utilizing parallelism; and overall server performance suffers, since MAXDOP setting affects the entire instance.

Microsoft finally addressed that issue and introduced database level configuration options in SQL Server 2016. With these new configuration options on databases property, now you can configure MAXDOP value on database level rather than entire instance level. Now you can have multiple databases on single instance but with different MAXDOP value on each of them and utilize parallelism in an efficient way.

You can see these databases scoped configuration values on option tab under database property:
 

 

You can either configure it using GUI or using below T-sql command:

Use <DatabaseName>
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8
GO

You can also view these configuration values by using the below dynamic management view

SELECT * FROM  sys.database_scoped_configurations;

As shown in above image there are four configurations setting available on database level:

1. Max DOP. Using this option you can enable parallelism on databases’ level. Depending on number of logical processors, you can specify numeric value on this option and that allows SQL Server to use that many processors to execute queries hitting to that specific database in parallel processing.
2. LEGACY CARDINALITY ESTIMATION. SQL Server uses this option to generate the best possible execution plan by estimating the number of rows specific queries are going to return. Now you can turn this option on or off on database level.
3. Parameter sniffing. SQL Server prepares the execution plan by evaluating parameter passed to the stored procedure and uses the same plan for that stored procedure’s consecutive runs, or other procedures which are the same in nature or form. This way it tries to improve performance by using same plan and avoiding compilation time.

   However, it does create a problem for some complex procedures or queries that would require a more optimal plan than the one created based on one parameter value. In SQL 2016, you may disable this option on database level and have SQL use query optimizer as a plan for using more current SQL statistics.

4. Query Optimizer Fixes. If you turn on this option on database, SQL allows query optimizer for all the queries on that database to use all the features that came with latest SQL hotfix or patch applied on that SQL instance.

   This was not the case on earlier SQL version. On earlier versions of SQL 2016, query optimizer was not able to use all the latest fixes immediately that come with SQL hotfix to avoid situations where some queries may perform badly with new changes.

   However, you were still able to do so by turning on trace flag 4199 and that enables optimizer for all the queries on all the databases on SQL instance. But now you can enable this feature on database level without affecting queries on other databases on same instance.

   One thing to mention here, this feature is only useful if you are running on SQL 2016, but database compatibility level is set to below 130 (SQL 2016). If you have databases with compatibility with 130 then enabling or disabling this option is not much of use as with 130 compatibility level. SQL by default allows optimizer to use the latest changes that SQL hotfix offers.

5. CLEAR PROCEDURE CACHE. This option is not available on GUI (SSMS), but you can configure it using ALTER DATABSE command on database level. As the name suggests with this option you can clear procedure cache on database level now. I can’t remember how many times I have had to execute DBCC FREEPROCCACHE while working store procedure or query tuning work. With this option, I can do it on database level without affecting other databases proc cache on same SQL instance.

Another fantastic thing about these options is, if you are having databases configured on SQL Always on, it allows you to set different values for these options on Primary and that onto Secondary server.

For example, you can set MAXDOP=1 for database on primary server and keep MAXDOP=3 for same database on secondary server on SQL Always On as shown below:
 

 

You can achieve the same thing by running below queries:

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP =1;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=3;
GO

Nowadays, many people/organizations configure readable secondary replica on SQL Always on to offload all read only workload. This feature is very helpful where you can set MAXDOP=1 for database on primary replica, mostly because of application demand and at the same time you set MAXDOP=3 for same database on secondary replica and achieve better performance for all read only work or queries with parallelism enabled on it.

Here are some things to keep in mind or consider when you use these options:

• ALTER ANY DATABASE SCOPED CONFIGURATION permission is required in order to enable these database options.
• You can set up different scoped option on database on secondary replica from primary, but all secondary databases should have same configuration values, it does not allow different values on individual secondary databases.
• If you are using Query hint in SQL statement, then it overrides database scoped configuration.
• If you are running queries under context of different database, as shown below:
   
  Use Tempdb
go
select * from DBA.dbo.testtable
go

  then these new database setting does not work. If you have set up MAXDOP=2 on DBA database, then the above query won’t be able to use parallelism. You must run it under context of DBA database only; as shown below:

  Use DBA
go
select * from DBA.dbo.testtable
go

SQL Server continues to evolve as a database leader with new features to streamline processes for DBAs. If you’re looking for support with SQL, please reach out, our experts are standing by.