ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Base de données Azure SQL Azure SQL Managed Instance Azure Synapse Analytics

Cette commande active plusieurs paramètres de configuration de base de données au niveau de la base de données individuelle.

Les paramètres suivants sont pris en charge dans Azure SQL Database, Azure SQL Managed Instance et SQL Server comme indiqué par la ligne S’applique à de chaque paramètre de la section Arguments :

• Effacer le cache de procédures.
• Affecter au paramètre MAXDOP une valeur recommandée (1, 2, etc.) pour la base de données primaire en fonction de ce qui fonctionne le mieux pour cette charge de travail particulière, puis affecter une autre valeur pour les bases de données répliques secondaires utilisées par les requêtes de rapport. Pour obtenir des conseils sur le choix d'un paramètre MAXDOP, consultez Configurer l'option de configuration de serveur max degree of parallelism.
• Définir le modèle d’estimation de la cardinalité de l’optimiseur de requête indépendant de la base de données au niveau de compatibilité.
• Activer ou désactiver la détection de paramètres au niveau de la base de données.
• Activer ou désactiver les correctifs d’optimisation des requêtes au niveau de la base de données.
• Activer ou désactiver le cache d’identité au niveau de la base de données
• Activer ou désactiver un stub de plan compilé à stocker dans le cache lorsqu’un lot est compilé pour la première fois
• Activer ou désactiver la collecte de statistiques d’exécution pour les modules Transact-SQL compilés en mode natif.
• Activer ou désactiver les options par défaut « online » pour les instructions DDL qui prennent en charge la syntaxe ONLINE =.
• Activer ou désactiver les options par défaut « resumable » pour les instructions DDL qui prennent en charge la syntaxe RESUMABLE =.
• Activer ou désactiver les fonctionnalités de traitement de requêtes intelligent.
• Activer ou désactiver le forçage du plan accéléré.
• Activez ou désactivez la fonctionnalité d’écoute automatique des tables temporaires globales.
• Activer ou désactiver l’infrastructure de profilage de requête léger.
• Activer ou désactiver le nouveau message d’erreur String or binary data would be truncated.
• Active ou désactive la collection du dernier plan d’exécution actuel dans sys.dm_exec_query_plan_stats.
• Spécifiez le nombre de minutes pendant lesquelles une opération d’index pouvant être reprise est suspendue avant qu’elle ne soit automatiquement abandonnée par l’Moteur de base de données.
• Activer ou désactiver l’attente des verrous en priorité basse pour la mise à jour asynchrone des statistiques.
• Activez ou désactivez le chargement des synthèses de registre dans Stockage Blob Azure.

Ce paramètre est disponible seulement dans Azure Synapse Analytics.

• Définir le niveau de compatibilité d’une base de données utilisateur

Conventions de la syntaxe Transact-SQL

Syntaxe

-- Syntax for SQL Server, Azure SQL Database and Azure SQL Managed Instance

ALTER DATABASE SCOPED CONFIGURATION
{
    { [ FOR SECONDARY] SET <set_options>}
}
| CLEAR PROCEDURE_CACHE [plan_handle]
| SET < set_options >
[;]

< set_options > ::=
{
    MAXDOP = { <value> | PRIMARY}
    | LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}
    | PARAMETER_SNIFFING = { ON | OFF | PRIMARY}
    | QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}
    | IDENTITY_CACHE = { ON | OFF }
    | INTERLEAVED_EXECUTION_TVF = { ON | OFF }
    | BATCH_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }
    | BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF }
    | TSQL_SCALAR_UDF_INLINING = { ON | OFF }
    | ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
    | ELEVATE_RESUMABLE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }
    | OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }
    | XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }
    | XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }
    | ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }
    | MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = { ON | OFF }
    | MEMORY_GRANT_FEEDBACK_PERSISTENCE = { ON | OFF }
    | BATCH_MODE_ON_ROWSTORE = { ON | OFF }
    | DEFERRED_COMPILATION_TV = { ON | OFF }
    | ACCELERATED_PLAN_FORCING = { ON | OFF }
    | GLOBAL_TEMPORARY_TABLE_AUTO_DROP = { ON | OFF }
    | LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF }
    | VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }
    | LAST_QUERY_PLAN_STATS = { ON | OFF }
    | PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES = <time>
    | ISOLATE_SECURITY_POLICY_CARDINALITY  = { ON | OFF }
    | EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS = { ON | OFF }
    | ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = { ON | OFF }
    | OPTIMIZED_PLAN_FORCING = { ON | OFF }
    | DOP_FEEDBACK = { ON | OFF }
    | CE_FEEDBACK = { ON | OFF }
    | PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = { ON | OFF }
    | LEDGER_DIGEST_STORAGE_ENDPOINT = { <endpoint URL string> | OFF }
    | OPTIMIZED_SP_EXECUTESQL = { ON | OFF }
}

-- Syntax for Azure Synapse Analytics

ALTER DATABASE SCOPED CONFIGURATION
{
    SET <set_options>
}
[;]

< set_options > ::=
{
    DW_COMPATIBILITY_LEVEL = { AUTO | 10 | 20 | 30 | 40 | 50 | 9000 }
}

Arguments

FOR SECONDARY

Spécifie les paramètres des bases de données secondaires (toutes les bases de données secondaires doivent avoir des valeurs identiques).

CLEAR PROCEDURE_CACHE [plan_handle]

Efface le cache (du plan) de procédure pour la base de données et peut être exécuté sur les bases de données primaires et secondaires.

Spécifiez un descripteur de plan de requête pour effacer un seul plan de requête du cache de plan.

S’applique à : la spécification d’un descripteur de plan de requête est disponible à compter de SQL Server 2019 (15.x) dans Azure SQL Database et Azure SQL Managed Instance.

MAXDOP = {<valeur> | PRIMARY }

<value>

Spécifiez le degré maximal de parallélisme (MAXDOP) par défaut qui doit être utilisé pour les instructions. 0 est la valeur par défaut, et indique que la configuration du serveur doit être utilisée. MaxDOP au niveau de la base de données remplace (sauf s’il est défini sur 0) le degré maximal de parallélisme défini au niveau du serveur par sp_configure. Les indicateurs de requête peuvent tout de même remplacer le paramètre MAXDOP défini au niveau de la base de données afin de configurer les requêtes qui nécessitent un paramétrage différent. Tous ces paramètres sont limités par le degré maximal de parallélisme (MAXDOP) défini pour le groupe de charge de travail.

Vous pouvez utiliser l'option MAXDOP pour limiter le nombre de processeurs à utiliser dans une exécution de plans parallèles. SQL Server prend en compte les plans d’exécution parallèle pour les requêtes, les opérations du langage de définition de données (DDL) d’index, l’insertion parallèle, la modification de colonne en ligne, la collecte de statistiques parallèle et l’alimentation des curseurs statiques et de jeux de clés.

Pour définir cette option au niveau de l’instance, consultez Configurer l’option de configuration du serveur max degree of parallelism.

PRIMARY

Peut uniquement être défini pour les bases de données secondaires lorsque la base de données est définie sur PRIMARY, et indique que la configuration est celle définie pour la base de données primaire. Si la configuration de la base de données primaire est modifiée, la valeur des bases de données secondaires est modifiée en conséquence, sans que vous ayez à la définir explicitement. PRIMARY est le paramètre par défaut des bases de données secondaires.

LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

Permet de définir le modèle d’estimation de la cardinalité de l’optimiseur de requête sur SQL Server 2012 ou antérieur selon le niveau de compatibilité de la base de données. La valeur par défaut est OFF, ce qui définit le modèle d’estimation de la cardinalité de l’optimiseur de requête en fonction du niveau de compatibilité de la base de données. Définir LEGACY_CARDINALITY_ESTIMATION sur ON équivaut à activer indicateur de Trace 9481.

PRIMARY

Cette valeur est valide uniquement pour les bases de données secondaires lorsque la base de données est définie sur PRIMARY, et indique que le paramètre du modèle d’estimation de la cardinalité de l’optimiseur de requête de toutes les bases de données secondaires est défini sur la valeur de la base de données primaire. Si la configuration du modèle d’estimation de la cardinalité de l’optimiseur de requête qui est définie dans la base de données primaire est modifiée, la valeur des bases de données secondaires est modifiée en conséquence. PRIMARY est le paramètre par défaut des bases de données secondaires.

PARAMETER_SNIFFING = { ON | OFF | PRIMARY }

Active ou désactive la détection de paramètres. La valeur par défaut est ON. La définition de PARAMETER_SNIFFING sur ON équivaut à activer l’indicateur de trace 4136.

PRIMARY

Cette valeur est valide uniquement pour les bases de données secondaires lorsque la base de données est définie sur PRIMARY, et indique que, sur toutes les bases de données secondaires, ce paramètre est défini sur la valeur de la base de données primaire. Si la configuration de la détection de paramètres est modifiée sur la base de données primaire, la valeur des bases de données secondaires est modifiée en conséquence, sans que vous ayez à la définir explicitement. PRIMARY est le paramètre par défaut des bases de données secondaires.

QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }

Active ou désactive les correctifs logiciels d’optimisation de requête, quel que soit le niveau de compatibilité de la base de données. La valeur par défaut est OFF, laquelle désactive les correctifs logiciels d’optimisation des requêtes qui ont été publiés après l’arrivée du plus haut niveau de compatibilité d’une version donnée (post-RTM). L’utilisation de la valeur ON équivaut à activer l’indicateur de trace 4199.

S’applique à : SQL Server (à compter de SQL Server 2016 (13.x)), Azure SQL Database et Azure SQL Managed Instance

PRIMARY

Cette valeur est valide uniquement pour les bases de données secondaires lorsque la base de données est définie sur PRIMARY, et indique que, sur toutes les bases de données secondaires, ce paramètre est défini sur la valeur de la base de données primaire. Si la configuration de la base de données primaire est modifiée, la valeur des bases de données secondaires est modifiée en conséquence, sans que vous ayez à la définir explicitement. PRIMARY est le paramètre par défaut des bases de données secondaires.

IDENTITY_CACHE = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)), Azure SQL Database et Azure SQL Managed Instance

Active ou désactive le cache d’identité au niveau de la base de données. La valeur par défaut est ON. La mise en cache d’identité est utilisée pour améliorer les performances INSERT sur les tables comprenant des colonnes d’identité. Pour éviter les écarts dans les valeurs des colonnes d’identité si un serveur redémarre de façon inattendue ou bascule vers un serveur secondaire, désactivez l’option IDENTITY_CACHE. Cette option est similaire à l’indicateur de trace 272 existant, sauf qu’elle peut être définie au niveau de la base de données et non uniquement au niveau du serveur.

INTERLEAVED_EXECUTION_TVF = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Vous permet d’activer ou de désactiver l’exécution entrelacée pour les fonctions table à instructions multiples dans l’étendue de la base de données ou de l’instruction, tout en maintenant le niveau de compatibilité de base de données 140 et au-delà. La valeur par défaut est ON. L’exécution entrelacée est une fonctionnalité qui fait partie du traitement de requêtes adaptatif dans Azure SQL Database. Pour plus d’informations, consultez Traitement de requêtes intelligent.

BATCH_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Vous permet d’activer ou de désactiver la rétroaction d’allocation de mémoire en mode batch dans l’étendue de la base de données tout en maintenant le niveau de compatibilité de la base de données à au moins 140. La valeur par défaut est ON. La rétroaction d’allocation de mémoire en mode batch, introduite dans SQL Server 2017 (14.x), fait partie de la suite de fonctionnalités du traitement de requêtes intelligent. Pour plus d’informations, consultez Rétroaction d’allocation de mémoire.

BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Vous permet d’activer ou de désactiver les jointures adaptatives en mode batch dans l’étendue de la base de données tout en maintenant le niveau de compatibilité de la base de données à au moins 140. La valeur par défaut est ON. Les jointures adaptatives en mode batch sont une fonctionnalité qui fait partie du traitement de requêtes intelligent introduit dans SQL Server 2017 (14.x).

TSQL_SCALAR_UDF_INLINING = { ON | OFF }

S’applique à : SQL Server (à partir de SQL Server 2019 (15.x)) et Azure SQL Database (fonctionnalité en préversion)

Vous permet d’activer ou de désactiver l’incorporation (inlining) des fonctions UDF scalaires T-SQL dans l’étendue de la base de données tout en maintenant le niveau de compatibilité de la base de données à au moins 150. La valeur par défaut est ON. L’incorporation (inlining) des fonctions UDF scalaires T-SQL fait partie de la famille des fonctionnalités de traitement de requêtes intelligent.

ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Permet de sélectionner les options destinées à forcer le moteur à élever automatiquement les opérations prises en charge pour une exécution en ligne. La valeur par défaut est OFF, ce qui signifie que les opérations ne sont pas élevées pour une exécution en ligne, sauf si cela est spécifié dans l’instruction. sys.database_scoped_configurations reflète la valeur actuelle de ELEVATE_ONLINE. Ces options s’appliquent uniquement aux opérations prises en charge pour une exécution en ligne.

FAIL_UNSUPPORTED

Cette valeur élève toutes les opérations DDL prises en charge pour une exécution en ligne (option ONLINE). Les opérations qui ne prennent pas en charge l’exécution en ligne échouent et lèvent une erreur.

WHEN_SUPPORTED

Cette valeur élève les opérations qui prennent en charge l’option ONLINE. Les opérations qui ne prennent pas en charge une exécution en ligne sont exécutées en mode hors connexion.

ELEVATE_RESUMABLE= { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Permet de sélectionner des options pour forcer le moteur à élever automatiquement les opérations prises en charge pour une exécution pouvant être reprise. La valeur par défaut est OFF, ce qui signifie que les opérations ne sont pas élevées pour une exécution pouvant être reprise, sauf si cela est spécifié dans l’instruction. sys.database_scoped_configurations reflète la valeur actuelle de ELEVATE_RESUMABLE. Ces options s’appliquent uniquement aux opérations prises en charge pour une exécution pouvant être reprise.

FAIL_UNSUPPORTED

Cette valeur élève toutes les opérations DDL prises en charge pour une exécution pouvant être reprise (option RESUMABLE). Les opérations qui ne prennent pas en charge l’exécution pouvant être reprise échouent et génèrent une erreur.

WHEN_SUPPORTED

Cette valeur élève les opérations qui prennent en charge l’option RESUMABLE. Les opérations qui ne prennent pas en charge la reprise ne sont pas exécutées de manière non réinsérisible.

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Active ou désactive un stub de plan compilé à stocker dans le cache lorsqu’un lot est compilé pour la première fois. La valeur par défaut est OFF. Une fois que la configuration étendue à la base de données OPTIMIZE_FOR_AD_HOC_WORKLOADS est activée pour une base de données, un stub de plan compilé est stocké dans le cache lorsqu’un lot est compilé pour la première fois. Les stubs de plan ont un encombrement mémoire moins important que celui des plans compilés complets. Si un lot est compilé ou réexécuté, le stub de plan compilé est supprimé et remplacé par un plan compilé complet.

XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }

S’applique à : Azure SQL Database et Azure SQL Managed Instance

Active ou désactive la collecte de statistiques d’exécution au niveau du module pour les modules T-SQL compilés en mode natif dans la base de données actuelle. La valeur par défaut est OFF. Les statistiques d’exécution sont disponibles dans sys.dm_exec_procedure_stats.

Les statistiques d’exécution au niveau du module pour les modules T-SQL compilés en mode natif sont collectées si cette option est activée (ON) ou si la collecte des statistiques est activée avec sp_xtp_control_proc_exec_stats.

XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }

S’applique à : Azure SQL Database et Azure SQL Managed Instance

Active ou désactive la collecte de statistiques d’exécution au niveau de l’instruction pour les modules T-SQL compilés en mode natif dans la base de données actuelle. La valeur par défaut est OFF. Les statistiques d’exécution sont disponibles dans sys.dm_exec_query_stats et dans le magasin des requêtes.

Les statistiques d’exécution au niveau de l’instruction pour les modules T-SQL compilés en mode natif sont collectées si cette option est activée (ON) ou si la collecte des statistiques est activée avec sp_xtp_control_query_exec_stats.

Pour plus d’informations sur l’analyse des performances des modules Transact-SQL compilés en mode natif, consultez Surveillance des performances des procédures stockées compilées en mode natif.

ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Vous permet d’activer ou de désactiver la rétroaction d’allocation de mémoire en mode ligne dans l’étendue de la base de données tout en maintenant le niveau de compatibilité de la base de données à au moins 150. La valeur par défaut est ON. La rétroaction d’allocation de mémoire en mode ligne est une fonctionnalité qui fait partie du traitement de requêtes intelligent introduit dans SQL Server 2017 (14.x). Le mode ligne est pris en charge dans SQL Server 2019 (15.x) et Azure SQL Database. Pour plus d’informations, consultez Rétroaction d’allocation de mémoire.

MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = { ON | OFF }

S’applique à : SQL Server (à partir de SQL Server 2022 (16.x)) et Azure SQL Database

Vous permet de désactiver le centile de la rétroaction d’allocation de mémoire dans toutes les exécutions de requête provenant de la base de données. La valeur par défaut est ON. Pour obtenir des informations complètes, consultez Rétroaction d’allocation de mémoire en mode centile et persistance.

MEMORY_GRANT_FEEDBACK_PERSISTENCE = { ON | OFF }

S’applique à : SQL Server 2022 (16.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Vous permet de désactiver la persistance de la rétroaction d’allocation de mémoire dans toutes les exécutions de requête provenant de la base de données. La valeur par défaut est ON. Pour obtenir des informations complètes, consultez Rétroaction d’allocation de mémoire en mode centile et persistance.

BATCH_MODE_ON_ROWSTORE = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Vous permet d’activer ou de désactiver le mode batch sur rowstore dans l’étendue de la base de données tout en maintenant le niveau de compatibilité de la base de données à au moins 150. La valeur par défaut est ON. Le mode batch sur rowstore est une fonctionnalité qui fait partie de la famille de fonctionnalités de traitement de requêtes intelligent.

DEFERRED_COMPILATION_TV = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Vous permet d’activer ou de désactiver la compilation différée de variables de table dans l’étendue de la base de données tout en maintenant le niveau de compatibilité de la base de données à au moins 150. La valeur par défaut est ON. La compilation différée de variables de table est une fonctionnalité qui fait partie de la famille de fonctionnalités de traitement de requêtes intelligent.

ACCELERATED_PLAN_FORCING = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Active un mécanisme optimisé pour le forçage du plan de requête, applicable à toutes les formes de forçage de plan, comme le plan de forçage du magasin de requêtes, l’optimisation automatique ou l’indicateur de requête USE PLAN. La valeur par défaut est ON.

GLOBAL_TEMPORARY_TABLE_AUTO_DROP = { ON | OFF }

S’applique à : Azure SQL Database et Azure SQL Managed Instance

Permet de définir la fonctionnalité d’écoute automatique pour les tables temporaires globales. La valeur par défaut est ON, ce qui signifie que les tables temporaires globales sont automatiquement supprimées quand aucune session ne les utilise. Lorsqu’elles sont définies sur OFF, les tables temporaires globales doivent être explicitement supprimées à l’aide d’une DROP TABLE instruction ou sont automatiquement supprimées lors du redémarrage du serveur.

• Avec les bases de données uniques/pools élastiques Azure SQL Database, cette option peut être définie dans les bases de données utilisateur individuelles du serveur SQL Database.
• Dans SQL Server et Azure SQL Managed Instance, cette option est définie dans tempdb et le paramètre des bases de données utilisateur individuelles n’a aucun effet.

LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Vous permet d’activer ou de désactiver l’infrastructure de profilage de requête léger. L’infrastructure de profilage de requête léger (LWP) fournit les données de performances de requête plus efficacement que les mécanismes de profilage standard et est activée par défaut. La valeur par défaut est ON.

VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Vous permet d’activer ou de désactiver le nouveau message d’erreur String or binary data would be truncated. La valeur par défaut est ON. SQL Server 2019 (15.x) introduit un nouveau message d’erreur (2628), plus spécifique, dans ce scénario :

String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'.

Quand sa valeur est définie sur ON pour un niveau de compatibilité de la base de données en dessous de 150, les erreurs de troncation déclenchent le nouveau message d’erreur 2628 pour fournir plus de contexte et simplifier le dépannage.

Quand sa valeur est définie sur OFF pour un niveau de compatibilité de la base de données en dessous de 150, les erreurs de troncation déclenchent l’ancien message d’erreur 8152.

Pour un niveau de compatibilité de la base de données égal ou inférieur à 140, le message d’erreur 2628 reste un message d’erreur d’activation qui nécessite l’activation de l’indicateur de trace 460 ; cette configuration au niveau de la de base de données n’a alors aucun effet.

LAST_QUERY_PLAN_STATS = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Permet d’activer ou désactiver la collection des statistiques du dernier plan de requête (équivalent à un plan d’exécution réel) dans sys.dm_exec_query_plan_stats. La valeur par défaut est OFF.

PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES

S’applique à : SQL Server 2022 (16.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

L’option PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES détermine la durée (en minutes) pendant laquelle l’index reprenable peut être mis en pause avant d’être automatiquement abandonné par le moteur.

• La valeur par défaut est définie sur un jour (1 440 minutes).
• La durée minimale est définie sur 1 minute
• La durée maximale est de 71 582 minutes.
• Lorsque la valeur est définie sur 0, les opérations suspendues ne sont jamais abandonnées automatiquement

La valeur actuelle de cette option s’affiche dans sys.database_scoped_configurations.

ISOLATE_SECURITY_POLICY_CARDINALITY = { ON | OFF}

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Vous permet de déterminer si un prédicat de sécurité au niveau des lignes (RLS) affecte la cardinalité du plan d’exécution de la requête utilisateur globale. La valeur par défaut est OFF. Si ISOLATE_SECURITY_POLICY_CARDINALITY est activé, un prédicat RLS n’affecte pas la cardinalité d’un plan d’exécution. Prenons l’exemple d’une table contenant 1 million de lignes et d’un prédicat RLS qui limite le résultat à 10 lignes pour l’utilisateur qui envoie la requête. Si la configuration étendue à la base de données est désactivée, l’estimation de la cardinalité de ce prédicat sera de 10. Lorsque cette configuration étendue à la base de données est ON, l’optimisation des requêtes estime 1 million de lignes. Il est recommandé d’utiliser la valeur par défaut pour la plupart des charges de travail.

DW_COMPATIBILITY_LEVEL = { AUTO | 10 | 20 | 30 | 40 | 50 | 9000 }

S’applique à : Azure Synapse Analytics uniquement

Définit les comportements de Transact-SQL et du traitement des requêtes pour qu’ils soient compatibles avec la version spécifiée du moteur de base de données. Une fois qu’elle est définie, lorsqu’une requête est exécutée sur cette base de données, seules les fonctionnalités compatibles sont exercices. À chaque niveau de compatibilité, différentes améliorations du traitement des requêtes sont prises en charge. Chaque niveau absorbe les fonctionnalités du niveau précédent. Le niveau de compatibilité d’une base de données est défini par défaut sur AUTO lors de sa création. Il s’agit du paramètre recommandé. Le niveau de compatibilité est conservé même après une les opérations d’interruption/reprise et de sauvegarde/restauration de la base de données. La valeur par défaut est AUTO.

Niveau de compatibilité	Commentaires
AUTO	Par défaut. Sa valeur est automatiquement mise à jour par le moteur Synapse Analytics et est représentée par 0 dans sys.database_scoped_configurations. AUTO mappe actuellement vers la fonctionnalité de niveau de compatibilité 30.
10	Met en œuvre les comportements de Transact-SQL et du moteur d’interrogation avant l’introduction de la prise en charge du niveau de compatibilité.
20	Premier niveau de compatibilité qui inclut les comportements contrôlés de Transact-SQL et du moteur d’interrogation. La procédure stockée système sp_describe_undeclared_parameters est prise en charge sous ce niveau.
30	Inclut les nouveaux comportements du moteur d’interrogation.
40	Inclut les nouveaux comportements du moteur d’interrogation.
50	La distribution multicolonne est prise en charge sous ce niveau. Pour plus d’informations, consultez CREATE TABLE, CREATE TABLE AS SELECT et CREATE MATERIALIZED VIEW.
9000	Niveau de compatibilité de la préversion. Les fonctionnalités en préversion contrôlées sous ce niveau sont mentionnées dans la documentation spécifique aux fonctionnalités. Ce niveau inclut aussi les fonctionnalités du niveau le plus haut non-9000.

EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS = { ON | OFF }

S’applique à : Azure SQL Database et Azure SQL Managed Instance

Permet de contrôler si les statistiques d’exécution pour les fonctions scalaires définies par l’utilisateur apparaissent dans la vue système sys.dm_exec_function_stats. Pour certaines charges de travail intensives qui sont lourdes de fonctions UDF scalaires, la collecte des statistiques d’exécution de fonction peut entraîner une surcharge de performances notable. Vous pouvez éviter cela en définissant la configuration de EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS délimitée à la base de données sur OFF. La valeur par défaut est ON.

ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2022 (16.x)), Azure SQL Database et Azure SQL Managed Instance

Si la mise à jour asynchrone des statistiques est activée, l’activation de cette configuration entraîne la mise à jour des statistiques en arrière-plan pour attendre un verrou sur une Sch-M file d’attente de faible priorité, afin d’éviter de bloquer d’autres sessions dans des scénarios de concurrence élevée. Pour plus d’informations, consultez AUTO_UPDATE_STATISTICS_ASYNC. La valeur par défaut est OFF.

OPTIMIZED_PLAN_FORCING = { ON | OFF }

S’applique à : SQL Server (à partir de SQL Server 2022 (16.x)) et Azure SQL Database

Le forçage de plan optimisé réduit la surcharge de compilation pour les requêtes forcées répétées. La valeur par défaut est ON. Une fois le plan d’exécution de requêtes généré, des étapes de compilation spécifiques sont stockées pour être réutilisées en tant que script de relecture d’optimisation. Un script de relecture d’optimisation est stocké dans le cadre du XML compressé du plan d’exécution de requêtes dans le Magasin des requêtes, dans un attribut OptimizationReplay caché. Découvrez-en plus sur le forçage de plan optimisé avec le Magasin des requêtes.

DOP_FEEDBACK = { ON | OFF }

S’applique à : SQL Server (à partir de SQL Server 2022 (16.x)) et Azure SQL Database

Identifie les inefficacités du parallélisme pour les requêtes répétées, en fonction du temps écoulé et des attentes. Si l’utilisation du parallélisme est jugée inefficace, la rétroaction DOP réduit le DOP pour la prochaine exécution de la requête, quel que soit le DOP configuré, et vérifie si cela a un effet positif. Nécessite que le Magasin des requêtes soit activé et en mode READ_WRITE. Pour plus d’informations, consultez Rétroaction de degré de parallélisme (DOP). La valeur par défaut est OFF.

CE_FEEDBACK = { ON | OFF }

S’applique à : SQL Server 2022 (16.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Les commentaires CE traitent des problèmes de régression perçus résultant d’hypothèses incorrectes du modèle CE lors de l’utilisation du CE par défaut (CE120 ou version ultérieure) et peuvent utiliser de manière sélective différentes hypothèses de modèle. Nécessite que le Magasin des requêtes soit activé et en mode READ_WRITE. Pour plus d’informations, consultez Rétroaction d’évaluation de la cardinalité (CE). La valeur par défaut est ON dans les niveaux de compatibilité de base de données 160 et supérieurs.

PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = { ON | OFF }

S’applique à : SQL Server 2022 (16.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

L’optimisation du plan de sensibilité des paramètres (PSP) traite le scénario dans lequel un seul plan mis en cache pour une requête paramétrable n’est pas optimal pour toutes les valeurs de paramètre entrantes possibles. C’est le cas avec les distributions de données non uniformes. La valeur par défaut est ON à partir du niveau de compatibilité de la base de données 160. Pour plus d’informations, consultez Optimisation du plan de confidentialité des paramètres.

LEDGER_DIGEST_STORAGE_ENDPOINT = { <chaîne d’URL de point de terminaison> | OFF }

S’applique à : SQL Server (à compter de SQL Server 2022 (16.x))

Active ou désactive le chargement des synthèses de registre dans Stockage Blob Azure. Pour activer le chargement des synthèses de registre, spécifiez le point de terminaison d’un compte Stockage Blob Azure. Pour désactiver le chargement des synthèses de registre, définissez la valeur d’option sur OFF. La valeur par défaut est OFF.

FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION = { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2022 (16.x)), Azure SQL Database et Azure SQL Managed Instance

Provoque la génération par SQL Server d’un fragment XML Showplan avec ParameterRuntimeValue lors de l’utilisation de l’infrastructure de profilage des statistiques d’exécution de requêtes légères ou l’exécution de la vue de gestion dynamique sys.dm_exec_query_statistics_xml lors de la résolution des problèmes de requêtes d’exécution longue.

OPTIMIZED_SP_EXECUTESQL = { ON | OFF }

S’applique à : Azure SQL Database

Active ou désactive le comportement de sérialisation de compilation de sp_executesql lorsqu’un lot est compilé. La valeur par défaut est OFF. Autoriser les lots qui utilisent sp_executesql pour sérialiser le processus de compilation est très efficace pour réduire l’impact des tempêtes de compilation lorsqu’il existe des compilations fréquentes et simultanées de requêtes adhoc qui tirent parti de la procédure stockée système sp_executesql. La première exécution de sp_executesql compile et insère son plan compilé dans le cache du plan. D’autres sessions abandonnent l’attente sur le verrou de compilation et réutilisent le plan une fois qu’il est disponible. Cela permet sp_executesql de se comporter comme des objets tels que des procédures stockées et des déclencheurs du point de vue de la compilation.

autorisations

Nécessite ALTER ANY DATABASE SCOPED CONFIGURATION sur la base de données. Cette autorisation peut être accordée par un utilisateur disposant de l’autorisation CONTROL sur une base de données.

Notes

Même si vous pouvez configurer des bases de données secondaires avec des paramètres différents de ceux de la base de données primaire, toutes les bases de données secondaires doivent utiliser la même configuration. Vous ne pouvez pas configurer des paramètres différents pour chaque base de données secondaire.

L’exécution de cette instruction efface le contenu du cache de procédures de la base de données actuelle, ce qui signifie que toutes les requêtes doivent être recompilées.

Pour les requêtes de noms en trois parties, les paramètres de la connexion de base de données actuelle pour la requête sont respectés, autres que pour les modules SQL (tels que les procédures, les fonctions et les déclencheurs) compilés dans un autre contexte de base de données et utilisent donc les options de la base de données dans laquelle elles résident. De même, lors de la mise à jour asynchrone des statistiques, le paramètre de la base de ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY données où résident les statistiques est respecté.

L’événement ALTER_DATABASE_SCOPED_CONFIGURATION est ajouté en tant qu’événement DDL qui peut être utilisé pour déclencher un déclencheur DDL, et il s’agit d’un enfant du groupe de déclencheurs ALTER_DATABASE_EVENTS.

Lorsqu’une base de données donnée est restaurée ou attachée, les paramètres de configuration délimités à la base de données sont transférés et restent avec la base de données.

À compter de SQL Server 2019 (15.x), dans Azure SQL Database et Azure SQL Managed Instance, certains noms d’options ont changé :

• DISABLE_INTERLEAVED_EXECUTION_TVF est devenu INTERLEAVED_EXECUTION_TVF
• DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK est devenu BATCH_MODE_MEMORY_GRANT_FEEDBACK
• DISABLE_BATCH_MODE_ADAPTIVE_JOINS est devenu BATCH_MODE_ADAPTIVE_JOINS

Limites

MAXDOP

Les paramètres granulaires peuvent remplacer les paramètres globaux, et le gouverneur de ressources peut limiter tous les autres paramètres MAXDOP. La logique du paramètre MAXDOP est la suivante :

• L’indicateur de requête remplace sp_configure et la configuration étendue à la base de données. Si le groupe de ressources MAXDOP est défini pour le groupe de charge de travail :

  • Si l’indicateur de requête est défini sur zéro (0), il est remplacé par le paramètre Resource Governor.

  • Si l’indicateur de requête n’est pas défini sur zéro (0), il est limité par le paramètre Resource Governor.

• La configuration étendue à la base de données (à moins d’être définie sur 0) remplace le paramètre sp_configure, sauf s’il existe un indicateur de requête et qu’il est limité par le paramètre Resource Governor.

• Le paramètre sp_configure est remplacé par le paramètre Resource Governor.

QUERY_OPTIMIZER_HOTFIXES

Quand l’indicateur QUERYTRACEON est utilisé pour activer l’optimiseur de requête par défaut de SQL Server 7.0 à SQL Server 2012 (11.x) ou les correctifs logiciels de l’optimiseur de requête, une condition OR lie l’indicateur de requête et le paramètre de configuration délimité à la base de données, ce qui signifie que si l’un des deux est activé, les configurations délimitées à la base de données s’appliquent.

Reprise d’activité géographique

Les bases de données secondaires accessibles en lecture (par exemple, les groupes de disponibilité Always On et les bases de données géorépliquées Azure SQL Database et Azure SQL Managed Instance) utilisent la valeur de la base de données secondaire en vérifiant l’état de la base de données. Même si la recompilation ne se produit pas lors du basculement et même si, techniquement, la nouvelle base de données primaire comprend des requêtes qui utilisent les paramètres des bases de données secondaires, l’idée est que le paramètre entre les bases de données primaires et secondaires varient uniquement lorsque la charge de travail est différente, et donc, que les requêtes mises en cache utilisent les paramètres optimaux, tandis que les nouvelles requêtes choisissent les nouveaux paramètres qui leur conviennent.

DacFx

ALTER DATABASE SCOPED CONFIGURATION étant une nouvelle fonctionnalité dans Azure SQL Database, Azure SQL Managed Instance et SQL Server (à compter de SQL Server 2016 (13.x)) qui affecte le schéma de base de données, les exportations du schéma (avec ou sans données) ne peuvent pas être importées dans une version antérieure de SQL Server, comme SQL Server 2012 (11.x) ou SQL Server 2014 (12.x). Par exemple, une exportation vers un DACPAC ou un BACPAC à partir d’une base de données SQL Database ou SQL Server 2016 (13.x) ayant utilisé cette nouvelle fonctionnalité ne peut pas être importée dans un serveur de niveau inférieur.

ELEVATE_ONLINE

Cette option s’applique uniquement aux instructions DDL qui prennent en charge la syntaxe WITH (ONLINE = <syntax>). Les index XML ne sont pas affectés.

ELEVATE_RESUMABLE

Cette option s’applique uniquement aux instructions DDL qui prennent en charge la syntaxe WITH (RESUMABLE = <syntax>). Les index XML ne sont pas affectés.

Métadonnées

La vue système sys.database_scoped_configurations (Transact-SQL) fournit des informations sur les configurations étendues à une base de données. Les options de configuration définies au niveau de la base de données s’affichent dans sys.database_scoped_configurations parce qu’elles remplacent les paramètres par défaut définis au niveau du serveur. La vue système sys.configurations (Transact-SQL) affiche uniquement les paramètres définis au niveau du serveur.

Exemples

Ces exemples illustrent l’utilisation de ALTER DATABASE SCOPED CONFIGURATION.

R. Accorder l’autorisation

Cet exemple accorde à l’utilisateur Joe l’autorisation nécessaire pour exécuter ALTER DATABASE SCOPED CONFIGURATION.

GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to [Joe] ;

B. Définir MAXDOP

Cet exemple définit MAXDOP = 1 pour une base de données primaire et MAXDOP = 4 pour la base de données secondaire dans un scénario de géoréplication.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1 ;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 4 ;

Cet exemple définit MAXDOP de sorte que sa valeur soit la même pour la base de données primaire et pour la base de données secondaire dans un scénario de géoréplication.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY ;

C. Définir LEGACY_CARDINALITY_ESTIMATION

Cet exemple définit LEGACY_CARDINALITY_ESTIMATION sur ON pour une base de données secondaire dans un scénario de géoréplication.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = ON ;

Cet exemple définit LEGACY_CARDINALITY_ESTIMATION de la même manière pour la base de données primaire et pour une base de données secondaire dans un scénario de géoréplication.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY ;

D. Définir PARAMETER_SNIFFING

Cet exemple définit PARAMETER_SNIFFING sur OFF pour une base de données primaire dans un scénario de géoréplication.

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF ;

Cet exemple définit PARAMETER_SNIFFING sur OFF pour une base de données secondaire dans un scénario de géoréplication.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF ;

Cet exemple définit PARAMETER_SNIFFING de la même manière pour la base de données primaire et pour une base de données secondaire dans un scénario de géoréplication.

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY ;

E. Définir QUERY_OPTIMIZER_HOTFIXES

Cet exemple définit QUERY_OPTIMIZER_HOTFIXES sur ON pour la base de données primaire dans un scénario de géoréplication.

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON ;

F. Effacer le contenu du cache de procédures

Cet exemple efface le contenu du cache de procédures (possible uniquement pour la base de données primaire).

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

G. Définir IDENTITY_CACHE

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)), Azure SQL Database et Azure SQL Managed Instance

Cet exemple désactive le cache d’identité.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF ;

H. Définir OPTIMIZE_FOR_AD_HOC_WORKLOADS

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Cet exemple permet à un stub de plan compilé d’être stocké dans le cache lorsqu’un lot est compilé pour la première fois.

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

I. Définir ELEVATE_ONLINE

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Cet exemple définit ELEVATE_ONLINE sur FAIL_UNSUPPORTED.

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = FAIL_UNSUPPORTED ;

J. Définir ELEVATE_RESUMABLE

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Cet exemple affecte la valeur WHEN_SUPPORTED à ELEVEATE_RESUMABLE.

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED ;

K. Effacer un plan de requête du cache du plan

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)), Azure SQL Database et Azure SQL Managed Instance

Cet exemple efface un plan spécifique à du cache de procédure

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 0x06000500F443610F003B7CD12C02000001000000000000000000000000000000000000000000000000000000;

L. Définir la durée de pause

S’applique à : Azure SQL Database et Azure SQL Managed Instance

Cet exemple définit la durée de pause de l’index reprenable sur 60 minutes.

ALTER DATABASE SCOPED CONFIGURATION
SET PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES = 60

m. Activer et désactiver le chargement des synthèses de registre

S’applique à : SQL Server (à compter de SQL Server 2022 (16.x))

Cet exemple active le chargement des synthèses de registre sur un compte Stockage Azure.

ALTER DATABASE SCOPED CONFIGURATION
SET LEDGER_DIGEST_STORAGE_ENDPOINT = 'https://mystorage.blob.core.windows.net'

Cet exemple désactive le chargement des synthèses de registre.

ALTER DATABASE SCOPED CONFIGURATION
SET LEDGER_DIGEST_STORAGE_ENDPOINT = OFF

Ressources supplémentaires

Ressources MAXDOP

• Degré de parallélisme
• Recommandations et directives pour l’option de configuration « max degree of parallelism » dans SQL Server

Ressources LEGACY_CARDINALITY_ESTIMATION

• Estimation de la cardinalité (SQL Server)
• Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator (Optimiser vos plans de requêtes avec l’Estimateur de la cardinalité de SQL Server 2014)

Ressources PARAMETER_SNIFFING

• Détection de paramètres
• I smell a parameter!

Ressources QUERY_OPTIMIZER_HOTFIXES

• Indicateurs de trace
• Modèle de service de l’indicateur de trace 4199 pour les correctifs de l’optimiseur de requête SQL Server

Ressources ELEVATE_ONLINE

Instructions pour les opérations d’index en ligne

Ressources ELEVATE_RESUMABLE

Instructions pour les opérations d’index en ligne

Contenu connexe

• sys.database_scoped_configurations
• sys.configurations
• Affichages catalogue de bases de données et de fichiers (Transact-SQL)
• Options de configuration du serveur (SQL Server)
• ALTER INDEX (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• Recommandations et directives pour l’option de configuration « max degree of parallelism » dans SQL Server
• Fonctionnement des opérations d'index en ligne
• Exécuter des opérations en ligne sur les index
• Traitement de requêtes intelligent dans les bases de données SQL
• Rétroaction d'allocation de mémoire
• Retour d'expérience sur l'estimation de la cardinalité (CE)
• Commentaires sur le degré de parallélisme (DOP)