how to check and configure DATABASE SCOPED PARAMETERS CONFIGURATION using SSMS in MSSQL SERVER
MSSQL ADMINISTRATION IN HINDI | MSSQL DBA CLASS IN HINDI | LEARN MSSQL IN HINDI sys.database_scoped_configurations (Transact-SQL)
When NULL is returned as the value for value_for_secondary, this means that the secondary is set to PRIMARY.
Database scoped configuration settings will be carried over with the database. This means that when a given database is restored or attached, the existing configuration settings remain.
which allows you to overcome the default related server’s configuration and configure each database with that setting to meet each database or application requirements. These new configurations can be isolated at the replica level too, where you can configure the primary replica with a specific setting and the secondary replica which is used to handle another workload type with another setting. Just test these setting on your test environment, then apply what you find it suitable on the production environment and enjoy the new Database Scoped Configuration feature.
Set the MAXDOP parameter to a recommended value (1,2, ...) for the primary database based on what works best for that particular workload, and set a different value for secondary replica databases used by reporting queries. For guidance on choosing a MAXDOP, review Configure the max degree of parallelism Server Configuration Option.
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.
Enable or disable the identity cache at the database level.
Enable or disable a compiled plan stub to be stored in cache when a batch is compiled for the first time.
Enable or disable collection of execution statistics for natively compiled Transact-SQL modules.
Enable or disable online by default options for DDL statements that support the ONLINE = syntax.
Enable or disable resumable by default options for DDL statements that support the RESUMABLE = syntax.
Enable or disable Intelligent query processing features.
Enable or disable accelerated plan forcing.
Enable or disable the auto-drop functionality of global temporary tables.
Enable or disable the lightweight query profiling infrastructure.
Enable or disable the new String or binary data would be truncated error message.
Enable or disable collection of last actual execution plan in sys.dm_exec_query_plan_stats.
Specify the number of minutes that a paused resumable index operation is paused before it is automatically aborted by the Database Engine.
Enable or disable waiting for locks at low priority for asynchronous statistics update.
Enable or disable uploading ledger digests to Azure Blob Storage or Azure Confidential Ledger. MSSQL SERVER ADMINISTRATION IN HINDI
