Database Scoped Configuration

  • Article

This release now supports a new database level object holding optional configuration values to control the performance and behavior of the application at the database level. This support is available in both SQL Server 2016 and SQL Database V12 using the new ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) statement. This statement modifies the default SQL Server 2016 Database Engine behavior for a particular database. Several benefits are expected from using this feature

  • Allows to set different configuration options at the database level
    • Current functionality allows to set it up only at the server or individual query level using query hints
    • This is especially important for Azure SQL DB, where certain options could not be configured at the database level.
  • Provides better isolation level for setting different options in case of multiple databases/applications running in a single instance.
  • Enables lower level permissions that can be easily granted to individual database users or groups to set some configuration options
  • Allows to setup the database configuration options differently for the primary and the secondary database which might be necessary for the different types of workloads that they serve

The following options can be configured

  • Set the MAXDOP parameter to an arbitrary value (0,1,2, …) to control the maximum degree of parallelism for the queries in the database. It is recommended to switch to db-scoped configuration to set the MAXDOP instead of using sp_configure at the server level, especially for Azure SQL DB where sp_configure is not available. This value may differ between the primary and the secondary database. For example, if the primary database is executing an OLTP workload, the MAXDOP can be set to 1, while for the secondary database executing reports the MAXDOP can be set to 0 (defined by the system). For more information on MAXDOP see Configure the max degree of parallelism Server Configuration Option
  • Set the LEGACY_CARDINALITY_ESTIMATION option to enable the legacy query optimizer Cardinality Estimation (CE) model (applicable to SQL Server 2012 and earlier), regardless of the database compatibility level setting. This is equivalent to Trace Flag 9481. This option allows to leverage all new functionality provided with compatibility level 130, but still uses the legacy CE model (version 70) in case the latest CE model impacts the query performance. For more information on CE see Cardinality Estimation
  • Enable or disable PARAMETER_SNIFFING at the database level. Disable this option to instruct the query optimizer to use statistical data instead of the initial values for all local variables and parameters when the query is compiled and optimized. This is equivalent to Trace Flag 4136 or the OPTIMIZE FOR UNKNOWN query hint
  • Enable or disable QUERY_OPTIMIZER_HOTFIXES at the database level, to take advantage of the latest query optimizer hotfixes, regardless of the compatibility level of the database. This is equivalent to Trace Flag 4199
  • CLEAR PROCEDURE_CACHE which allows to clear procedure cache at the database level without impacting other databases and without requiring sysadmin permission. This command can be executed using ALTER ANY DATABASE SCOPE CONFIGURATION permission on the database, and the operation can be executed on the primary and/or the secondary

For the T-SQL syntax and other details see ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)