LLI Wiki — My Digital Knowledge Base

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.

Mastodon LLIMastodon SocialFosstodon