SQL Server 2016: new database-scoped configuration parameters part 2

In this blog post, let’s continue with other database-scoped options shipped with the SQL Server 2016 RC0.

We will first begin with the first one and my preferred option: setting the MAXDOP value for an individual database. I really hope that applications like SharePoint that requires to limit the MAXDOP to 1 at the server level will use this kind of options in the future. As a reminder, changing this configuration parameter at the server level may have a huge impact on other applications and in most cases we end up dedicating the entire instance for the concerned application.

First, let’s take a look at the MAXDOP setting at the server level on my lab environment:

SELECT 
	name as configuration_setting,
	value_in_use
FROM sys.configurations
WHERE name IN ('cost threshold for parallelism', 'max degree of parallelism');
GO

So, basically any query with a cost greater than 5 may be candidate to parallel execution plans with a maximum of 4 processors in my case.

Let’s first disable parallelism only for the DB_2016 database by using this command syntax:

USE DB_2016;
GO

ALTER DATABASE SCOPED CONFIGURATION 
SET MAXDOP = 1;
GO

Now let’s verify the state of this parameter by using the new sys.database_scoped_configurations DMV:

SELECT 
	configuration_id, 
	name, 
	[value] AS [value_for_primary], 
	value_for_secondary
FROM sys.database_scoped_configurations 
OPTION (RECOMPILE);

You have probably noticed the last two columns. According to the Microsoft documentation here, it means that we may enable or disable database-scoped configuration settings either for AlwaysOn primary or secondary replicas. Well, very interesting … and I will probably cover this specific topic in a future blog post but at this moment let’s focus only on the database-scoped capabilities.

So it’s time to execute the following testing query:

SELECT COUNT(*)
FROM dbo.test_2016

And here the related execution plan generated by SQL Server:

This is a serial plan as you may see above. However, according to the estimated subtree Cost, we would expect to a parallel execution plan but if we take a look at the NonParallelPlanReason property we may notice clearly why no parallel plan has been used in this case. Now, let’s imagine a scenario where with one hand you want to disable parallelism on a AlwaysOn primary replica and on other hand you want to benefit to parallelism for reporting queries on the AlwaysOn readable secondary … The response in a next blog post J

So let’s continue and let’s first go back to the previous output result from the new sys.database_scoped_configurations DMV. You may see other interesting parameters as PARAMETER_SNIFFING or LEGACY_CARDINALITY_ESTIMATION. I remembered a performance audit at one of my customers where the trace flag 4136 was enabled on the audited SQL Server instance. Enabling this trace flag may be a good approach to solve parameter sniffing issues in some circumstances but the main drawback is the scope that may be too large and may impact other databases. The new database-scoped PARAMETER_SNIFFING parameter will probably address this kind of scenario.

Furthermore, enabling the LEGACY_CARDINALITY_ESTIMATION database-scoped parameter is equivalent to enabling the trace flag 9481 either at the server instance level, at the session level or finally at the query level by using the QUERYTRACEON hint. Another way to disable new cardinality estimator was to switch to compatibility level 120 or earlier to force SQL Server using cardinality estimator version 70. Many ways that may be replaced by only one at the database level.

Then, we have now the possibility to enable query optimization hotfixes for an individual database that is equivalent to use the trace flag 4199 at the server level. You may refer to this Microsoft KB (2801413) to get a complete list of trace flags implicitly enabled when enabling the global trace flag 4199.

Let’s finish with the CLEAR PROCEDURE_CACHE database-scoped parameter. The good news is that we are now able to clear the procedure cache for a specific database and eventually on AlwaysOn environments either on the primary or the secondaries.

Let’s perform a quick test … but before let’s have a look at the number of entries for each concerned database from the sys.dm_exec_cached_plans DMV:

USE master;
GO

SELECT 
	d.name AS [db_name],
	count(*) as nb_cached_entries
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY (
	SELECT CAST(pa.value AS INT) AS database_id
	FROM sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
	WHERE pa.attribute = N'dbid'
) AS DB
INNER JOIN sys.databases AS d
	ON d.database_id = DB.database_id
GROUP BY d.name
ORDER BY d.name

Now, let’s try to clear the procedure cache for the DB_2016 database by using the new following syntax:

USE DB_2016;
GO

ALTER DATABASE SCOPED CONFIGURATION 
CLEAR PROCEDURE_CACHE;
GO

And finally let’s check if all cached entries related to the DB_2016 database have been cleared correctly by executing the previous query based on the sys.dm_exec_cached_plans DMV:

Nice! Finally, we have a real command to clear the procedure cache at the database level!

From my point of view, this set of new database-scoped parameters are really welcome because the first advantage is that we get a better and more granular control or their scopes. The second advantage is that we have now a “standard way” to enable or disable these parameters compared to use trace flags at different levels.

So the next time I will write a blog post about using these parameters on SQL Server availability groups, so stay tuned!

By David Barbarin