SQL Server 2016 introduces a new set of configurations that can be applied at the database level to control its performance and behavior. These configurations were previously applicable only at the SQL instance level before version SQL Server 2016, With the ability to configure settings at the database level, many databases that are hosted in the same instance can now be isolated from each other, with each database has its own customized configurations. The database level configurations allow us also to set different database configurations for the Always On Availability Groups primary and secondary databases to meet the different types of workloads.
A new T-SQL statement ALTER DATABASE SCOPED CONFIGURATION has been introduced to set these new database level configurations for a particular database. These database scoped configurations can be viewed from the Options tab of the Database Properties window as follows:
The first database scoped configuration that is used to override the server’s default setting for a specific database is the MAXDOP option. MAXDOP is used in SQL Server to configure the maximum degree of parallelism, which specifies the number of processors used by each parallel query, at the database level. The MAXDOP option can take numeric values such as 1,2,3…etc, in addition to the PRIMARY value, which assigns the MAXDOP of the primary database to the secondary one if the Always On Availability Group is configured. In order to be able to configure database scoped configurations, the user should be granted ALTER ANY DATABASE SCOPED CONFIGURATION permission on that database. The following statement allows our example user, Suheir, to configure the database level configurations:
|
1 2 3 |
GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to Suheir |
Let’s check the SQL Server instance level MAXDOP value as below by querying the sys.configurations system object:
|
1 2 3 4 5 |
SELECT name AS configName,value_in_use As ConfigValue FROM sys.configurations WHERE name IN ('max degree of parallelism','cost threshold for parallelism'); |
You will see that the server is configure to assign 2 processors for any query with cost more than 2:
If we run the below query that change the MAXDOP value of the SQLShackDemo database to use a single processor only:
|
1 2 3 4 5 |
USE SQLShackDemo GO ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP =1 ; |
And try to run a complex query with cost more than 2, you will find that the query generated using the APEXSQL Plan application will use a serial plan as the MAXDOP value 1 at the database level overrides the MAXDOP value 2 at the server level:
SQL Server allows you also to configure the MAXDOP value for the secondary database with value different from the value set to the primary server depending on the workload type performed on these replicas as follows:
|
1 2 3 |
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=4 ; |
Or set the MAXDOP value at the secondary database with the same primary database MAXDOP value if you are not sure what the primary value is, as below:
|
1 2 3 |
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=PRIMARY ; |
The second database scoped configuration is the LEGACY_CARDINALITY_ESTIMATION option, which is used to enable or disable the legacy query optimizer Cardinality Estimation model. The Cardinality Estimation, or simply CE, is used in SQL Server to predicate the number of rows that will be returned from your query, which will be used to generate the best plan for that query. In SQL Server 2014 and later, the Cardinality Estimation is updated to work well with the modern OLTP and OLAP workloads. If you are using SQL Server 2014 or later and find that the modern CE is not suiting your workload and impacting the queries performance, you can turn on the legacy CE simply without changing the database compatibility to previous SQL Server versions and lose all new enhancements in the new SQL Server versions using the below query, which is equivalent to turning on the Trace Flag 9481:
|
1 2 3 |
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=ON ; |
You can also configure the secondary database in the Always On Availability Groups with a value different from the primary database if there is a different workload type assigned to that database as below:
|
1 2 3 |
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=OFF ; |
Or assign it with the same value as the primary database:
|
1 2 3 4 |
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=PRIMARY ; |
The third database scoped configuration is the PARAMETER_SNIFFING option. When we have many common queries that have the same shape, the parameters will be useful to improve its performance to create one plan for all these queries rather than compiling each one of these queries, Improving the overall SQL Server performance. SQL Server tries to change the queries with no parameters into parameterized queries to take advantage of this performance enhancement. But in some cases with complex queries, the parameterizing may cause performance issues, where the plan that is suitable for one parameter will not fit the other parameters, so that SQL Server Engine performs a process in which it uses the parameter value to estimate the selectivity and cardinality. This process is called the Parameter Sniffing.
SQL Server allows you to enable and disable this feature at the database level. If you disable the PARAMETER_SNIFFING, you order the SQL Server Query Optimizer to use the SQL statistics instead of the initially provided input values. This process is equivalent to using the Trace Flag 4136. You can also disable it for your query by adding the OPTIMIZE FOR UNKNOWN query hint.
The below query will disable the PARAMETER_SNIFFING at the SQLShackDemo database level:
|
1 2 3 |
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING =OFF ; |
You can also disable this option on the Always On Availability Group secondary database to meet the secondary workload requirements:
|
1 2 3 |
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING=OFF ; |
Or assign it with the same setting as the primary replica:
|
1 2 3 |
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING =PRIMARY ; |
Enabling the fourth database scoped configuration QUERY_OPTIMIZER_HOTFIXES allows you to take advantages of the latest hotfixes for the SQL Server Query Optimizer, which is disabled by default. This will work same as enabling the Trace Flag 4199 in your SQL Server.
The below query is used to enable your database to work with the latest hotfixes of the SQL Server Query Optimizer:
|
1 2 3 |
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON ; |
With the ability to set the QUERY_OPTIMIZER_HOTFIXES value on the secondary database to be same as the primary database as follows:
|
1 2 3 |
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES =PRIMARY |
The last database scoped configuration that is not mentioned within the Database Properties Options tab and can be configured only using the ALTER DATABASE T-SQL statement is the CLEAR PROCEDURE_CACHE option. This option allows you to clear the procedures cache at the current database only. The CLEAR PROCEDURE_CACHE option will affect the current SQL cache memory status without having any options to be assigned to it, this is why you will not find it within the SSMS database configurations.
If we run the below query to return the number of objects in the plan cache per each database before cleaning the cache for the AdventureWorksDW2012 database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE master; GO SELECT DBS.name AS DatabaseName,count(*) as NumOfcacheEntries FROM sys.dm_exec_cached_plans AS C_Plan CROSS APPLY ( SELECT CAST(Plan_Attr.value AS INT) AS dbs_id FROM sys.dm_exec_plan_attributes(C_Plan.plan_handle) AS Plan_Attr WHERE Plan_Attr.attribute = N'dbid' ) AS All_DataBases INNER JOIN sys.databases AS DBS ON DBS.database_id = All_DataBases.dbs_id GROUP BY DBS.name ORDER BY DatabaseName asc |
The result in our case will be like:
The below query will clear the procedure cache at the AdventureWorksDW2012 database level only, with no option to perform that on the secondary database:
|
1 2 3 4 5 |
USE AdventureWorksDW2012 GO ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ; |
If you check the number of cache entries again, you will find that the previous cache cleanup query take effects only at the AdventureWorksDW2012 database level only as in the below result:
A new DMV sys.database_scoped_configurations introduced in SQL Server 2016 to retrieve the database scoped configurations values for both the primary and secondary replicas of the current database:
|
1 2 3 |
SELECT * FROM sys.database_scoped_configurations; |
The SQLShackDemo database scoped configuration will be like:
Where the NULL values for the secondary means that the primary replica values will be used.
Conclusion
SQL Server 2016 comes with many new features and enhancements to the current features. One of the new enhancements in SQL Server 2016 is the new Database Scoped Configuration; 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.
Useful Links
- Database Scoped Configuration
- Cardinality Estimation (SQL Server)
- I Smell a Parameter!
- sys.dm_exec_cached_plans (Transact-SQL)
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021