LLI Wiki — My Digital Knowledge Base

Database scoped configuration: MAXDOP, cardinality and sniffing

ALTER DATABASE SCOPED CONFIGURATION settings (MAXDOP, cardinality estimator, parameter sniffing, query optimizer fixes) with their default values for SQL Server 2014 to 2019.

Since SQL Server 2016, database scoped configuration (ALTER DATABASE SCOPED CONFIGURATION) lets you set, for one database, options that used to be set for the whole instance:

Option Role
MAXDOP Degree of parallelism for the database
LEGACY_CARDINALITY_ESTIMATION Uses the legacy cardinality estimation model, regardless of the compatibility level (equivalent of trace flag 4136, limited to the database)
PARAMETER_SNIFFING Enables or disables parameter sniffing
QUERY_OPTIMIZER_HOTFIXES Enables query optimizer fixes (equivalent of trace flag 4199, limited to the database)
CLEAR PROCEDURE_CACHE Clears the plan cache of the database, without a DBCC command

Read the current configuration

-- Get database scoped configuration values for current database 
  SELECT configuration_id, name, [value] AS [value_for_primary], value_for_secondary
  FROM sys.database_scoped_configurations WITH (NOLOCK) OPTION (RECOMPILE);

Example of non-default settings

The following commands change the AppDb database. The FOR SECONDARY variants apply to the secondary databases.

USE [AppDb]
GO
-- Enable legacy Cardinality Estimation for Primary database
-- Equivalent to using TF 4136, except this is only for this database
ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO
-- Enable legacy Cardinality Estimation for Secondary database
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO
-- Set legacy Cardinality Estimation for the Secondary database(s) to the same value as the Primary database
-- This is the default setting
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
-- Disable parameter sniffing for Primary database
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
GO
-- Disable parameter sniffing for Secondary database
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF;
GO
-- Set parameter sniffing for the Secondary database(s) to the same value as the Primary database
-- This is the default setting
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY    
SET PARAMETER_SNIFFING = PRIMARY;
GO
-- Enable query optimizer fixes for Primary database
-- Equivalent to using TF 4199, except this is only for this database
ALTER DATABASE SCOPED CONFIGURATION 
SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO
-- Enable query optimizer fixes for Secondary database
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO
-- Set MAXDOP for Primary database
ALTER DATABASE SCOPED CONFIGURATION 
SET MAXDOP = 4;
GO
-- Set MAXDOP for Secondary database(s)
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
SET MAXDOP = 4;
GO
-- Set MAXDOP for the Secondary database(s) to the same value as the Primary database
-- This is the default setting
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
SET MAXDOP = PRIMARY; 
-- Clear plan cache for current database (only possible for Primary database)
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Default values (SQL Server 2014 to 2019)

USE [AppDb]
GO
-- Disable legacy Cardinality Estimation for Primary database
ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
-- Disable legacy Cardinality Estimation for Secondary database
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
-- Set legacy Cardinality Estimation for the Secondary database(s) to the same value as the Primary database
-- This is the default setting
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
-- Enable parameter sniffing for Primary database
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
-- Enable parameter sniffing for Secondary database
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = ON;
GO
-- Set parameter sniffing for the Secondary database(s) to the same value as the Primary database
-- This is the default setting
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY    
SET PARAMETER_SNIFFING = PRIMARY;
GO
-- Disable query optimizer fixes for Primary database
ALTER DATABASE SCOPED CONFIGURATION 
SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
-- Disable query optimizer fixes for Secondary database
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
-- Set query optimizer fixes for the Secondary database(s) to the same value as the Primary database
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
-- Set MAXDOP for Primary database (0 = server setting)
ALTER DATABASE SCOPED CONFIGURATION 
SET MAXDOP = 0;
GO
-- Set MAXDOP for Secondary database(s)
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
SET MAXDOP = 0;
GO
-- Set MAXDOP for the Secondary database(s) to the same value as the Primary database
-- This is the default setting
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
SET MAXDOP = PRIMARY; 
-- Clear plan cache for current database (only possible for Primary database)
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Notes

The FOR SECONDARY commands apply to all secondary databases in an Always On availability group. Microsoft reference: ALTER DATABASE SCOPED CONFIGURATION.

Mastodon LLIMastodon SocialFosstodon