The new ALTER Database SCOPED configuration (Transact-SQL) is used to configure the database-level configuration. This statement can configure the configuration of each database:
- Cleanup process Cache
- Set the MAXDOP parameter to configure primary and secondary
- Set the evaluation mode for the query optimizer
- Starting and shutting down database-level parameter sniffing
- Start and close database-level query optimization patches
- To start and close the identity cache at the database level
Syntax:ALTER DATABASE SCOPED CONFIGURATION {{[for secondary] SET <set_options>}} | CLEAR Procedure_cache | SET < set_options >[;] < Set_options >:: = {MAXDOP = {<value> | | PRIMARY} | Legacy_cardinality_estimation = {on | OFF | PRIMARY} | parameter_sniffing = {on | OFF | PRIMARY} | query_optimizer_hotfixes = {on | OFF | PRIMARY} | Identity_cache = {on | OFF}}
Parameters:For secondary specifies the database to set secondary MAXDOP = {<value> | PRIMARY} set MAXDOP, the default is 0, if you set the database execution will overwrite the configuration in sp_configure, if there are query hints, query hints will overwrite this configuration. Primary means that if primary is set on secondary, then secondary integrates the settings on the primary. Legacy_cardinality_estimation = {on | OFF | PRIMARY} allows you to set the query optimizer evaluation mode if off will use the query optimizer evaluation mode based on compatibility level if the old query optimizer is used for on. Primary is the same as above parameter_sniffing = {on | OFF | PRIMARY} startup and shutdown parameters sniffer query_optimizer_hotfixes = {on | OFF | PRIMARY} starts and closes the query optimization patch, regardless of the database compatibility level. The default is off, turn off query optimization patches, and see Microsoft Support article for patches specifically. The clear Procedure_cache cleanup process schedule can be executed in primary,secondary. Identity_cache = {on | Off} to start and close the identity cache at the database level. By default, the On,identity cache is used to improve the performance of insert in identity. To avoid an identity gap, you can choose to close.
SQL Server 2016 new features: database-level configuration
