Implement database scoped configuration
In this we will learn how to implement database scoped configuration.
Arguments
FOR SECONDARY
First, it defines the secondary database settings (all secondary databases must have the identical values).
CLEAR PROCEDURE_CACHE [plan_handle]
Then, clears the procedure (plan) cache for the database, and can be executed both on the primary and the secondaries.
And, specify a query plan handle to clear a single query plan from the plan cache.
APPLIES TO: Specifying a query plan handle is available in Azure SQL Database and SQL Server 2019 or higher.
MAXDOP = { | PRIMARY }
Specifies the default max degree of parallelism (MAXDOP) setting that should be used for statements. Here, 0 is the default value and indicates that the server configuration will be used instead. The MAXDOP at the database scope overrides (unless it is set to 0) the max degree of parallelism set at the server level by sp_configure.
Further, you can use the MAXDOP option to limit the number of processors to use in parallel plan execution. SQL Server considers parallel execution plans for queries, index data definition language (DDL) operations, parallel insert, online alter column, parallel stats collection, and static and keyset-driven cursor population.
PRIMARY
Can only be set for the secondaries, while the database in on the primary, and indicates that the configuration will be the one set for the primary. However, if the configuration for the primary changes. Then, the value on the secondaries will change accordingly without the need to set the secondaries value explicitly. PRIMARY is the default setting for the secondaries.
LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }
Sets the query optimizer cardinality estimation methodology to SQL Server 2012 and older versions, regardless of the database’s compatibility level. The default is OFF, which determines the cardinality estimation methodology for the query optimizer depending on the database compatibility level. Further, when LEGACY_CARDINALITY_ESTIMATION is set to ON, Trace Flag 9481 is enabled.
PRIMARY
This value is only usable on secondaries when the database is on the primary, and it indicates that the value for this setting on all secondaries is the primary’s value. If the primary configuration changes, the value on the secondaries changes as well, thus there’s no need to adjust the secondaries value directly. The secondaries are set to PRIMARY by default.
IDENTITY_CACHE = { ON | OFF }
APPLIES TO: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database
At the database level, this option enables or disables the identity cache. The default setting is ON. On tables with identity columns, identity caching is utilized to improve INSERT efficiency. Disable the IDENTITY CACHE option to avoid gaps in the values of an identity column if the server restarts unexpectedly or fails over to a backup server. Furthermore, this option is identical to Trace Flag 272, except it may be set at the database level rather than only the server level.
INTERLEAVED_EXECUTION_TVF = { ON | OFF }
APPLIES TO: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database
Allows you to activate or disable Interleaved execution for multi-statement table-valued functions at the database or statement scope while keeping database compatibility level 140 or above. Interleaved execution is a feature of Azure SQL Database’s adaptive query processing.
BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF}
APPLIES TO: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database
Allows you to turn on or off batch mode adaptive joins at the database scope while retaining database compatibility level 140 or above. Batch mode adaptive joins, on the other hand, are a feature of SQL Server 2017’s Intelligent query processing (14.x).
TSQL_SCALAR_UDF_INLINING = { ON | OFF }
APPLIES TO: SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database (feature is in public preview)
Allows you to enable or disable T-SQL Scalar UDF inlining at the database scope while still maintaining database compatibility level 150 and higher. However, T-SQL Scalar UDF inlining is part of the Intelligent query processing feature family.
WHEN_SUPPORTED
This value elevates operations that support ONLINE. Operations that do not support online will be run offline.
ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
APPLIES TO: SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database (feature is in public preview)
Allows you to choose settings that will allow the engine to raise supported operations to resumable status automatically. The default is OFF, which implies that unless stated in the statement, operations will not be upgraded to resumable. The current value of ELEVATE RESUMABLE is reflected in sys.database scoped configurations. These choices, however, are only available for procedures that are resumable.
FAIL_UNSUPPORTED
This value elevates all supported DDL operations to RESUMABLE. Operations that do not support resumable execution fail and throw a warning.
ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF}
APPLIES TO: SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database (feature is in public preview)
Allows you to set or disable row mode memory grant feedback at the database scope while keeping database compatibility level 150 or higher. Row mode memory grant feedback, on the other hand, is a feature of SQL Server 2017 (14.x ) intelligent query processing (row mode is supported in SQL Server 2019 (15.x) and Azure SQL Database).
BATCH_MODE_ON_ROWSTORE = { ON | OFF}
APPLIES TO: SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database (feature is in public preview)
Allows you to enable or disable batch mode on rowstore at the database scope while still maintaining database compatibility level 150 and higher. However, Batch mode on rowstore is a feature that is part of Intelligent query processing feature family.
ACCELERATED_PLAN_FORCING = { ON | OFF }
Applies to: SQL Server (Starting with SQL Server 2019 (15.x))
Enables an optimized mechanism for query plan forcing, applicable to all forms of plan forcing, such as Query Store Force Plan, Automatic Tuning, or the USE PLAN query hint. The default is ON.
LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF}
APPLIES TO: SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database
Allows you to enable or disable the lightweight query profiling infrastructure. Further, the lightweight query profiling infrastructure (LWP) provides query performance data more efficiently than standard profiling mechanisms and is enabled by default.
VERBOSE_TRUNCATION_WARNINGS = { ON | OFF}
APPLIES TO: SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database
- Firstly, allows you to enable or disable the new String or binary data would be truncated error message. SQL Server 2019 (15.x) introduces a new, more specific error message (2628) for this scenario:
- Secondly, String or binary data would be truncated in table ‘%.ls’, column ‘%.ls’. Truncated value: ‘%.*ls’.
- Further, when set to ON under database compatibility level 150, truncation errors raise the new error message 2628 to provide more context and simplify the troubleshooting process.
- And, when set to OFF under database compatibility level 150, truncation errors raise the previous error message 8152.
PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES
APPLIES TO: Azure SQL Database only
The PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES option determines how long (in minutes) the resumable index is being paused before being automatically aborted by the engine.
- Firstly, the default value is set to 1 day (1440 minutes)
- Secondly, the minimum duration is set to 1 minute
- Thirdly, the maximum duration is 71582 minutes
- Lastly, when set to 0, a paused operation will never automatically abort
Limitations and Restrictions
MAXDOP
The granular settings can override the global ones and that resource governor can cap all other MAXDOP settings. However, the logic for MAXDOP setting is the following:
- Firstly, query hint overrides both the sp_configure and the database scoped configuration. If the resource group MAXDOP is set for the workload group:
- Firstly, if the query hint is set to zero (0), it is overridden by the resource governor setting.
- Next, if the query hint is not zero (0), it is capped by the resource governor setting.
- Secondly, the database scoped configuration (unless it’s zero) overrides the sp_configure setting unless there is a query hint and is capped by the resource governor setting.
- Lastly, the sp_configure setting is overridden by the resource governor setting.
QUERY_OPTIMIZER_HOTFIXES
When the QUERYTRACEON hint is used to activate the default query optimizer in SQL Server 7.0 through SQL Server 2012 (11.x) editions or query optimizer hotfixes, the default query optimizer is enabled. Furthermore, the query hint and the database scoped configuration option would be an OR condition, meaning that if either is enabled, the database scoped configurations would apply.
Geo DR
The secondary value is used by readable secondary databases (Always On Availability Groups and Azure SQL Database geo-replicated databases) to validate the database’s condition. However, despite the fact that recompilation does not occur on failover, the new primary includes queries that use the secondary settings. Further, the idea is that the settings between main and secondary only alter when the workload varies, so cached queries use the best parameters.
DacFx
Since ALTER DATABASE SCOPED CONFIGURATION is a new feature in Azure SQL Database and SQL Server (starting with SQL Server 2016 (13.x)) that affects the database schema, exports of the schema (with or without data) are not able to be imported into an older version of SQL Server, such as SQL Server 2012 (11.x) or SQL Server 2014 (12.x). For example, export to a DACPAC or a BACPAC from an SQL Database or SQL Server 2016 (13.x) database that used this new feature would not be able to be imported into a down-level server.
Examples
These examples demonstrate the use of ALTER DATABASE SCOPED CONFIGURATION
A. Grant Permission
This example grant permission required to execute ALTER DATABASE SCOPED CONFIGURATION to user Joe.
SQL
GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to [Joe] ;
B. Set MAXDOP
This example sets MAXDOP = 1 for a primary database and MAXDOP = 4 for a secondary database in a geo-replication scenario.
SQL
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1 ;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 4 ;
This example sets MAXDOP for a secondary database same as it is set for primary database in a geo-replication scenario.
SQL
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY ;
C. Set LEGACY_CARDINALITY_ESTIMATION
This example sets LEGACY_CARDINALITY_ESTIMATION to ON for a secondary database in a geo-replication scenario.
SQL
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = ON ;
This example sets LEGACY_CARDINALITY_ESTIMATION for a secondary database as it is for its primary database in a geo-replication scenario.
SQL
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY ;
D. Set PARAMETER_SNIFFING
This example sets PARAMETER_SNIFFING to OFF for a primary database in a geo-replication scenario.
SQL
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF ;
This example sets PARAMETER_SNIFFING to OFF for a secondary database in a geo-replication scenario.
SQL
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF ;
This example sets PARAMETER_SNIFFING for secondary database as it is on primary database in a geo-replication scenario.
SQL
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY ;
Reference: Microsoft Documentation