New Database scoped Configurations

MS introduced new Database scoped configurations in SQL Azure (V12) and SQL Server 2016;

Need for new Database Scoped Configurations

Have you ever faced a situation where one of the database requirements are totally different from other like MAXDOP settings and you wanted to set a different MAXDOP for a single DB; or you wanted a feature which can keep such Primary and secondary database configurations in sync. MS is listening and they introduced Database Scoped Configurations and you can change them with this DDL statement: Alter Database Scoped Configuration. This was a much needed enhancement.

Here’s a list of Database scoped configurations which are now available on DB level. These are not new configurations, but now you can also configure these options at the database level rather than the server level. So imagine you are planning to consolidate servers on a single instance, but earlier you were not able to do it because of different needs of such configurations, but with this option in place now we can keep all such databases on a single instance and still keep the settings based on the individual requirements.

This feature is available both in Azure SQL databases (V12) and SQL Server 2016. The DB scoped configurations available in this release are:

• Clear procedure cache.
• Set the MAXDOP parameter value for the primary database based on what works best for that particular database and set a different value (e.g. 0) for all secondary databases used (e.g. for reporting queries).
• Set the query optimizer cardinality estimation model independent of the database to compatibility level.
• Enable or disable parameter sniffing at the database level.
• Enable or disable query optimization hotfixes at the database level.

The option which I liked the most is to keep primary and secondary databases in sync with respect to database scoped configurations.

 

While using this statement you can use the keyword FOR SECONDARY → while altering DB scoped configurations, you can specify this switch to apply the settings for secondary databases; Please note that all secondary databases must have the identical values).

for each sub-configuration you get these options: { ON | OFF | PRIMARY }

Primary → This option tells SQL to use the same setting of Primary on secondary DB. If this option is selected it will take effect on all secondaries. If the configuration on the primary changes, the value on the secondaries will change accordingly. PRIMARY is the default setting for the secondaries.

ON →  simply enable the configuration, that means it will override the configuration configured at server\instance level;

OFF → Disables the configuration, server\instance level configuration will be used for this DB;

What all permissions I need in order to configure this?
One must have ALTER ANY DATABASE SCOPE CONFIGURATION permission. If in case you want to grant this permission you must have CONTROL permission on the database.

Syntax:

ALTER DATABASE SCOPED CONFIGURATION  
{        
     {  [ FOR SECONDARY] SET <set_options>  }    
}  
| CLEAR PROCEDURE_CACHE  
[;]    
  
< set_options > ::=    
{  
    MAXDOP = { <value> | PRIMARY}    
    | LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}    
    | PARAMETER_SNIFFING = { ON | OFF | PRIMARY}    
    | QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}    
}

 

Hope you liked the post, Stay tuned and I’ll explain each configuration in subsequent blogs.

Happy Learning!

feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLChamp