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.