On March 7, 2016, Microsoft announced the availability of the RC0 Build of SQL Server 2016. One of the new features in the RC0 Build is Database Scoped Configurations, which gives you the ability to easily make several database-level configuration changes for things that were previously configured at the instance-level.
- Setting MAXDOP for an individual database
- Setting the query optimizer cardinality estimation model for an individual database (independent of the database compatibility level)
- Enable or disable parameter sniffing for an individual database
- Enable or disable query optimization hotfixes for an individual database (equivalent to TF 4199)
- The ability to clear the plan cache for an individual database (without using a DBCC command)
If you are using AlwaysOn Availability Groups, the first four commands can also be used to affect the database-level configuration setting for ALL of the secondary database copies that are in a AG. You can set these values for just the Primary database, set the values in the secondary database(s) to the same value as the Primary database, or set the values in the secondary database(s) to different values from the Primary database.
There is also a new DMV, sys.database_scoped_configurations, which lets you see the configuration values for the current database, including the values for the Primary database and the secondary databases (if that database is part of an AlwaysOn AG). I have examples of these commands in the code in Figure 1.
Figure 1: Example Database Scoped Configuration Commands in SQL Server 2016