Configuration à portée base : MAXDOP, cardinalité et sniffing
Réglages ALTER DATABASE SCOPED CONFIGURATION (MAXDOP, estimateur de cardinalité, parameter sniffing, correctifs de l'optimiseur) avec leurs valeurs par défaut pour SQL Server 2014 à 2019.
Depuis SQL Server 2016, la configuration à portée base (ALTER DATABASE SCOPED CONFIGURATION) permet de régler au niveau d'une base des options auparavant définies pour l'instance entière :
| Option | Rôle |
|---|---|
MAXDOP |
Degré de parallélisme de la base |
LEGACY_CARDINALITY_ESTIMATION |
Utilise l'ancien modèle d'estimation de cardinalité, indépendamment du niveau de compatibilité (équivalent du drapeau de trace 4136, limité à la base) |
PARAMETER_SNIFFING |
Active ou désactive le sniffing des paramètres |
QUERY_OPTIMIZER_HOTFIXES |
Active les correctifs de l'optimiseur (équivalent du drapeau de trace 4199, limité à la base) |
CLEAR PROCEDURE_CACHE |
Vide le plan cache de la base, sans commande DBCC |
Lire la configuration courante
-- 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);
Exemple de réglages (valeurs hors défaut)
Les commandes suivantes modifient la base AppDb. Les variantes FOR SECONDARY s'appliquent aux bases secondaires.
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
Valeurs par défaut (SQL Server 2014 à 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
Les commandes FOR SECONDARY s'appliquent à toutes les bases secondaires d'un groupe de disponibilité Always On. Documentation Microsoft : ALTER DATABASE SCOPED CONFIGURATION.