This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2016 introduced Database Scoped Configuration Options that can set at the database level, which was previously available at the instance level only.
In SSMS, you can right click on database -> go to -> option -> find Database Scoped Configuration section.
It introduced main four parameters, which we can set at the database level.
Legacy Cardinality Estimation: It enables you to configure the query optimizer cardinality estimation model.
MAXDOP: Maximum degree of parallelism for an individual database.
Parameter Sniffing: Previously, we can disable this feature using trace flag 4136, but now you can ON/OFF Parameter Sniffing at the database level.
Query Optimizer Fixes: You can enable or disable query optimization hotfixes.
Clear Database Plan: It allows clearing procedure cache at the database level without impacting other databases.
SQL Server 2016 also introduced one DMV to check the current value of Database Scoped Configurations.
|
1 |
SELECT *FROM sys.database_scoped_configurations |
Database user requires Scoped Configuration permission to execute ALTER SCOPED CONFIGURATION.
|
1 |
GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to UserName; |
Related T-SQL Scripts:
Clear atabase Plan Cache:
|
1 |
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; |
Enable Legacy Cardinality Estimation:
|
1 |
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; |
Set MAXDOP Value:
|
1 |
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 3; |
Disable Parameter Sniffing:
|
1 |
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF; |
Enable Query Optimizer Fixes:
|
1 |
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON; |
Leave a Reply