Options SET d’ALTER DATABASE (Transact-SQL)

* SQL Server *  

Base de données SQL

SQL Managed Instance

Azure Synapse
Analytics

Microsoft Fabric

 

SQL Server

La mise en miroir de bases de données, Groupes de disponibilité Always On et les niveaux de compatibilité sont des options SET mais sont décrits dans des rubriques distinctes en raison de leur longueur. Pour plus d’informations, consultez Mise en miroir de bases de données ALTER DATABASE, ALTER DATABASE SET HADR et Niveau de compatibilité ALTER DATABASE.

Les configurations de niveau base de données sont utilisées pour définir plusieurs configurations de base de données au niveau de la base de données individuelle. Pour plus d’informations, consultez ALTER DATABASE SCOPED CONFIGURATION.

Syntaxe

ALTER DATABASE { database_name | CURRENT }
SET
{
    <option_spec> [ ,...n ] [ WITH <termination> ]
}

<option_spec> ::=
{
    <accelerated_database_recovery>
  | <auto_option>
  | <automatic_tuning_option>
  | <change_tracking_option>
  | <containment_option>
  | <cursor_option>
  | <database_mirroring_option>
  | <date_correlation_optimization_option>
  | <db_encryption_option>
  | <db_state_option>
  | <db_update_option>
  | <db_user_access_option>
  | <delayed_durability_option>
  | <external_access_option>
  | FILESTREAM ( <FILESTREAM_option> )
  | <HADR_options>
  | <mixed_page_allocation_option>
  | <parameterization_option>
  | <query_store_options>
  | <recovery_option>
  | <remote_data_archive_option>
  | <persistent_log_buffer_option>
  | <service_broker_option>
  | <snapshot_option>
  | <sql_option>
  | <suspend_for_snapshot_backup>
  | <target_recovery_time_option>
  | <termination>
  | <temporal_history_retention>
  | <data_retention_policy>
}
;

<accelerated_database_recovery> ::=
{
    ACCELERATED_DATABASE_RECOVERY = { ON | OFF }
     [ ( PERSISTENT_VERSION_STORE_FILEGROUP = { filegroup name } ) ];
}

<auto_option> ::=
{
    AUTO_CLOSE { ON | OFF }
  | AUTO_CREATE_STATISTICS { OFF | ON [ ( INCREMENTAL = { ON | OFF } ) ] }
  | AUTO_SHRINK { ON | OFF }
  | AUTO_UPDATE_STATISTICS { ON | OFF }
  | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<automatic_tuning_option> ::=
{
    AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF } )
}

<change_tracking_option> ::=
{
    CHANGE_TRACKING
   {
       = OFF
     | = ON [ ( <change_tracking_option_list > [,...n] ) ]
     | ( <change_tracking_option_list> [,...n] )
   }
}

<change_tracking_option_list> ::=
{
   AUTO_CLEANUP = { ON | OFF }
 | CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }
}

<containment_option> ::=
   CONTAINMENT = { NONE | PARTIAL }

<cursor_option> ::=
{
    CURSOR_CLOSE_ON_COMMIT { ON | OFF }
  | CURSOR_DEFAULT { LOCAL | GLOBAL }
}

<database_mirroring_option>
  ALTER DATABASE Database Mirroring

<date_correlation_optimization_option> ::=
    DATE_CORRELATION_OPTIMIZATION { ON | OFF }

<db_encryption_option> ::=
    ENCRYPTION { ON | OFF | SUSPEND | RESUME }

<db_state_option> ::=
    { ONLINE | OFFLINE | EMERGENCY }

<db_update_option> ::=
    { READ_ONLY | READ_WRITE }

<db_user_access_option> ::=
    { SINGLE_USER | RESTRICTED_USER | MULTI_USER }

<delayed_durability_option> ::=
    DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

<external_access_option> ::=
{
    DB_CHAINING { ON | OFF }
  | TRUSTWORTHY { ON | OFF }
  | DEFAULT_FULLTEXT_LANGUAGE = { <lcid> | <language name> | <language alias> }
  | DEFAULT_LANGUAGE = { <lcid> | <language name> | <language alias> }
  | NESTED_TRIGGERS = { OFF | ON }
  | TRANSFORM_NOISE_WORDS = { OFF | ON }
  | TWO_DIGIT_YEAR_CUTOFF = { 1753, ..., 2049, ..., 9999 }
}

<FILESTREAM_option> ::=
{
    NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL
  | DIRECTORY_NAME = <directory_name>
}

<HADR_options> ::=
    ALTER DATABASE SET HADR

<mixed_page_allocation_option> ::=
    MIXED_PAGE_ALLOCATION { OFF | ON }

<parameterization_option> ::=
    PARAMETERIZATION { SIMPLE | FORCED }

<query_store_options> ::=
{
    QUERY_STORE
    {
          = OFF [ ( FORCED ) ]
        | = ON [ ( <query_store_option_list> [,...n] ) ]
        | ( < query_store_option_list> [,...n] )
        | CLEAR [ ALL ]
    }
}

<query_store_option_list> ::=
{
      OPERATION_MODE = { READ_WRITE | READ_ONLY }
    | CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = number )
    | DATA_FLUSH_INTERVAL_SECONDS = number
    | MAX_STORAGE_SIZE_MB = number
    | INTERVAL_LENGTH_MINUTES = number
    | SIZE_BASED_CLEANUP_MODE = { AUTO | OFF }
    | QUERY_CAPTURE_MODE = { ALL | AUTO | CUSTOM | NONE }
    | MAX_PLANS_PER_QUERY = number
    | WAIT_STATS_CAPTURE_MODE = { ON | OFF }
    | QUERY_CAPTURE_POLICY = ( <query_capture_policy_option_list> [,...n] )
}

<query_capture_policy_option_list> :: =
{
      STALE_CAPTURE_POLICY_THRESHOLD = number { DAYS | HOURS }
    | EXECUTION_COUNT = number
    | TOTAL_COMPILE_CPU_TIME_MS = number
    | TOTAL_EXECUTION_CPU_TIME_MS = number
}

<recovery_option> ::=
{
    RECOVERY { FULL | BULK_LOGGED | SIMPLE }
  | TORN_PAGE_DETECTION { ON | OFF }
  | PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}

<remote_data_archive_option> ::=
{
    REMOTE_DATA_ARCHIVE =
    {
        ON ( SERVER = <server_name>,
             {
                  CREDENTIAL = <db_scoped_credential_name>
                  | FEDERATED_SERVICE_ACCOUNT = ON | OFF
             }
        )
        | OFF
    }
}

<persistent_log_buffer_option> ::=
{
    PERSISTENT_LOG_BUFFER 
    {
          = ON (DIRECTORY_NAME= 'path-to-directory-on-a-DAX-volume')
        | = OFF
    }
}

<service_broker_option> ::=
{
    ENABLE_BROKER
  | DISABLE_BROKER
  | NEW_BROKER
  | ERROR_BROKER_CONVERSATIONS
  | HONOR_BROKER_PRIORITY { ON | OFF }
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
  | READ_COMMITTED_SNAPSHOT { ON | OFF }
  | MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = { ON | OFF }
}

<sql_option> ::=
{
    ANSI_NULL_DEFAULT { ON | OFF }
  | ANSI_NULLS { ON | OFF }
  | ANSI_PADDING { ON | OFF }
  | ANSI_WARNINGS { ON | OFF }
  | ARITHABORT { ON | OFF }
  | COMPATIBILITY_LEVEL = { 160 | 150 | 140 | 130 | 120 | 110 | 100 }
  | CONCAT_NULL_YIELDS_NULL { ON | OFF }
  | NUMERIC_ROUNDABORT { ON | OFF }
  | QUOTED_IDENTIFIER { ON | OFF }
  | RECURSIVE_TRIGGERS { ON | OFF }
}

<suspend_for_snapshot_backup> ::=
    SET SUSPEND_FOR_SNAPSHOT_BACKUP = { ON | OFF } [ ( MODE = COPY_ONLY ) ]

<target_recovery_time_option> ::=
    TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }

<termination>::=
{
    ROLLBACK AFTER number [ SECONDS ]
  | ROLLBACK IMMEDIATE
  | NO_WAIT
}

<temporal_history_retention> ::=
    TEMPORAL_HISTORY_RETENTION { ON | OFF }

<data_retention_policy> ::=
    DATA_RETENTION { ON | OFF }

Arguments

database_name

Nom de la base de données à modifier.

CURRENT

S’applique à : SQL Server (à compter de SQL Server 2012 (11.x))

Exécute l’action dans la base de données actuelle. CURRENT n’est pas pris en charge pour toutes les options dans tous les contextes. Si CURRENT échoue, fournissez le nom de la base de données.

<accelerated_database_recovery> ::=

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x))

Active récupération de base de données accélérée (ADR). ADR est défini sur OFF par défaut dans SQL Server 2019 (15.x) et versions ultérieures. À l’aide de cette syntaxe, vous pouvez désigner un groupe de fichiers spécifique pour les données pvS (Persistent Version Store). Si aucun groupe de fichiers n’est spécifié, le PVS est stocké dans le groupe de fichiers PRIMARY. Pour plus d’informations, consultez Gérer la récupération de base de données accélérée.

<auto_option> ::=

Contrôle les options automatiques.

AUTO_CLOSE { ON | OFF }

• ACTIVÉ

  La base de données est arrêtée correctement et ses ressources sont libérées dès que le dernier utilisateur l'a quittée.

  La base de données est rouverte automatiquement lorsqu'un utilisateur tente de la réutiliser. Par exemple, ce comportement se produit lorsqu’un utilisateur émet une instruction USE database_name. La base de données peut s’arrêter correctement avec AUTO_CLOSE défini sur ON. Si c’est le cas, la base de données ne rouvrira pas tant qu’un utilisateur ne tente pas d’utiliser la base de données la prochaine fois que le moteur de base de données redémarre.

  Une fois qu’une base de données est arrêtée, la prochaine fois qu’une application tente d’utiliser la base de données, la base de données doit d’abord être ouverte, puis l’état doit être changé en « En ligne ». Ceci peut prendre un certain temps et entraîner des dépassements de délai d’expiration dans l’application.

• OFF

  La base de données reste ouverte après que le dernier utilisateur l'a quittée.

  L'option AUTO_CLOSE est utile pour les bases de données bureautiques, puisqu'elle permet aux fichiers de base de données d'être gérés comme des fichiers normaux. Ils peuvent être déplacés, copiés pour faire une sauvegarde ou même envoyés par e-mail à d’autres utilisateurs. Le processus AUTO_CLOSE est asynchrone ; l’ouverture et la fermeture répétées de la base de données n’ont aucune incidence sur les performances.

Quand la base de données est définie sur AUTOCLOSE = ON, une opération qui initie un arrêt de la base de données automatique efface le cache du plan pour l’instance de SQL Server. Cette opération entraîne la recompilation de tous les plans d'exécution ultérieurs et peut entraîner une baisse temporaire et brutale des performances des requêtes. À compter de SQL Server 2005 (9.x) Service Pack 2, pour chaque mémoire cache effacée dans le cache de plan, le journal des erreurs SQL Server contient le message d’information suivant : SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. Ce message est enregistré toutes les cinq minutes si le cache est vidé au cours de cet intervalle de temps.

Le paramètre AUTO_CLOSE peut être une fonctionnalité utile dans certaines situations rares, par exemple, dans une instance SQL Server sans mémoire suffisante pour fonctionner de manière stable avec un grand nombre de bases de données, ou pour une instance SQL Server 32 bits héritée avec un grand nombre de bases de données. Dans de tels scénarios, il peut être utile d’activer AUTO_CLOSE et de conserver les ressources de mémoire nécessaires pour ouvrir une base de données lorsqu’il n’existe aucune application à l’aide de la base de données. Quand la base de données est ouverte, certaines allocations de mémoire par défaut sont nécessaires (par exemple des structures internes pour représenter différents objets de métadonnées et des mémoires tampon du journal des transactions de la base de données).

AUTO_CREATE_STATISTICS { ON | OFF }

• ACTIVÉ

  L’optimiseur de requête crée des statistiques sur les colonnes uniques des prédicats de requête, en fonction des besoins, afin d’améliorer les plans de requête et les performances des requêtes. Ces statistiques de colonnes uniques sont créées quand l’optimiseur de requête compile les requêtes. Les statistiques de colonnes uniques sont créées uniquement sur les colonnes qui ne constituent pas déjà la première colonne d’un objet de statistiques existant.

  Le paramètre par défaut est ON. Nous vous recommandons d'utiliser le paramètre par défaut pour la plupart des bases de données.

• OFF

  L’optimiseur de requête ne crée pas de statistiques sur les colonnes uniques des prédicats de requête quand il compile les requêtes. Si cette option a la valeur OFF, il peut en résulter des plans de requête non optimisés et une dégradation des performances des requêtes.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_create_stats_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAutoCreateStatistics de la fonction DATABASEPROPERTYEX.

Pour plus d’informations, consultez la section « Utilisation des options de statistiques à l’échelle de la base de données » dans Statistiques.

INCREMENTAL = ON | OFF

S’applique à : SQL Server (à compter de SQL Server 2014 (12.x)) et Azure SQL Database.

Définissez AUTO_CREATE_STATISTICS sur la valeur ON, et INCREMENTAL sur la valeur ON. Ceci définit les statistiques créées automatiquement comme étant incrémentielles, dès lors que celles-ci sont prises en charge. La valeur par défaut est OFF. Pour plus d’informations, voir CREATE STATISTICS.

AUTO_SHRINK { ON | OFF }

• ACTIVÉ

  Les fichiers de base de données peuvent faire l'objet d'une réduction périodique. Sauf si vous avez une exigence spécifique, ne définissez pas l’option de base de données AUTO_SHRINK sur ON. Pour plus d’informations, consultez Réduction d’une base de données.

  Les fichiers de données et les fichiers journaux peuvent être réduits automatiquement. AUTO_SHRINK ne réduit la taille du journal des transactions que si vous définissez la base de données sur le mode de récupération SIMPLE ou si vous sauvegardez le journal. Quand vous définissez AUTO_SHRINK sur OFF, les fichiers de base de données ne sont pas réduits automatiquement lors des vérifications périodiques de l’espace inutilisé.

  L’option AUTO_SHRINK réduit les fichiers quand ceux-ci contiennent plus de 25 % d’espace inutilisé. Elle réduit le fichier à une des deux tailles suivantes (selon la valeur la plus grande) :

  • La taille à laquelle 25 pour cent du fichier est de l’espace inutilisé
  • La taille du fichier quand il a été créé

  Vous ne pouvez pas réduire une base de données en lecture seule.

• OFF

  Les fichiers de base de données ne sont pas automatiquement réduits pendant les vérifications périodiques de l’espace inutilisé.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_shrink_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAutoShrink de la fonction DATABASEPROPERTYEX.

AUTO_UPDATE_STATISTICS { ON | OFF }

• ACTIVÉ

  Spécifie que l’optimiseur de requête met à jour les statistiques quand elles sont utilisées par une requête et quand elles sont susceptibles d’être obsolètes. Les statistiques deviennent obsolètes après que des opérations d'insertion, de mise à jour, de suppression ou de fusion ont modifié la distribution des données dans la table ou la vue indexée. L’optimiseur de requête détermine si les statistiques sont obsolètes en comptant le nombre de modifications de données depuis la dernière mise à jour des statistiques et en comparant le nombre de modifications à un seuil. Ce seuil est basé sur le nombre de lignes contenues dans la table ou la vue indexée.

  L’optimiseur de requête vérifie s’il existe des statistiques obsolètes avant de compiler une requête et d’exécuter un plan de requête mis en cache. L’optimiseur de requête utilise les colonnes, les tables et les vues indexées du prédicat de requête pour identifier les statistiques susceptibles d’être obsolètes. L’optimiseur de requête détermine ces informations avant de compiler une requête. Avant d’exécuter un plan de requête mis en cache, le Moteur de base de données vérifie que le plan de requête référence des statistiques à jour.

  L'option AUTO_UPDATE_STATISTICS s'applique aux statistiques créées pour les index, aux colonnes uniques contenues dans les prédicats de requête et aux statistiques créées à l'aide de l'instruction CREATE STATISTICS. Cette option s'applique également aux statistiques filtrées.

  La valeur par défaut est ON. Nous vous recommandons d'utiliser le paramètre par défaut pour la plupart des bases de données.

  Utilisez l'option AUTO_UPDATE_STATISTICS_ASYNC pour spécifier si les statistiques doivent être mises à jour en mode synchrone ou asynchrone.

• OFF

  Spécifie que l’optimiseur de requête ne met pas à jour les statistiques quand elles sont utilisées par une requête. L’optimiseur de requête ne met pas non plus à jour les statistiques quand elles sont susceptibles d’être obsolètes. Si cette option a la valeur OFF, il peut en résulter des plans de requête non optimisés et une dégradation des performances des requêtes.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_update_stats_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAutoUpdateStatistics de la fonction DATABASEPROPERTYEX.

Pour plus d’informations, consultez la section « Utilisation des options de statistiques à l’échelle de la base de données » dans Statistiques.

AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

• ACTIVÉ

  Spécifie que les mises à jour des statistiques pour l'option AUTO_UPDATE_STATISTICS sont asynchrones. L’optimiseur de requête n’attend pas la fin des mises à jour des statistiques pour compiler les requêtes.

  Affecter la valeur ON à cette option n'a aucun effet à moins que AUTO_UPDATE_STATISTICS n'ait également la valeur ON.

  Par défaut, l’option AUTO_UPDATE_STATISTICS_ASYNC est OFF ; l’optimiseur de requête met à jour les statistiques de façon synchrone.

• OFF

  Spécifie que les mises à jour des statistiques pour l'option AUTO_UPDATE_STATISTICS sont synchrones. L’optimiseur de requête attend la fin des mises à jour des statistiques pour compiler les requêtes.

  Notes

  Affecter la valeur OFF à cette option n'a aucun effet à moins que AUTO_UPDATE_STATISTICS n'ait également la valeur ON.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_update_stats_async_on de la vue de catalogue sys.databases.

Pour plus d’informations décrivant quand utiliser des mises à jour de statistiques synchrones ou asynchrones, consultez la section « Options des statistiques » dans Statistiques.

<automatic_tuning_option> ::=

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x))

Active ou désactive l’option FORCE_LAST_GOOD_PLANOptimisation automatique. Vous pouvez visualiser l’état de cette option dans la vue sys.database_automatic_tuning_options.

FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF }

• DEFAULT

  La valeur par défaut pour SQL Server est OFF.

• ACTIVÉ

  Le moteur de base de données force automatiquement le dernier plan correct connu sur les requêtes Transact-SQL là où le nouveau plan de requête provoque des régressions des performances. Le moteur de base de données supervise en permanence les performances de la requête Transact-SQL avec le plan forcé.

  S’il existe des gains de performances, le moteur de base de données continue d’utiliser le dernier bon plan connu. Si les gains de performances ne sont pas détectés, le moteur de base de données produit un nouveau plan de requête. L’instruction échoue si le magasin de requêtes n’est pas activé ou si le Magasin des requêtes n’est pas en mode en lecture-écriture.

• OFF

  Le Moteur de base de données indique les régressions des performances de requêtes potentielles dues à des changements de plan de requête dans la vue sys.dm_db_tuning_recommendations. Toutefois, ces recommandations ne sont pas appliquées automatiquement. Les utilisateurs peuvent superviser les recommandations actives et résoudre les problèmes identifiés en appliquant les scripts Transact-SQL qui sont montrés dans la vue. La valeur par défaut est OFF.

<change_tracking_option> ::=

S’applique à : SQL Server et Azure SQL Database

Contrôle les options de suivi des modifications. Vous pouvez activer le suivi des modifications, définir des options, modifier des options et désactiver le suivi des modifications. Pour obtenir des exemples, consultez la section Exemples plus loin dans cet article.

• ACTIVÉ

  Active le suivi des modifications pour la base de données Lorsque vous activez le suivi des modifications, vous pouvez également définir les options AUTO CLEANUP et CHANGE RETENTION.

• AUTO_CLEANUP = { ON | OFF }

  • ACTIVÉ

    Les informations de suivi des modifications sont supprimées automatiquement à l'issue de la période de rétention spécifiée.

  • OFF

    Les données de suivi des modifications ne sont pas supprimées automatiquement de la base de données.

• CHANGE_RETENTION = période_conservation { DAYS | HOURS | MINUTES }

  Spécifie la période minimale de conservation des informations de suivi des modifications dans la base de données. Les données sont supprimées uniquement lorsque AUTO_CLEANUP a la valeur ON.

  retention_period est un entier qui spécifie la composante numérique de la période de rétention.

  La période de conservation par défaut est 2 jours. La période de rétention minimale est 1 minute. Le type de conservation par défaut est DAYS.

• OFF désactive le suivi des modifications pour la base de données. Désactivez le suivi des modifications sur toutes les tables avant de le désactiver sur la base de données.

<containment_option> ::=

S’applique à : SQL Server (à compter de SQL Server 2012 (11.x))

Contrôle des options de la relation contenant-contenu de la base de données.

CONTAINMENT = { NONE | PARTIAL}

• Aucune

  La base de données n’est pas une base de données autonome.

• PARTIAL

  La base de données est une base de données autonome. La définition de l’autonomie de la base de données sur une défaillance partielle si la base de données a la réplication, la capture de données modifiées ou le suivi des modifications activé. La vérification des erreurs prend fin après un échec. Pour plus d'informations sur les bases de données autonomes, consultez Bases de données autonomes.

<cursor_option> ::=

Contrôle les options de curseur.

CURSOR_CLOSE_ON_COMMIT { ON | OFF }

• ACTIVÉ

  Les curseurs ouverts quand vous validez ou restaurez une transaction sont fermés.

• OFF

  Les curseurs restent ouverts lorsqu’une transaction est validée ; La restauration d’une transaction ferme tous les curseurs, à l’exception de ceux définis comme INSENSITIVE ou STATIC.

Les paramètres de niveau connexion définis à l'aide de l'instruction SET se substituent au paramètre de base de données par défaut de CURSOR_CLOSE_ON_COMMIT. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui désactive l’option CURSOR_CLOSE_ON_COMMIT pour la session (valeur OFF) par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET CURSOR_CLOSE_ON_COMMIT.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_cursor_close_on_commit_on de la vue de catalogue sys.databases ou la propriété IsCloseCursorsOnCommitEnabled de la fonction DATABASEPROPERTYEX.

CURSOR_DEFAULT { LOCAL | GLOBAL }

S’applique à : SQL Server

Détermine si l'étendue du curseur utilise LOCAL ou GLOBAL.

• LOCAL

  Quand vous spécifiez LOCAL et que vous ne définissez pas de curseur comme GLOBAL lors de la création du curseur, le curseur est d’étendue locale. Plus précisément, l’étendue du curseur est locale pour le lot, la procédure stockée ou le déclencheur dans lequel vous avez créé le curseur. Le nom du curseur n'est valide que dans cette étendue.

  Le curseur peut être référencé par des variables de curseur locales dans le traitement, la procédure stockée ou le déclencheur, ou bien par un paramètre OUTPUT d'une procédure stockée. Le curseur est libéré implicitement à la fin du lot, de la procédure stockée ou du déclencheur. Le curseur est libéré à moins d’avoir été retourné dans un paramètre OUTPUT. Le curseur peut avoir été retourné dans un paramètre OUTPUT. Si le curseur est retourné de cette manière, il est libéré lorsque la dernière variable qui fait référence au curseur est libérée ou est hors de portée.

• GLOBAL

  Si GLOBAL est spécifié et qu’aucun curseur n’est défini comme LOCAL lors de sa création, le curseur est d’étendue globale pour la connexion. Toute procédure stockée ou tout lot exécuté par la connexion peut faire référence au nom du curseur.

  Le curseur n'est libéré implicitement qu'au moment de la déconnexion. Pour plus d’informations, voir DECLARE CURSOR.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_local_cursor_default de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsLocalCursorsDefault de la fonction DATABASEPROPERTYEX.

<temporal_history_retention> ::=

TEMPORAL_HISTORY_RETENTION { ON | OFF }

Défini par défaut sur ON, mais défini automatiquement sur la valeur OFF après une opération de limite de restauration dans le temps. Pour plus d’informations, notamment sur la façon d’activer ce paramètre, consultez Configuration d’une stratégie de rétention.

• ACTIVÉ

  Par défaut. Active la stratégie de rétention de table temporelle. Pour plus d’informations, consultez Gérer la conservation des données d’historique dans les tables temporelles versionnées par le système.

• OFF

  N’effectuez pas de stratégie de rétention historique temporelle.

<data_retention_policy> ::=

s’applique à: Azure SQL Edge uniquement.

DATA_RETENTION { ON | OFF }

• ACTIVÉ

  Active le nettoyage basé sur la stratégie de rétention des données sur une base de données.

• OFF

  Désactive le nettoyage basé sur la stratégie de rétention des données sur une base de données.

<database_mirroring>

S’applique à : SQL Server

Pour une description des arguments, voir Mise en miroir de bases de données ALTER DATABASE.

<date_correlation_optimization_option> ::=

S’applique à : SQL Server

Contrôle l'option date_correlation_optimization.

DATE_CORRELATION_OPTIMIZATION { ON | OFF }

• ACTIVÉ

  SQL Server conserve les statistiques de corrélation dans laquelle une contrainte FOREIGN KEY lie deux tables quelconques de la base de données et où les tables ont des colonnes datetime.

• OFF

  Les statistiques de corrélation ne sont pas conservées.

Pour pouvoir définir DATE_CORRELATION_OPTIMIZATION sur ON, il ne doit exister aucune connexion active à la base de données, à l’exception de celle qui exécute l’instruction ALTER DATABASE. Ensuite, différentes connexions peuvent être prises en charge.

La valeur actuelle de cette option peut être déterminée en examinant la colonne is_date_correlation_on dans la vue de catalogue sys.databases.

<db_encryption_option> ::=

Contrôle l'état de chiffrement de la base de données.

ENCRYPTION { ON | OFF | SUSPEND | RESUME }

• ACTIVÉ

  Indique que la base de données doit être chiffrée.

• OFF

  Indique que la base de données ne doit pas être chiffrée.

• SUSPEND

  S’applique à : SQL Server (à compter de SQL Server 2019 (15.x))

  Peut être utilisé pour suspendre l’analyse de chiffrement après l’activation ou la désactivation du chiffrement transparent des données, ou après la modification de la clé de chiffrement.

• RESUME

  S’applique à : SQL Server (à compter de SQL Server 2019 (15.x))

  Peut être utilisée pour reprendre une analyse du chiffrement.

Pour plus d’informations sur le chiffrement de base de données, consultez Transparent Data Encryption (TDE) et Transparent Data Encryption pour Azure SQL Database, Azure SQL Managed Instance et Azure Synapse Analytics.

Lorsque le chiffrement est activé au niveau de la base de données, tous les groupes de fichiers sont chiffrés. Les nouveaux groupes de fichiers héritent de la propriété chiffrée. Si des groupes de fichiers de la base de données sont définis sur READ ONLY, l’opération de chiffrement de base de données échoue.

Vous pouvez visualiser l’état de chiffrement de la base de données et l’état de l’analyse du chiffrement dans la vue de gestion dynamique sys.dm_database_encryption_keys.

<db_state_option> ::=

S’applique à : SQL Server

Contrôle l'état de la base de données.

• OFFLINE

  La base de données est fermée et arrêtée correctement, puis marquée comme étant déconnectée. Tant que la base de données est hors connexion, elle ne peut pas être modifiée.

• ONLINE

  La base de données est ouverte et peut être utilisée.

• EMERGENCY

  La base de données est marquée READ_ONLY, la journalisation est désactivée et l'accès est restreint aux membres du rôle serveur fixe sysadmin. EMERGENCY est principalement utilisé à des fins de dépannage. Par exemple, une base de données marquée comme suspecte en raison d’un fichier journal corrompu peut se voir affecter l’état EMERGENCY. Ce paramètre peut permettre à l’administrateur système d’accéder en lecture seule à la base de données. Seuls les membres du rôle serveur fixe sysadmin peuvent définir l'état EMERGENCY pour une base de données.

Nécessite l’autorisation ALTER DATABASE pour la base de données concernée afin de changer l’état d’une base de données en hors connexion ou en urgence, et l’autorisation ALTER ANY DATABASE au niveau du serveur pour faire passer une base de données de hors connexion à en ligne.

Vous pouvez déterminer l’état de cette option en consultant les colonnes state et state_desc de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété Status de la fonction DATABASEPROPERTYEX. Pour plus d'informations, consultez Database States.

Une base de données marquée RESTORING ne peut pas se voir affecter la valeur OFFLINE, ONLINE ou EMERGENCY. Une base de données peut être dans l’état RESTOREING pendant une opération de restauration active ou lorsqu’une opération de restauration d’une base de données ou d’un fichier journal échoue en raison d’un fichier de sauvegarde endommagé.

<db_update_option> ::=

Contrôle si des mises à jour sont autorisées dans la base de données.

• READ_ONLY

  Les utilisateurs peuvent lire des données dans la base de données mais ils n'ont pas le droit de les modifier.

  Notes

  Pour améliorer les performances des requêtes, mettez à jour les statistiques avant de définir une base de données à READ_ONLY. Si des statistiques supplémentaires sont nécessaires une fois qu’une base de données est définie sur READ_ONLY, le moteur de base de données crée des statistiques dans la base de données système tempdb. Pour plus d’informations sur les statistiques pour une base de données en lecture seule, consultez Statistiques.

• READ_WRITE

  La base de données est accessible aux opérations de lecture et d'écriture.

Pour modifier cet état, vous devez bénéficier d'un accès exclusif à la base de données. Pour plus d'informations, consultez la clause SINGLE_USER.

<db_user_access_option> ::=

Contrôle l'accès utilisateur à la base de données.

SINGLE_USER

S’applique à : SQL Server

Indique que l'accès à la base de données n'est autorisé qu'à un seul utilisateur à la fois. Si vous spécifiez SINGLE_USER et qu’un autre utilisateur se connecte à la base de données, l’instruction ALTER DATABASE est bloquée jusqu’à ce que tous les utilisateurs se déconnectent de la base de données spécifiée. Pour changer ce comportement, consultez la clause WITH <termination>.

La base de données demeure en mode SINGLE_USER même si l'utilisateur qui a défini l'option se déconnecte. À ce stade, un autre utilisateur (et un seul) peut se connecter à la base de données.

Avant d'affecter la valeur SINGLE_USER à la base de données, vérifiez que l'option AUTO_UPDATE_STATISTICS_ASYNC a la valeur OFF. Lorsqu’il est défini sur ON, le thread d’arrière-plan utilisé pour mettre à jour les statistiques prend une connexion à la base de données et vous ne pouvez pas accéder à la base de données en mode mono-utilisateur. Pour consulter l’état de cette option, interrogez la colonne is_auto_update_stats_async_on de la vue de catalogue sys.databases. Si l'option a la valeur ON, effectuez les tâches suivantes :

1. Affectez la valeur OFF à AUTO_UPDATE_STATISTICS_ASYNC.

2. Recherchez les travaux des statistiques asynchrones actifs en interrogeant la vue de gestion dynamique sys.dm_exec_background_job_queue.

Si des travaux sont actifs, laissez ces travaux se terminer ou arrêtez-les manuellement à l’aide de KILL STATS JOB.

RESTRICTED_USER

Autorise uniquement les membres du rôle de base de données fixe db_owner et des rôles serveur fixes dbcreator et sysadmin à se connecter à la base de données. RESTRICTED_USER n’en limite pas le nombre. Fermez toutes les connexions à la base de données dans la plage de temps spécifiée par la clause d’arrêt de l’instruction ALTER DATABASE. Après que la base est passée à l'état RESTRICTED_USER, toute tentative de connexion par des utilisateurs non qualifiés est refusée.

MULTI_USER

Tous les utilisateurs qui bénéficient des autorisations appropriées peuvent se connecter à la base de données. Vous pouvez déterminer l’état de cette option en consultant la colonne user_access de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété UserAccess de la fonction DATABASEPROPERTYEX.

<delayed_durability_option> ::=

S’applique à : SQL Server (à compter de SQL Server 2014 (12.x))

Contrôle si les transactions sont validées de manière entièrement durable ou durable différée.

• DISABLED

  Toutes les transactions suivant SET DISABLED sont entièrement durables. Toutes les options de durabilité définies dans une instruction de validation ou de bloc atomique sont ignorées.

• ALLOWED

  Toutes les transactions suivant SET ALLOWED sont soit entièrement durables, soit retardées durables, en fonction de l’option de durabilité définie dans l’instruction de validation ou de bloc atomique.

• FORCED

  Toutes les transactions suivant SET FORCED sont retardées durables. Toutes les options de durabilité définies dans une instruction de validation ou de bloc atomique sont ignorées.

<external_access_option> ::=

S’applique à : SQL Server

Contrôle si des ressources externes, par exemple des objets d'une autre base de données, peuvent accéder à la base de données.

DB_CHAINING { ON | OFF }

• ACTIVÉ

  La base de données peut être la source ou la cible d'une chaîne de propriétés des bases de données croisées.

• OFF

  La base de données ne peut pas prendre part à un chaînage des propriétés des bases de données croisées.

Pour définir cette option, l'autorisation CONTROL SERVER est nécessaire sur la base de données.

L’option DB_CHAINING ne peut pas être définie sur les bases de données système master, model et tempdb.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_db_chaining_on de la vue de catalogue sys.databases.

TRUSTWORTHY { ON | OFF }

• ACTIVÉ

  Les modules de base de données (par exemple, les procédures stockées ou les fonctions définies par l'utilisateur) qui utilisent un contexte d'emprunt d'identité peuvent accéder à des ressources en dehors de la base de données.

• OFF

  Les modules de base de données qui utilisent l’emprunt d’identité ne peuvent pas accéder à des ressources externes à la base de données.

  TRUSTWORTHY prend la valeur OFF chaque fois que la base de données est attachée.

Pour toutes les bases de données système (sauf msdb), l’option TRUSTWORTHY est par défaut définie sur OFF. La valeur n’est pas être modifiable pour les bases de données model et tempdb. Nous vous recommandons de ne jamais définir l’option TRUSTWORTHY sur ON pour la base de données master.

Pour définir cette option, l'autorisation CONTROL SERVER est nécessaire sur la base de données.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_trustworthy_on de la vue de catalogue sys.databases.

DEFAULT_FULLTEXT_LANGUAGE

S’applique à : SQL Server (à compter de SQL Server 2012 (11.x))

Spécifie la valeur de langue par défaut pour les colonnes indexées de texte intégral.

DEFAULT_LANGUAGE

S’applique à : SQL Server (à compter de SQL Server 2012 (11.x))

Spécifie la langue par défaut de toutes les nouvelles connexions. La langue peut être spécifiée en indiquant l’ID local (lcid), son nom ou son alias. Pour connaître la liste des noms et des alias de langue acceptables, voir sys.syslanguages. Cette option est autorisée uniquement lorsque CONTAINMENT a été défini sur PARTIAL. Si CONTAINMENT est défini sur NONE, des erreurs se produisent.

NESTED_TRIGGERS

S’applique à : SQL Server (à compter de SQL Server 2012 (11.x))

Spécifie si un déclencheur AFTER peut s'exécuter en cascade et, par conséquent, réaliser une action qui initialise un autre déclencheur, lequel initialise un autre déclencheur, etc. Cette option est autorisée uniquement lorsque CONTAINMENT a été défini sur PARTIAL. Si CONTAINMENT est défini sur NONE, des erreurs se produisent.

TRANSFORM_NOISE_WORDS

S’applique à : SQL Server (à compter de SQL Server 2012 (11.x))

Utilisé pour supprimer un message d'erreur si des mots parasites ou des mots vides provoquent l'échec d'une opération booléenne sur une requête de texte intégral. Cette option est autorisée uniquement lorsque CONTAINMENT a été défini sur PARTIAL. Si CONTAINMENT est défini sur NONE, des erreurs se produisent.

TWO_DIGIT_YEAR_CUTOFF

S’applique à : SQL Server (à compter de SQL Server 2012 (11.x))

Spécifie un entier compris entre 1 753 et 9 999 qui représente l'année de coupure permettant d'interpréter les années à deux chiffres comme des années à quatre chiffres. Cette option est autorisée uniquement lorsque CONTAINMENT a été défini sur PARTIAL. Si CONTAINMENT est défini sur NONE, des erreurs se produisent.

<FILESTREAM_option> ::=

S’applique à : SQL Server (à compter de SQL Server 2012 (11.x))

Contrôle les paramètres des FileTables.

NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }

• OFF

  L'accès non transactionnel aux données FileTable est désactivé.

• READ_ONLY

  Les données FILESTREAM dans les FileTables de cette base de données peuvent être lues par des processus non transactionnels.

• FULL

  Active l’accès non transactionnel complet aux données FILESTREAM dans les FileTables.

DIRECTORY_NAME = <directory_name>

Nom de répertoire compatible avec Windows. Ce nom doit être unique parmi tous les noms de répertoire au niveau de la base de données dans cette instance de SQL Server. La comparaison d'unicité n'est pas sensible à la casse, indépendamment des paramètres de classement. Cette option doit être définie avant de créer un FileTable dans cette base de données.

<HADR_options> ::=

S’applique à : SQL Server

Voir ALTER DATABASE SET HADR.

<mixed_page_allocation_option> ::=

S’applique à : SQL Server (à compter de SQL Server 2016 (13.x))

Contrôle si la base de données peut créer des pages initiales à l’aide d’une extension mixte pour les huit premières pages d’une table ou d’un index.

MIXED_PAGE_ALLOCATION { OFF | ON }

• OFF

  La base de données crée toujours les pages initiales à l’aide d’extensions uniformes. OFF est la valeur par défaut.

• ACTIVÉ

  La base de données peut créer des pages initiales à l’aide d’extensions mixtes.

Ce paramètre est ON pour toutes les bases de données système. tempdb est la seule base de données système qui prenne en charge la valeur OFF.

<PARAMETERIZATION_option> ::=

Contrôle l'option de paramétrage. Pour plus d’informations sur le paramétrage, consultez Guide d’architecture de traitement des requêtes.

PARAMETERIZATION { SIMPLE | FORCED }

• SIMPLE

  Les requêtes sont paramétrables en fonction du comportement par défaut de la base de données.

• FORCED

  SQL Server paramètre toutes les requêtes de la base de données.

La valeur actuelle de cette option peut être déterminée en examinant la colonne is_parameterization_forced dans la vue de catalogue sys.databases.

<query_store_options> ::=

S’applique à : SQL Server (à compter de SQL Server 2016 (13.x))

ON | OFF [ ( FORCED ) ] | CLEAR [ ALL ]

Contrôle si le Magasin des requêtes est activé dans cette base de données et contrôle la suppression du contenu du Magasin des requêtes. Pour plus d’informations, consultez Scénarios d’utilisation du magasin des requêtes.

• ACTIVÉ

  Active le magasin des requêtes.

  Beaucoup des nouvelles fonctionnalités de performances de SQL Server 2022 (16.x), comme les indicateurs de Magasin des requêtes, la rétroaction d’estimation de la cardinalité (CE), la rétroaction de degré de parallélisme (DOP) et la persistance de la rétroaction d’allocation de mémoire (MGF), nécessitent l’activation du Magasin des requêtes. Pour les bases de données qui ont été restaurées à partir d’autres instances SQL Server et pour celles mises à niveau à partir d’une mise à niveau sur place vers SQL Server 2022 (16.x), ces bases de données conservent les paramètres précédents du Magasin des requêtes. S’il existe des préoccupations concernant la surcharge du Magasin des requêtes, les administrateurs peuvent tirer parti de stratégies de capture personnalisées avec QUERY_CAPTURE_MODE = CUSTOM. Pour obtenir des exemples d’activation du Magasin des requêtes avec des options de stratégie de capture personnalisée, consultez la section Exemples plus loin dans cet article.

• OFF [ ( FORCED ) ]

  Désactive le magasin des requêtes. FORCED est facultatif. FORCED abandonne toutes les tâches en arrière-plan du Magasin des requêtes en cours d’exécution, et ignore le vidage synchrone lorsque le Magasin des requêtes est désactivé. Entraîne l’arrêt du Magasin des requêtes aussi vite que possible. FORCED s’applique à SQL Server 2016 (13.x) SP2 CU14, SQL Server 2017 (14.x) CU21, SQL Server 2019 (15.x) CU6 et aux builds ultérieures.

  Notes

  Le Magasin des requêtes ne peut pas être désactivé dans Azure SQL Database. L’exécution de ALTER DATABASE [database] SET QUERY_STORE = OFF retourne le 'QUERY_STORE=OFF' is not supported in this version of SQL Server.d’avertissement.

• CLEAR [ ALL ]

  Supprime les données relatives aux requêtes du Magasin des requêtes. ALL est facultatif. ALL supprime les données et métadonnées relatives aux requêtes du Magasin des requêtes.

OPERATION_MODE { READ_ONLY | READ_WRITE }

Décrit le mode de fonctionnement du magasin des requêtes.

READ_WRITE

Le magasin des requêtes collecte et conserve les informations sur les plans de requête et les statistiques d’exécution.

READ_ONLY

Les informations peuvent être lues à partir du Magasin des requêtes, mais les nouvelles informations ne sont pas ajoutées. Si l’espace émis maximal du Magasin des requêtes a été épuisé, le Magasin des requêtes change son mode d’opération en READ_ONLY.

CLEANUP_POLICY

Décrit la stratégie de conservation des données du magasin des requêtes. STALE_QUERY_THRESHOLD_DAYS détermine le nombre de jours pendant lesquels les informations d’une requête sont conservées dans le magasin des requêtes. STALE_QUERY_THRESHOLD_DAYS est de type bigint. La valeur par défaut est 30.

DATA_FLUSH_INTERVAL_SECONDS

Détermine la fréquence à laquelle les données écrites dans le magasin des requêtes sont stockées sur le disque. Pour optimiser les performances, les données collectées par le magasin des requêtes sont écrites de façon asynchrone sur le disque. La fréquence à laquelle ce transfert asynchrone se produit est configurée à l'aide de l'argument DATA_FLUSH_INTERVAL_SECONDS. DATA_FLUSH_INTERVAL_SECONDS est de type bigint. La valeur par défaut est 900 (15 minutes).

MAX_STORAGE_SIZE_MB

Détermine l’espace alloué au magasin des requêtes. MAX_STORAGE_SIZE_MB est de type bigint. La valeur par défaut est de 100 Mo pour SQL Server(de SQL Server 2016 (13.x) à SQL Server 2017 (14.x)). À partir de SQL Server 2019 (15.x), la valeur par défaut est 1000 Go.

La limite MAX_STORAGE_SIZE_MB n’est pas strictement appliquée. La taille de stockage est vérifiée seulement quand le Magasin des requêtes écrit des données sur le disque. Cet intervalle est défini par l’option DATA_FLUSH_INTERVAL_SECONDS ou par l’option de la boîte de dialogue Magasin des requêtes de Management Studio Intervalle de vidage des données. La valeur par défaut de l’intervalle est 900 secondes (ou 15 minutes).

Si le Magasin des requêtes a enfreint la limite de MAX_STORAGE_SIZE_MB entre les vérifications de taille de stockage, il passe en mode lecture seule. Si l’option SIZE_BASED_CLEANUP_MODE est activée, le mécanisme de nettoyage permettant d’appliquer la limite MAX_STORAGE_SIZE_MB est également déclenché.

Une fois que suffisamment d’espace a été effacé, le mode Magasin des requêtes bascule automatiquement en lecture-écriture.

INTERVAL_LENGTH_MINUTES

Détermine l’intervalle de temps auquel les données des statistiques d’exécution du runtime sont agrégées dans le magasin des requêtes. Pour optimiser l'espace, les statistiques d'exécution du runtime du magasin de statistiques du runtime sont agrégées sur une période fixe. Cette fenêtre de temps fixe est configurée à l'aide de l'argument INTERVAL_LENGTH_MINUTES. INTERVAL_LENGTH_MINUTES est de type bigint. La valeur par défaut est 60.

SIZE_BASED_CLEANUP_MODE { AUTO | OFF }

Contrôle si le nettoyage s’active automatiquement quand la quantité totale de données approche de la taille maximale.

• AUTO

  Le nettoyage basé sur la taille est automatiquement activé lorsque la taille sur le disque atteint 90% de MAX_STORAGE_SIZE_MB. Le nettoyage basé sur la taille supprime les requêtes les moins coûteuses et les plus anciennes en premier. Il s’arrête à environ 80 % de MAX_STORAGE_SIZE_MB. Il s’agit de la valeur de configuration par défaut.

• OFF

  Le nettoyage basé sur la taille n’est pas activé automatiquement.

SIZE_BASED_CLEANUP_MODE est de type nvarchar.

QUERY_CAPTURE_MODE { ALL | AUTO | CUSTOM | NONE }

Désigne le mode de capture de requête actif actuellement. Chaque mode définit des stratégies de capture de requête spécifiques. QUERY_CAPTURE_MODE est de type nvarchar.

• ALL

  Capture toutes les requêtes. ALL est la valeur de configuration par défaut pour SQL Server (de SQL Server 2016 (13.x) à SQL Server 2017 (14.x)).

• AUTO

  Capturer les requêtes pertinentes en fonction du nombre d’exécutions et de la consommation de ressources. Il s’agit de la valeur de configuration par défaut pour SQL Server (à compter de SQL Server 2019 (15.x)) et Azure SQL Database.

• Aucune

  Cesser la capture des nouvelles requêtes. Le Magasin des requêtes continue de collecter des statistiques de compilation et d’exécution pour les requêtes qui ont déjà été capturées. Utilisez cette configuration avec précaution, car vous risquez de ne pas capturer des requêtes importantes.

• CUSTOM

  S’applique à : SQL Server (à compter de SQL Server 2019 (15.x))

  Permet de contrôler les options QUERY_CAPTURE_POLICY. Des stratégies de capture personnalisées peuvent aider le Magasin des requêtes à capturer les requêtes les plus importantes dans votre charge de travail. Pour connaître les options personnalisables, consultez <query_capture_policy_option_list>.

max_plans_per_query

Définit le nombre maximal de plans gérés pour chaque requête. MAX_PLANS_PER_QUERY est de type int. La valeur par défaut est 200.

WAIT_STATS_CAPTURE_MODE { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x))

Contrôle si les statistiques d’attente sont capturées par requête.

• ACTIVÉ

  Les informations des statistiques d’attente par requête sont capturées. Il s’agit de la valeur de configuration par défaut.

• OFF

  Les informations de statistiques d’attente par requête ne sont pas capturées.

<query_capture_policy_option_list> :: =

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x))

Contrôle les options de stratégie de capture du Magasin des requêtes. Sauf pour STALE_CAPTURE_POLICY_THRESHOLD, ces options définissent dans la valeur de la durée de validité de la stratégie de capture les conditions OR qui doivent intervenir pour que les requêtes soient capturées.

À compter de SQL Server 2019 (15.x), le QUERY_CAPTURE_MODE = AUTO paramètre capture Magasin des requêtes détails quand l’un des seuils suivants est atteint :

• EXECUTION_COUNT = 30 exécutions = nombre d’exécutions
• TOTAL_COMPILE_CPU_TIME_MS = 1 seconde = temps de compilation en millisecondes
• TOTAL_EXECUTION_CPU_TIME_MS = 100 ms = durée processeur d’exécution en millisecondes

Par exemple :

EXECUTION_COUNT = 30,
TOTAL_COMPILE_CPU_TIME_MS = 1000,
TOTAL_EXECUTION_CPU_TIME_MS = 100

Vous pouvez personnaliser ces options avec QUERY_CAPTURE_MODE = CUSTOM :

• STALE_CAPTURE_POLICY_THRESHOLD = entier { DAYS | HOURS }

  Définit la période d’évaluation pour déterminer si une requête doit être capturée. La valeur par défaut est de 1 jour, les valeurs possibles oscillant entre 1 heure et sept jours.

• EXECUTION_COUNT = entier

  Définit le nombre d’exécutions d’une requête pendant la période d’évaluation. La valeur par défaut est 30, ce qui signifie que pour la durée de validité par défaut de la stratégie de capture, une requête doit s’exécuter au moins 30 fois dans la même journée pour devenir persistante dans le Magasin des requêtes. EXECUTION_COUNT est de type int.

• TOTAL_COMPILE_CPU_TIME_MS = entier

  Définit le temps UC de compilation écoulé total utilisé par une requête pendant la période d’évaluation. La valeur par défaut est 1000. Ainsi, pour le seuil par défaut de la stratégie de capture obsolète, une requête doit présenter au total au moins une seconde de temps processeur écoulé pendant la compilation de la requête dans une même journée pour devenir persistante dans le Magasin des requêtes. TOTAL_COMPILE_CPU_TIME_MS est de type int.

• TOTAL_EXECUTION_CPU_TIME_MS = entier

  Définit le temps UC d’exécution écoulé total utilisé par une requête pendant la période d’évaluation. La valeur par défaut est 100 ; ainsi, pour la durée de validité par défaut de la stratégie de capture, une requête doit avoir un total d’au moins 100 ms de temps processeur écoulé pendant l’exécution dans une même journée pour devenir persistante dans le Magasin des requêtes. TOTAL_EXECUTION_CPU_TIME_MS est de type int.

<recovery_option> ::=

S’applique à : SQL Server

Contrôle les options de récupération de base de données et la vérification des erreurs d'E/S disque.

• FULL

  Assure une récupération complète après la défaillance d'un support à l'aide des sauvegardes de journaux des transactions. Si un fichier de données est endommagé, la récupération des supports peut restaurer toutes les transactions validées. Pour plus d’informations, consultez modèles recovery.

• BULK_LOGGED

  Assure une récupération après la défaillance d’un support. Cette option associe des performances optimales et une utilisation minimale de l’espace réservé aux fichiers journaux pour certaines opérations en bloc ou de grande échelle. Pour plus d’informations sur les opérations pouvant être journalisées minimalement, consultez Le journal des transactions. Avec le mode de récupération BULK_LOGGED, ces opérations font l'objet d'une journalisation minimale. Pour plus d’informations, consultez modèles recovery.

• SIMPLE

  Une stratégie de sauvegarde simple utilisant un espace de journalisation minimal est fournie. L’espace réservé aux fichiers journaux peut être automatiquement réutilisé lorsqu’il n’est plus utilisé pour la récupération des défaillances serveur. Pour plus d’informations, consultez modèles recovery.

  Important

  Le mode de récupération simple est plus facile à gérer que les deux autres modes, mais le risque de perte de données est plus élevé lorsqu'un fichier de données est endommagé. Toutes les modifications apportées depuis la dernière sauvegarde de la base de données ou de la sauvegarde différentielle de la base de données sont perdues et doivent être réintroduites manuellement.

Le mode de récupération par défaut est déterminé par le mode de récupération de la base de données système model. Pour plus d’informations sur la sélection du modèle de récupération approprié, consultez modèles de récupération.

Vous pouvez déterminer l’état de cette option en consultant les colonnes recovery_model et recovery_model_desc de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété Recovery de la fonction DATABASEPROPERTYEX.

TORN_PAGE_DETECTION { ON | OFF }

• ACTIVÉ

  Les pages incomplètes peuvent être détectées par le Moteur de base de données.

• OFF

  Les pages incomplètes ne peuvent pas être détectées par le Moteur de base de données.

PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }

Détecte les pages de base de données endommagées résultant d'erreurs de chemin d'E/S disque. Les erreurs de chemin d’E/S disque peuvent être la cause de problèmes de corruption de base de données. Ces erreurs sont le plus souvent provoquées par des pannes d’alimentation ou des défaillances matérielles du disque qui se produisent au moment où la page est écrite sur le disque.

• CHECKSUM

  Calcule une somme de contrôle du contenu d'une page entière et stocke la valeur dans l'en-tête de page lorsque celle-ci est écrite sur le disque. Lorsque la page est ensuite lue à partir du disque, la somme de contrôle est recalculée et le résultat est comparé à la valeur préalablement stockée dans l'en-tête de la page. Si les valeurs diffèrent, le message d’erreur 824 (indiquant l’échec d’une somme de contrôle) est signalé dans le journal des erreurs SQL Server et le journal des événements Windows. Un échec de somme de contrôle indique un problème de chemin d'E/S. Pour en déterminer la cause, vous devez examiner le matériel, les pilotes de microprogrammes, le BIOS, les pilotes de filtre (par exemple un logiciel antivirus) et d'autres composants de chemin d'E/S.

• TORN_PAGE_DETECTION

  Enregistre un modèle spécifique de 2 bits pour chaque secteur de 512 octets dans la page de base de données de 8 kilo-octets (Ko) et le stocke dans l'en-tête de page de base de données au moment où la page est écrite sur le disque. Lorsque la page est ensuite lue à partir du disque, les bits endommagés stockés dans l'en-tête de la page sont comparés aux informations réelles du secteur concerné.

  Lorsque les valeurs ne concordent pas, cela signifie que seule une partie de la page a été écrite sur le disque. Dans un tel cas, le message d'erreur 824 (indiquant une erreur de page endommagée) est signalé dans le journal des erreurs SQL Server et le journal des événements Windows. Les pages endommagées sont généralement détectées par la récupération de base de données s’il s’agit réellement d’une écriture de page incomplète. Toutefois, les échecs de chemin d'E/S peuvent donner lieu à tout moment à une page endommagée.

• Aucune

  Les écritures de pages de base de données ne génèrent pas de valeur CHECKSUM ou TORN_PAGE_DETECTION. SQL Server ne vérifie pas une somme de contrôle ou une page déchirée pendant une lecture même si une valeur CHECKSUM ou TORN_PAGE_DETECTION est présente dans l’en-tête de page.

Prenez en considération les points suivants lorsque vous utilisez l'option PAGE_VERIFY :

• La valeur par défaut est CHECKSUM.

• Quand une base de données utilisateur ou système est mise à niveau vers SQL Server 2005 (9.x) ou une version ultérieure, la valeur de PAGE_VERIFY (NONE ou TORN_PAGE_DETECTION) n’est pas changée. Nous vous recommandons de changer pour CHECKSUM.

  Notes

  Dans les versions antérieures de SQL Server, l’option de base de données PAGE_VERIFY est définie sur NONE pour la base de données tempdb. Elle n’est pas modifiable. À compter de SQL Server 2008 (10.0.x), la valeur par défaut pour la base de données tempdb est CHECKSUM pour les nouvelles installations de SQL Server. Quand vous mettez à niveau une installation SQL Server, la valeur par défaut reste NONE. L'option peut être modifiée. Nous vous recommandons d’utiliser CHECKSUM pour la base de données tempdb.

• TORN_PAGE_DETECTION peut utiliser moins de ressources, mais fournit un sous-ensemble minimal de la protection CHECKSUM.

• Il est possible de définir PAGE_VERIFY sans mettre la base de données hors connexion, la verrouiller ni empêcher d'une quelconque façon la concurrence pour cette base de données.

• CHECKSUM et TORN_PAGE_DETECTION s'excluent mutuellement. Les deux options ne peuvent pas être activées en même temps.

Lors de la détection d'une page endommagée ou d'un échec de somme de contrôle, vous pouvez procéder à une récupération en restaurant les données ou en reconstruisant éventuellement l'index si la défaillance se limite à des pages d'index. En présence d'une erreur de somme de contrôle, pour déterminer le type de la ou des pages de données affectées, exécutez DBCC CHECKDB. Pour plus d’informations sur les options de restauration, voir Arguments RESTORE. Bien que la restauration des données résout le problème d’altération des données, la cause racine (par exemple, une défaillance matérielle du disque) doit être diagnostiqué et corrigé dès que possible pour éviter les erreurs persistantes.

SQL Server réessaye toute lecture qui échoue avec une somme de contrôle, une page déchirée ou une autre erreur d’E/S quatre fois. Si la lecture réussit lors de l’une de ces tentatives, un message est écrit dans le journal des erreurs. Commande qui a déclenché la lecture continue. La commande échoue avec le message d’erreur 824 si les nouvelles tentatives échouent.

Pour plus d’informations sur les messages d’erreur 823, 824 et 825, consultez :

• résoudre les problèmes d’erreur MSSQLSERVER 823
• résoudre les problèmes d’erreur MSSQLSERVER 824
• résoudre les problèmes d’erreur MSSQLSERVER 825 (nouvelle tentative de lecture).

Vous pouvez déterminer la valeur actuelle de cette option en consultant la colonne page_verify_option de la vue de catalogue sys.databases ou la propriété IsTornPageDetectionEnabled de la fonction DATABASEPROPERTYEX.

<remote_data_archive_option> ::=

S’applique à : SQL Server (à compter de SQL Server 2016 (13.x))

Active ou désactive Stretch Database pour la base de données. Pour plus d'informations, consultez Stretch Database.

REMOTE_DATA_ARCHIVE = { ON ( SERVER = <server_name>, { CREDENTIAL = <db_scoped_credential_name> | FEDERATED_SERVICE_ACCOUNT = ON | OFF } ) | OFF

• ACTIVÉ

  Active Stretch Database pour la base de données. Pour plus d’informations, notamment sur les prérequis supplémentaires, consultez Activer Stretch Database pour une base de données.

  Nécessite l’autorisation db_owner pour activer Stretch Database pour une table. Nécessite les autorisations db_owner et CONTROL DATABASE pour activer Stretch Database pour une base de données.

  • SERVER = <server_name>

    Spécifie l’adresse du serveur Azure. Incluez la partie .database.windows.net du nom. Par exemple : MyStretchDatabaseServer.database.windows.net.

  • CREDENTIAL = <db_scoped_credential_name>

    Spécifie les informations d’identification délimitées à la base de données utilisées par l’instance de SQL Server pour se connecter au serveur Azure. Vérifiez que les informations d’identification existent avant d’exécuter cette commande. Pour plus d’informations, voir CREATE DATABASE SCOPED CREDENTIAL.

  • FEDERATED_SERVICE_ACCOUNT = { ON | OFF }

    Vous pouvez utiliser un compte de service fédéré pour que le serveur SQL Server local communique avec le serveur Azure distant quand les conditions suivantes sont toutes remplies.

    • Le compte de service sous lequel l’instance de SQL Server s’exécute est un compte de domaine.
    • Le compte de domaine appartient à un domaine dont Active Directory est fédéré avec l’ID Microsoft Entra.
    • Le serveur Azure distant est configuré pour prendre en charge l’authentification Microsoft Entra.
    • Le compte de service sous lequel s’exécute l’instance de SQL Server doit être configuré comme un compte dbmanager ou sysadmin sur le serveur Azure distant.

    En outre, si vous spécifiez que le compte de service fédéré est ON, vous ne pouvez pas spécifier l’argument CREDENTIAL. Vous devez fournir l’argument CREDENTIAL si vous spécifiez OFF.

• OFF

  Désactive Stretch Database pour la base de données. Pour plus d’informations, consultez Désactiver Stretch Database et récupérer les données distantes.

  Vous pouvez uniquement désactiver Stretch Database pour une base de données une fois que celle-ci ne contient plus de tables qui sont activées pour Stretch Database. Lorsque vous désactivez Stretch Database, la migration des données s’arrête. En outre, les résultats de la requête n’incluent plus les résultats de tables distantes.

  Le fait de désactiver Stretch n’a pas pour effet de supprimer la base de données distante. Pour supprimer la base de données distante, supprimez-la en utilisant le portail Azure.

PERSISTENT_LOG_BUFFER

s’applique à: SQL Server 2017 (14.x) et versions ultérieures.

Lorsque cette option est spécifiée, la mémoire tampon du journal des transactions est créée sur un volume situé sur un périphérique de disque soutenu par la mémoire de classe de stockage (NVDIMM-N stockage nonvolatile), également appelé mémoire tampon de journal persistante. Pour plus d’informations, consultez accélération de la latence de validation des transactions à l’aide du de mémoire de classe de stockage et Ajouter une mémoire tampon de journal persistante à une base de données.

<service_broker_option> ::=

S’applique à : SQL Server

Contrôle les options Service Broker suivantes : active ou désactive la remise de messages, définit un nouvel identificateur Service Broker ou définit les priorités de conversation sur ON ou OFF.

ENABLE_BROKER

Spécifie que Service Broker est activé pour la base de données spécifiée. La remise des messages est démarrée et l’indicateur is_broker_enabled est défini sur true dans la vue de catalogue sys.databases. La base de données conserve l’identificateur Service Broker existant. Service Broker ne peut pas être activé lorsque la base de données est la base de données principale dans une configuration de mise en miroir de bases de données.

DISABLE_BROKER

Spécifie que Service Broker est désactivé pour la base de données spécifiée. La remise des messages est arrêtée et l’indicateur is_broker_enabled est défini sur false dans la vue de catalogue sys.databases. La base de données conserve l’identificateur Service Broker existant.

NEW_BROKER

Spécifie que la base de données doit recevoir un nouvel identificateur Service Broker. La base de données agit comme un nouveau Service Broker. Toutes les conversations existantes dans la base de données sont ainsi immédiatement supprimées sans générer de message de fin de dialogue. Tout itinéraire qui fait référence à l'ancien identificateur Service Broker doit être recréé avec le nouvel identificateur.

ERROR_BROKER_CONVERSATIONS

Spécifie que la remise de messages Service Broker est activée. Ce paramètre préserve l’identificateur Service Broker existant de la base de données. Service Broker met fin à toutes les conversations de la base de données avec une erreur. Ce paramètre permet aux applications d’exécuter un nettoyage régulier des conversations existantes.

HONOR_BROKER_PRIORITY { ON | OFF }

• ACTIVÉ

  Les opérations d'envoi prennent en considération les niveaux de priorité assignés aux conversations. Les messages issus de conversations dont les niveaux de priorité sont élevés sont envoyés avant ceux issus de conversations dont les niveaux de priorité sont faibles.

• OFF

  Les opérations d'envoi s'exécutent comme si toutes les conversations étaient dotées du niveau de priorité par défaut.

Les modifications apportées à l'option HONOR_BROKER_PRIORITY prennent effet immédiatement pour les nouveaux dialogues ou les dialogues qui n'ont pas de messages en attente d'envoi. Les boîtes de dialogue avec des messages à envoyer lorsque ALTER DATABASE est exécuté ne récupèrent pas le nouveau paramètre tant que certains messages de la boîte de dialogue ne sont pas envoyés. La durée nécessaire pour que tous les dialogues commencent à utiliser le nouveau paramètre peut varier considérablement.

La valeur actuelle de cette propriété est indiqué dans la colonne is_broker_priority_honored de la vue de catalogue sys.databases.

<snapshot_option> ::=

Calcule le niveau d’isolation de la transaction.

ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

• ACTIVÉ

  Active l'option Instantané au niveau de la base de données. Lorsque cette option est activée, les instructions DML commencent à générer des versions de ligne même quand aucune transaction n’utilise l’isolement d’instantané. Une fois que cette option est activée, les transactions peuvent spécifier le niveau d'isolement des transactions SNAPSHOT. Quand une transaction s'exécute au niveau d'isolement SNAPSHOT, toutes les instructions voient un instantané des données tel qu'il existe au début de la transaction. Si une transaction exécutée au niveau d'isolement SNAPSHOT accède à des données dans plusieurs bases de données, l'option ALLOW_SNAPSHOT_ISOLATION doit avoir la valeur ON dans toutes les bases de données ou chaque instruction de la transaction doit utiliser des indicateurs de verrouillage sur toute référence d'une clause FROM à une table de la base de données dont l'option ALLOW_SNAPSHOT_ISOLATION a la valeur OFF.

• OFF

  Désactive l'option Instantané au niveau de la base de données. Les transactions peuvent spécifier le niveau d’isolation de la transaction SNAPSHOT.

Lorsque vous modifiez l'état de ALLOW_SNAPSHOT_ISOLATION (de ON à OFF ou inversement), ALTER DATABASE ne retourne pas le contrôle à l'appelant tant que toutes les transactions existantes dans la base de données n'ont pas été validées. Si la base de données présente déjà l'état spécifié dans l'instruction ALTER DATABASE, le contrôle est immédiatement retourné à l'appelant. Si l’instruction ALTER DATABASE n’est pas retournée rapidement, utilisez sys.dm_tran_active_snapshot_database_transactions pour déterminer si certaines transactions sont de longue durée. Si l'instruction ALTER DATABASE est annulée, la base de données conserve l'état qu'elle présentait au démarrage de ALTER DATABASE. la vue de catalogue sys.databases indique l’état des transactions d’isolement d’instantané dans la base de données. Si snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, la commande ALTER DATABASE ... ALLOW_SNAPSHOT_ISOLATION OFF interrompt six secondes et réessaye l’opération.

Vous ne pouvez pas modifier l’état de ALLOW_SNAPSHOT_ISOLATION si la base de données est hors connexion (OFFLINE).

Si vous définissez ALLOW_SNAPSHOT_ISOLATION dans une base de données READ_ONLY, le paramètre est conservé si la base de données est définie ultérieurement sur READ_WRITE.

Vous pouvez modifier les paramètres ALLOW_SNAPSHOT_ISOLATION pour les bases de données master, model, msdb et tempdb. Le paramètre est conservé à chaque arrêt et redémarrage de l’instance du Moteur de base de données si vous changez le paramètre pour tempdb. Si le paramètre est modifié pour model, il devient le paramètre par défaut de toutes les bases de données créées à l’exception de tempdb.

Cette option a la valeur ON par défaut pour les bases de données master et msdb.

La valeur actuelle de cette option peut être déterminée en examinant la colonne snapshot_isolation_state dans la vue de catalogue sys.databases.

READ_COMMITTED_SNAPSHOT { ON | OFF }

• ACTIVÉ

  Active l'option d'instantané de lecture validée au niveau de la base de données. Lorsque cette option est activée, les instructions DML commencent à générer des versions de ligne même quand aucune transaction n’utilise l’isolement d’instantané. Une fois que cette option est activée, les transactions qui définissent le niveau d'isolement de lecture validée utilisent le contrôle de version de ligne au lieu du verrouillage. Toutes les instructions voient un instantané des données telles qu’elles existent au début de l’instruction quand une transaction est exécutée au niveau d’isolation READ COMMITTED.

• OFF

  Désactive l'option d'instantané de lecture validée au niveau de la base de données. Les transactions spécifiant le niveau d'isolation READ COMMITTED utilisent le verrouillage.

Pour définir READ_COMMITTED_SNAPSHOT sur ON ou sur OFF, il ne doit exister aucune connexion active à la base de données, à l’exception de la connexion exécutant la commande ALTER DATABASE. Toutefois, il n’est pas nécessaire que la base de données soit en mode mono-utilisateur. Vous ne pouvez pas modifier l’état de cette option si la base de données est hors connexion (OFFLINE).

Si vous définissez READ_COMMITTED_SNAPSHOT dans une base de données READ_ONLY, le paramètre est conservé lorsque la base de données est définie ultérieurement sur READ_WRITE.

Il n’est pas possible de définir READ_COMMITTED_SNAPSHOT sur ON pour les bases de données système master, tempdb et msdb. Si le paramètre est modifié pour model, il devient le paramètre par défaut de toutes les bases de données créées à l’exception de tempdb.

La valeur actuelle de cette option peut être déterminée en examinant la colonne is_read_committed_snapshot_on dans la vue de catalogue sys.databases.

MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT { ON | OFF }

S’applique à : SQL Server (à compter de SQL Server 2014 (12.x))

• ACTIVÉ

  Quand le niveau d’isolation de la transaction est défini sur un niveau inférieur à SNAPSHOT, toutes les opérations en Transact-SQL interprété sur les tables à mémoire optimisée sont exécutées avec l’isolation SNAPSHOT. Des niveaux d’isolation inférieurs à snapshot sont, par exemple, READ COMMITTED ou READ UNCOMMITTED. Ces opérations sont exécutées si le niveau d’isolation de la transaction est défini explicitement sur le niveau de la session, ou si la valeur par défaut est utilisée implicitement.

• OFF

  N’élève pas le niveau d’isolation pour les opérations en Transact-SQL interprété sur les tables à mémoire optimisée.

Vous ne pouvez pas modifier l’état de MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT si la base de données est hors connexion (OFFLINE).

L’option par défaut est OFF.

La valeur actuelle de cette option peut être déterminée en examinant la colonne is_memory_optimized_elevate_to_snapshot_on dans la vue de catalogue sys.databases.

<sql_option> ::=

Contrôle les options de conformité ANSI au niveau de la base de données.

ANSI_NULL_DEFAULT { ON | OFF }

Détermine la valeur par défaut, NULL ou NOT NULL, d’une colonne, d’un type CLR défini par l’utilisateur dont le paramètre d’acceptation des valeurs NULL n’est pas défini de façon explicite dans les instructions CREATE TABLE ou ALTER TABLE. Les colonnes définies avec des contraintes suivent les règles de contrainte, quel que soit ce paramètre.

• ACTIVÉ

  La valeur par défaut d’une colonne non définie est NULL.

• OFF

  La valeur par défaut d’une colonne non définie est NOT NULL.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre défini pour ANSI_NULL_DEFAULT au niveau de la base de données par défaut. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_NULL_DEFAULT pour la session par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET ANSI_NULL_DFLT_ON.

Pour garantir la compatibilité ANSI, l'activation (ON) de l'option de base de données ANSI_NULL_DEFAULT entraîne la définition de NULL comme valeur par défaut de la base de données.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_ansi_null_default_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAnsiNullDefault de la fonction DATABASEPROPERTYEX.

ANSI_NULLS { ON | OFF }

• ACTIVÉ

  Toutes les comparaisons avec une valeur Null produisent le résultat UNKNOWN (inconnu).

• OFF

  Les comparaisons de valeurs non Unicode avec une valeur nulle génèrent la valeur TRUE si les deux valeurs sont NULL.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut défini pour ANSI_NULLS au niveau de la base de données. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_NULLS pour la session par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET ANSI_NULLS.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_ansi_nulls_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAnsiNullsEnabled de la fonction DATABASEPROPERTYEX.

ANSI_PADDING { ON | OFF }

• ACTIVÉ

  Les chaînes sont complétées pour avoir la même longueur avant leur conversion. Elles sont également complétées pour avoir la même longueur avant leur insertion dans un type de données varchar ou nvarchar.

• OFF

  Cette option insère des espaces à droite dans les valeurs de type character dans des colonnes varchar ou nvarchar. Cette option laisse également les zéros à droite dans les valeurs de type binary insérées dans des colonnes varbinary. Les valeurs ne sont pas complétées à concurrence de la longueur de la colonne.

  Lorsque cette option a la valeur OFF, elle affecte uniquement la définition des nouvelles colonnes.

Les colonnes char(n) et binary(n) qui acceptent des valeurs NULL sont complétées à concurrence de la longueur de la colonne lorsque ANSI_PADDING a la valeur ON. Les espaces à droite et les zéros sont tronqués lorsque ANSI_PADDING a la valeur OFF. Les colonnes char(n) et binary(n) qui n’acceptent pas les valeurs NULL sont toujours complétées à concurrence de la longueur de la colonne.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre de ANSI_PADDING au niveau de la base de données par défaut. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_PADDING pour la session par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET ANSI_PADDING.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_ansi_padding_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAnsiPaddingEnabled de la fonction DATABASEPROPERTYEX.

ANSI_WARNINGS { ON | OFF }

• ACTIVÉ

  Des erreurs ou avertissements sont émis si des conditions telles que « division par zéro » sont vérifiées. Des erreurs et avertissements sont également générés lorsque des valeurs Null apparaissent dans des fonctions d’agrégation.

• OFF

  Aucun avertissement n'est généré et des valeurs Null sont retournées lorsque des conditions telles qu'une division par zéro se manifestent.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut de ANSI_NULLS défini au niveau de la base de données. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_WARNINGS pour la session par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET ANSI_WARNINGS.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_ansi_warnings_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAnsiWarningsEnabled de la fonction DATABASEPROPERTYEX.

ARITHABORT { ON | OFF }

• ACTIVÉ

  Arrête une requête lorsqu'un dépassement de capacité ou une division par zéro se produit durant son exécution.

• OFF

  Un message d’avertissement s’affiche quand l’une de ces erreurs se produit. Le traitement de la requête, du lot ou de la transaction se poursuit comme s’il n’y avait pas d’erreur, même si un message d’avertissement s’affiche.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_arithabort_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsArithmeticAbortEnabled de la fonction DATABASEPROPERTYEX.

COMPATIBILITY_LEVEL = { 160 | 150 | 140 | 130 | 120 | 110 | 100 }

Pour plus d’informations, consultez niveau de compatibilité ALTER DATABASE.

CONCAT_NULL_YIELDS_NULL { ON | OFF }

• ACTIVÉ

  Le résultat d'une concaténation est NULL lorsque l'un des deux opérandes est NULL. Par exemple, la concaténation de la chaîne de caractères « Ceci est » et de NULL retourne la valeur NULL au lieu de la valeur « Ceci est ».

• OFF

  La valeur Null est considérée comme une chaîne de caractères vide.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut de CONCAT_NULL_YIELDS_NULL défini au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à CONCAT_NULL_YIELDS_NULL pour la session lors de la connexion à une instance de SQL Server. Pour plus d’informations, voir SET CONCAT_NULL_YIELDS_NULL.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_concat_null_yields_null_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsNullConcat de la fonction DATABASEPROPERTYEX.

NUMERIC_ROUNDABORT { ON | OFF }

• ACTIVÉ

  Une erreur est générée lors d'une perte de précision dans une expression.

• OFF

  La perte de précision ne génère pas de message d’erreur et le résultat est arrondi en fonction de la précision de la colonne ou de la variable stockant le résultat.

  Important

  NUMERIC_ROUNDABORT doit avoir la valeur OFF lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.

Vous pouvez déterminer l’état de cette option dans la colonne is_numeric_roundabort_on de l’affichage catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsNumericRoundAbortEnabled de la fonction DATABASEPROPERTYEX.

QUOTED_IDENTIFIER { ON | OFF }

• ACTIVÉ

  Des guillemets doubles peuvent être utilisés pour entourer des identificateurs délimités.

  Toutes les chaînes délimitées par des guillemets doubles sont considérées comme des identificateurs d'objet. Les identificateurs entre guillemets n’ont pas à respecter les règles Transact-SQL propres aux identificateurs. Ils peuvent être des mots clés et contenir des caractères non autorisés dans les identificateurs Transact-SQL. Si un guillemet double (") fait partie de l’identificateur, il peut être représenté par deux guillemets doubles ("").

• OFF

  Les identificateurs ne peuvent pas être mis entre guillemets et doivent respecter toutes les règles Transact-SQL applicables aux identificateurs. Les chaînes littérales peuvent être délimitées par des guillemets simples ou doubles.

SQL Server permet également de délimiter les identificateurs par des crochets ([ et ]). Les identificateurs entre crochets peuvent toujours être utilisés, quel que soit le paramètre QUOTED_IDENTIFIER. Pour plus d’informations, consultez identificateurs de base de données.

Lors de la création d’une table, l’option QUOTED IDENTIFIER est toujours stockée avec la valeur ON dans les métadonnées de la table. L’option est stockée même si elle a la valeur OFF au moment de la création de la table.

Les paramètres définis au niveau de la connexion à l'aide de l'instruction SET se substituent au paramètre de base de données par défaut de QUOTED_IDENTIFIER. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à QUOTED_IDENTIFIER par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET QUOTED_IDENTIFIER.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_quoted_identifier_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsQuotedIdentifiersEnabled de la fonction DATABASEPROPERTYEX.

RECURSIVE_TRIGGERS { ON | OFF }

• ACTIVÉ

  L'activation récursive des déclencheurs AFTER est autorisée.

• OFF

  Vous pouvez déterminer l’état de cette option en consultant la colonne is_recursive_triggers_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsRecursiveTriggersEnabled de la fonction DATABASEPROPERTYEX.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_recursive_triggers_on de la vue de catalogue sys.databases ou la propriété IsRecursiveTriggersEnabled de la fonction DATABASEPROPERTYEX.

<suspend_for_snapshot_backup> ::=

S’applique à : SQL Server 2022 (16.x) et versions ultérieures

Suspend les bases de données pour la sauvegarde d’instantanés. Peut définir un groupe d’une ou plusieurs bases de données. Peut désigner le mode de copie uniquement.

SET SUSPEND_FOR_SNAPSHOT_BACKUP = { ON | OFF }

Suspend ou annule la suspension des bases de données. Valeur par défaut : OFF.

MODE = COPY_ONLY

Optionnel. Utilise le mode COPY_ONLY.

<target_recovery_time_option> ::=

S’applique à : SQL Server (à compter de SQL Server 2012 (11.x))

Spécifie la fréquence des points de contrôle indirects en fonction de chaque base de données. À compter de SQL Server 2016 (13.x), la valeur par défaut des nouvelles bases de données est 1 minute, ce qui indique que la base de données utilise des points de contrôle indirects. Pour les versions antérieures, la valeur par défaut est 0, ce qui indique que la base de données utilise des points de contrôle automatiques, dont la fréquence dépend du paramètre d’intervalle de récupération de l’instance de serveur. Microsoft recommande une valeur d’une minute pour la plupart des systèmes.

TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }

• target_recovery_time

  Spécifie la limite maximale de durée de récupération de la base de données spécifiée en cas de sinistre. target_recovery_time est de type int.

• SECONDS

  Indique que target_recovery_time correspond au nombre de secondes.

• MINUTES

  Indique que target_recovery_time correspond au nombre de minutes.

Pour plus d’informations sur les points de contrôle indirects, consultez points de contrôle de base de données.

WITH <termination> ::=

Spécifie le(s) cas où une transaction incomplète doit être restaurée lors d'un changement d'état de la base de données. Lorsque la clause de fin est omise, l’instruction ALTER DATABASE attend indéfiniment s’il existe un verrou quelconque sur la base de données. Une seule clause de fin peut être spécifiée, à la suite des clauses SET.

• ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE

  Indique si la restauration intervient après le nombre de secondes spécifié ou immédiatement.

• NO_WAIT

  Spécifie que la requête échoue si l’état de la base de données ou la modification d’option demandé ne peut pas se terminer immédiatement. Une exécution immédiate signifie ne pas attendre la validation ou la restauration des transactions.

Options définies

Pour récupérer les paramètres actuels des options de base de données, utilisez la vue de catalogue sys.databases ou DATABASEPROPERTYEX.

Quand vous définissez une option de base de données, la nouvelle valeur prend effet immédiatement.

Vous pouvez modifier les valeurs par défaut de l’une des options de base de données afin qu’elles s’appliquent à toutes les nouvelles bases de données créées. Pour cela, modifiez l’option de base de données appropriée dans la base de données model.

Toutes les options de base de données n’utilisent pas la clause WITH <termination> et ne peuvent pas être combinées avec d’autres options. Le tableau suivant répertorie ces options ainsi que l'état de l'option et d'arrêt.

Catégorie d'options	Peut être spécifiée avec d'autres options	Peut utiliser la clause WITH <termination>
<db_state_option>	Oui	Oui
<db_user_access_option>	Oui	Oui
<db_update_option>	Oui	Oui
<delayed_durability_option>	Oui	Oui
<external_access_option>	Oui	Non
<cursor_option>	Oui	Non
<auto_option>	Oui	Non
<sql_option>	Oui	Non
<recovery_option>	Oui	Non
<target_recovery_time_option>	Non	Oui
<database_mirroring_option>	Non	Non
ALLOW_SNAPSHOT_ISOLATION	Non	Non
READ_COMMITTED_SNAPSHOT	Non	Oui
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT	Oui	Oui
<service_broker_option>	Oui	Non
DATE_CORRELATION_OPTIMIZATION	Oui	Oui
<parameterization_option>	Oui	Oui
<change_tracking_option>	Oui	Oui
<db_encryption_option>	Oui	Non
<accelerated_database_recovery>	Oui	Oui

Le cache de plan pour l'instance de SQL Server est effacé par la configuration d'une des options suivantes :

OFFLINE

ONLINE

MODIFY_NAME

COLLATE

READ_ONLY

READ_WRITE

MODIFY FILEGROUP DEFAULT

MODIFY FILEGROUP READ_WRITE

MODIFY FILEGROUP READ_ONLY

Le cache du plan est également vidé dans les scénarios suivants.

• L'option de base de données AUTO_CLOSE est activée (ON). Lorsqu'aucune connexion utilisateur ne fait référence ou n'utilise la base de données, la tâche en arrière-plan essaie de fermer et d'arrêter la base de données automatiquement.
• Vous exécutez plusieurs requêtes sur une base de données dont les options par défaut sont activées. Puis, la base de données est supprimée.
• Un instantané de base de données pour une base de données source est supprimé.
• Vous reconstruisez avec succès le journal des transactions d'une base de données.
• Vous restaurez une sauvegarde de base de données.
• Vous détachez une base de données.

Cette opération entraîne la recompilation de tous les plans d'exécution ultérieurs et peut entraîner une baisse temporaire et brutale des performances des requêtes. Pour chaque mémoire cache effacée du cache du plan, le journal des erreurs de SQL Server contient le message d'information suivant : SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. Ce message est enregistré toutes les cinq minutes si le cache est vidé au cours de cet intervalle de temps.

Exemples

R. Configurer les options d'une base de données

L'exemple suivant définit les options de mode de récupération et de vérification de pages de données pour l'exemple de base de données AdventureWorks2022 .

USE master;
GO
ALTER DATABASE [database_name]
SET RECOVERY FULL PAGE_VERIFY CHECKSUM;
GO

B. Définir la base de données en READ_ONLY

Pour modifier l’état d’une base de données ou d’un groupe de fichiers en READ_ONLY ou en READ_WRITE, vous avez besoin d’un accès exclusif à la base de données. L'exemple ci-dessous illustre le basculement de la base de données en mode SINGLE_USER pour obtenir l'accès exclusif. L'exemple affecte ensuite à la base de données AdventureWorks2022 l'état READ_ONLY et rend à tous les utilisateurs l'accès à la base de données.

USE master;
GO
ALTER DATABASE [database_name]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [database_name]
SET READ_ONLY
GO
ALTER DATABASE [database_name]
SET MULTI_USER;
GO

C. Activer l’isolement d’instantané sur une base de données

L'exemple ci-dessous illustre l'activation de l'option d'infrastructure d'isolement d'instantané pour la base de données AdventureWorks2022 .

USE [database_name];
USE master;
GO
ALTER DATABASE [database_name]
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
    snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'[database_name]';
GO

Le jeu de résultats montre que l'infrastructure d'isolement d'instantané est activée.

name	snapshot_isolation_state	description
[nom_base_de_données]	1	ACTIVÉ

D. Activer, modifier ou désactiver le suivi des modifications

L'exemple ci-dessous illustre l'activation du suivi des modifications pour la base de données AdventureWorks2022 et la définition d'une période de rétention de 2 jours.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

L'exemple suivant illustre comment modifier la période de rétention en spécifiant 3 jours.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

L'exemple ci-dessous illustre comment désactiver le suivi des modifications pour la base de données AdventureWorks2022 .

ALTER DATABASE [database_name]
SET CHANGE_TRACKING = OFF;

E. Activer le magasin des requêtes

S’applique à : SQL Server (à compter de SQL Server 2016 (13.x))

L’exemple suivant active le magasin des requêtes et configure ses paramètres.

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60
    );

F. Activer le Magasin des requêtes avec des statistiques d’attente

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x))

L’exemple suivant active le magasin des requêtes et configure ses paramètres.

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
    );

G. Activer le Magasin des requêtes avec des options de stratégie de capture personnalisées

S’applique à : SQL Server (à compter de SQL Server 2019 (15.x))

L’exemple suivant active le magasin des requêtes et configure ses paramètres.

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Contenu connexe

• Statistiques
• DATABASEPROPERTYEX (Transact-SQL)
• sys.databases
• sys.data_spaces
• sys.database_automatic_tuning_options
• sys.database_automatic_tuning_mode
• niveau de compatibilité ALTER DATABASE
• Mise en miroir de bases de données ALTER DATABASE
• ALTER DATABASE SET HADR
• CREATE DATABASE
• activer et désactiver le suivi des modifications (SQL Server)
• DROP DATABASE (Transact-SQL)
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
• sp_configure
• Meilleures pratiques pour la surveillance des charges de travail avec le magasin des requêtes

SQL Server

* SQL Database *  

SQL Managed Instance

Azure Synapse
Analytics

Microsoft Fabric

 

SQL Database

Les niveaux de compatibilité sont SET options, mais sont décrits dans niveau de compatibilité ALTER DATABASE.

Syntaxe

ALTER DATABASE { database_name | Current }
SET
{
    <option_spec> [ ,...n ] [ WITH <termination> ]
}
;

<option_spec> ::=
{
    <auto_option>
  | <automatic_tuning_option>
  | <change_tracking_option>
  | <cursor_option>
  | <db_encryption_option>
  | <db_update_option>
  | <db_user_access_option>
  | <delayed_durability_option>
  | <parameterization_option>
  | <query_store_options>
  | <snapshot_option>
  | <sql_option>
  | <target_recovery_time_option>
  | <termination>
  | <temporal_history_retention>
}
;

<auto_option> ::=
{
    AUTO_CREATE_STATISTICS { OFF | ON [ ( INCREMENTAL = { ON | OFF } ) ] }
  | AUTO_SHRINK { ON | OFF }
  | AUTO_UPDATE_STATISTICS { ON | OFF }
  | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<automatic_tuning_option> ::=
{
    AUTOMATIC_TUNING = { AUTO | INHERIT | CUSTOM }
  | AUTOMATIC_TUNING ( CREATE_INDEX = { DEFAULT | ON | OFF } )
  | AUTOMATIC_TUNING ( DROP_INDEX = { DEFAULT | ON | OFF } )
  | AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF } )
}

<change_tracking_option> ::=
{
    CHANGE_TRACKING
    {
        = OFF
      | = ON [ ( <change_tracking_option_list > [,...n] ) ]
      | ( <change_tracking_option_list> [,...n] )
    }
}

<change_tracking_option_list> ::=
   {
       AUTO_CLEANUP = { ON | OFF }
     | CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }
   }

<cursor_option> ::=
{
    CURSOR_CLOSE_ON_COMMIT { ON | OFF }
}

<db_encryption_option> ::=
  ENCRYPTION { ON | OFF }

<db_update_option> ::=
  { READ_ONLY | READ_WRITE }

<db_user_access_option> ::=
  { RESTRICTED_USER | MULTI_USER }

<delayed_durability_option> ::= DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

<parameterization_option> ::=
  PARAMETERIZATION { SIMPLE | FORCED }

<query_store_options> ::=
{
  QUERY_STORE
  {
      = OFF
    | = ON [ ( <query_store_option_list> [,... n] ) ]
    | ( < query_store_option_list> [,... n] )
    | CLEAR [ ALL ]
  }
}

<query_store_option_list> ::=
{
  OPERATION_MODE = { READ_WRITE | READ_ONLY }
  | CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = number )
  | DATA_FLUSH_INTERVAL_SECONDS = number
  | MAX_STORAGE_SIZE_MB = number
  | INTERVAL_LENGTH_MINUTES = number
  | SIZE_BASED_CLEANUP_MODE = { AUTO | OFF }
  | QUERY_CAPTURE_MODE = { ALL | AUTO | CUSTOM | NONE }
  | MAX_PLANS_PER_QUERY = number
  | WAIT_STATS_CAPTURE_MODE = { ON | OFF }
  | QUERY_CAPTURE_POLICY = ( <query_capture_policy_option_list> [,...n] )
}

<query_capture_policy_option_list> :: =
{
    STALE_CAPTURE_POLICY_THRESHOLD = number { DAYS | HOURS }
    | EXECUTION_COUNT = number
    | TOTAL_COMPILE_CPU_TIME_MS = number
    | TOTAL_EXECUTION_CPU_TIME_MS = number
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
  | READ_COMMITTED_SNAPSHOT { ON | OFF }
  | MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT { ON | OFF }
}
<sql_option> ::=
{
    ANSI_NULL_DEFAULT { ON | OFF }
  | ANSI_NULLS { ON | OFF }
  | ANSI_PADDING { ON | OFF }
  | ANSI_WARNINGS { ON | OFF }
  | ARITHABORT { ON | OFF }
  | COMPATIBILITY_LEVEL = { 160 | 150 | 140 | 130 | 120 | 110 | 100 }
  | CONCAT_NULL_YIELDS_NULL { ON | OFF }
  | NUMERIC_ROUNDABORT { ON | OFF }
  | QUOTED_IDENTIFIER { ON | OFF }
  | RECURSIVE_TRIGGERS { ON | OFF }
}

<termination>::=
{
    ROLLBACK AFTER integer [ SECONDS ]
  | ROLLBACK IMMEDIATE
  | NO_WAIT
}

<temporal_history_retention>::=TEMPORAL_HISTORY_RETENTION { ON | OFF }

Arguments

database_name

Nom de la base de données à modifier.

• CURRENT

  CURRENT exécute l’action dans la base de données active. CURRENT n’est pas pris en charge pour toutes les options dans tous les contextes. Si CURRENT échoue, fournissez le nom de la base de données.

<auto_option> ::=

Contrôle les options automatiques.

AUTO_CREATE_STATISTICS { ON | OFF }

• ACTIVÉ

  L’optimiseur de requête crée si nécessaire des statistiques sur les colonnes uniques des prédicats de requête, afin d’améliorer les plans de requête et les performances des requêtes. Ces statistiques de colonnes uniques sont créées quand l’optimiseur de requête compile les requêtes. Les statistiques de colonnes uniques sont créées uniquement sur les colonnes qui ne constituent pas déjà la première colonne d’un objet de statistiques existant.

  La valeur par défaut est ON. Nous vous recommandons d'utiliser le paramètre par défaut pour la plupart des bases de données.

• OFF

  L’optimiseur de requête ne crée pas de statistiques sur les colonnes uniques des prédicats de requête quand il compile les requêtes. Si cette option a la valeur OFF, il peut en résulter des plans de requête non optimisés et une dégradation des performances des requêtes.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_create_stats_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAutoCreateStatistics de la fonction DATABASEPROPERTYEX.

Pour plus d’informations, consultez la section « Options des statistiques » dans Statistiques.

INCREMENTAL = ON | OFF

Définissez AUTO_CREATE_STATISTICS sur la valeur ON, et INCREMENTAL sur la valeur ON. Ce paramètre crée automatiquement des statistiques incrémentielles sur les statistiques incrémentielles sont prises en charge. La valeur par défaut est OFF. Pour plus d’informations, voir CREATE STATISTICS.

AUTO_SHRINK { ON | OFF }

• ACTIVÉ

  Les fichiers de base de données peuvent faire l'objet d'une réduction périodique. Sauf si vous avez une exigence spécifique, ne définissez pas l’option de base de données AUTO_SHRINK sur ON. Pour plus d’informations, consultez Réduction d’une base de données.

Les fichiers de données et les fichiers journaux peuvent être automatiquement réduits. AUTO_SHRINK ne réduit la taille du journal des transactions que si vous définissez la base de données sur le mode de récupération SIMPLE ou si vous sauvegardez le journal. Si la valeur spécifiée est OFF, les fichiers de base de données ne sont pas réduits automatiquement lors des vérifications périodiques de l’espace inutilisé.

L'option AUTO_SHRINK provoque un compactage dès qu'un fichier comprend plus de 25 % d'espace inutilisé. L’option provoque une réduction du fichier à l’une de deux tailles. Il est réduit à la taille plus grande retenue :

• La taille où 25 pour cent du fichier correspond à l’espace inutilisé
• La taille du fichier quand il a été créé

Vous ne pouvez pas réduire une base de données en lecture seule.

• OFF

  Les fichiers de base de données ne sont pas automatiquement réduits pendant les vérifications périodiques de l’espace inutilisé.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_shrink_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAutoShrink de la fonction DATABASEPROPERTYEX.

AUTO_UPDATE_STATISTICS { ON | OFF }

• ACTIVÉ

  Spécifie que l’optimiseur de requête met à jour les statistiques quand elles sont utilisées par une requête et quand elles sont susceptibles d’être obsolètes. Les statistiques deviennent obsolètes après que des opérations d'insertion, de mise à jour, de suppression ou de fusion ont modifié la distribution des données dans la table ou la vue indexée. L’optimiseur de requête détermine si les statistiques sont obsolètes en comptant le nombre de modifications de données depuis la dernière mise à jour des statistiques et en comparant le nombre de modifications à un seuil. Ce seuil est basé sur le nombre de lignes contenues dans la table ou la vue indexée.

  L’optimiseur de requête vérifie s’il existe des statistiques obsolètes avant de compiler une requête et d’exécuter un plan de requête mis en cache. L’optimiseur de requête utilise les colonnes, les tables et les vues indexées du prédicat de requête pour identifier les statistiques susceptibles d’être obsolètes. L’optimiseur de requête détermine ces informations avant de compiler une requête. Avant d’exécuter un plan de requête mis en cache, le Moteur de base de données vérifie que le plan de requête référence des statistiques à jour.

  L'option AUTO_UPDATE_STATISTICS s'applique aux statistiques créées pour les index, aux colonnes uniques contenues dans les prédicats de requête et aux statistiques créées à l'aide de l'instruction CREATE STATISTICS. Cette option s'applique également aux statistiques filtrées.

  La valeur par défaut est ON. Nous vous recommandons d'utiliser le paramètre par défaut pour la plupart des bases de données.

  Utilisez l'option AUTO_UPDATE_STATISTICS_ASYNC pour spécifier si les statistiques doivent être mises à jour en mode synchrone ou asynchrone.

• OFF

  Spécifie que l’optimiseur de requête ne met pas à jour les statistiques quand elles sont utilisées par une requête. L’optimiseur de requête ne met pas non plus à jour les statistiques quand elles sont susceptibles d’être obsolètes. Si cette option a la valeur OFF, il peut en résulter des plans de requête non optimisés et une dégradation des performances des requêtes.

  Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_update_stats_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAutoUpdateStatistics de la fonction DATABASEPROPERTYEX.

  Pour plus d’informations, consultez la section « Options des statistiques » dans Statistiques.

AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

• ACTIVÉ

  Spécifie que les mises à jour des statistiques pour l'option AUTO_UPDATE_STATISTICS sont asynchrones. L’optimiseur de requête n’attend pas la fin des mises à jour des statistiques pour compiler les requêtes.

  Affecter la valeur ON à cette option n'a aucun effet à moins que AUTO_UPDATE_STATISTICS n'ait également la valeur ON.

  Par défaut, l’option AUTO_UPDATE_STATISTICS_ASYNC est définie sur OFF ; l’optimiseur de requête met à jour les statistiques en mode synchrone.

• OFF

  Spécifie que les mises à jour des statistiques pour l'option AUTO_UPDATE_STATISTICS sont synchrones. L’optimiseur de requête attend la fin des mises à jour des statistiques pour compiler les requêtes.

  Affecter la valeur OFF à cette option n'a aucun effet à moins que AUTO_UPDATE_STATISTICS n'ait également la valeur ON.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_update_stats_async_on de la vue de catalogue sys.databases.

Pour plus d’informations décrivant quand utiliser des mises à jour de statistiques synchrones ou asynchrones, consultez la section « Options des statistiques » dans Statistiques.

<automatic_tuning_option> ::=

Contrôle les options automatiques pour Optimisation automatique. Vous pouvez afficher les options des paramètres suivants sur le Portail Azure ou avec T-SQL dans la vue sys.database_automatic_tuning_options.

AUTOMATIC_TUNING = { AUTO | INHERIT | CUSTOM }

• AUTO

  La définition de la valeur de réglage automatique sur AUTO applique les valeurs par défaut de configuration Azure pour le réglage automatique. Sur le Portail Azure, cela reflète l’option « Hériter de : Valeurs Azure par défaut ».

• INHERIT

  L’utilisation de la valeur INHERIT permet d’hériter de la configuration par défaut du serveur parent. Sur le Portail Azure, cela reflète l’option « Hériter de : Serveur ». Ceci est particulièrement pratique si vous voulez personnaliser la configuration de l’optimisation automatique sur un serveur parent et que toutes les bases de données sur ce serveur HÉRITENT de ces paramètres personnalisés. Pour que l’héritage fonctionne, les trois options de réglage individuelles FORCE_LAST_GOOD_PLAN, CREATE_INDEX et DROP_INDEX doivent être définies sur DEFAULT sur les bases de données.

• CUSTOM

  À l’aide de la valeur CUSTOM, vous devez configurer personnaliséement chacune des options de réglage automatique disponibles sur les bases de données. Sur le Portail Azure, cela reflète l’option « Hériter de : Ne pas hériter ».

CREATE_INDEX = { DEFAULT | ON | OFF }

Active ou désactive l’option CREATE_INDEX de gestion des index automatique de l’optimisation automatique. Vous pouvez afficher l’état de cette option sur le Portail Azure ou avec T-SQL dans la vue sys.database_automatic_tuning_options.

• DEFAULT

  Hérite des paramètres par défaut du serveur. Dans ce cas, les options d’activation ou de désactivation automatique des fonctionnalités du réglage automatique sont définies au niveau du serveur.

• ACTIVÉ

  Si activé, les index manquants sont automatiquement générés sur une base de données. Après la création des index, des gains de performances de la charge de travail se vérifient. Lorsque ces index créés ne fournissent plus d’avantages pour les performances de la charge de travail, ils sont automatiquement annulés. Les index créés automatiquement sont marqués comme index générés par le système.

• OFF

  ne génère pas automatiquement les index manquants sur la base de données.

DROP_INDEX = { DEFAULT | ON | OFF }

Active ou désactive l’option DROP_INDEX de gestion des index automatique de l’optimisation automatique. Vous pouvez afficher l’état de cette option sur le Portail Azure ou avec T-SQL dans la vue sys.database_automatic_tuning_options.

• DEFAULT

  Hérite des paramètres par défaut du serveur. Dans ce cas, les options d’activation ou de désactivation automatique des fonctionnalités du réglage automatique sont définies au niveau du serveur.

• ACTIVÉ

  Supprime automatiquement les index en double ou qui ne sont plus utiles de la charge de travail des performances.

• OFF

  ne supprime pas automatiquement les index manquants sur la base de données.

FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF }

Active ou désactive l’option FORCE_LAST_GOOD_PLAN de correction automatique de plan de l’optimisation automatique. Vous pouvez afficher l’état de cette option sur le Portail Azure ou avec T-SQL dans la vue sys.database_automatic_tuning_options.

• DEFAULT

  Hérite des paramètres par défaut du serveur. Dans ce cas, les options d’activation ou de désactivation automatique des fonctionnalités du réglage automatique sont définies au niveau du serveur. Il s’agit de la valeur par défaut. La valeur par défaut des nouveaux serveurs SQL Azure est ON, ce qui signifie que par défaut, les nouvelles bases de données héritent du paramètre ON.

• ACTIVÉ

  Le moteur de base de données force automatiquement le dernier plan correct connu sur les requêtes Transact-SQL là où le nouveau plan de requête provoque des régressions des performances. Le moteur de base de données supervise en permanence les performances de la requête Transact-SQL avec le plan forcé. S’il existe des gains de performances, le moteur de base de données continue d’utiliser le dernier bon plan connu. Si les gains de performances ne sont pas détectés, le moteur de base de données produit un nouveau plan de requête. L’instruction échoue si le Magasin des requêtes n’est pas activé ou n’est pas en mode en lecture-écriture.

• OFF

  Le Moteur de base de données indique les régressions des performances de requêtes potentielles dues à des changements de plan de requête dans la vue sys.dm_db_tuning_recommendations. Toutefois, ces recommandations ne sont pas appliquées automatiquement. Les utilisateurs peuvent superviser les recommandations actives et résoudre les problèmes identifiés en appliquant les scripts Transact-SQL qui sont montrés dans la vue.

<change_tracking_option> ::=

Contrôle les options de suivi des modifications. Vous pouvez activer le suivi des modifications, définir des options, modifier des options et désactiver le suivi des modifications. Pour obtenir des exemples, consultez la section Exemples plus loin dans cet article.

• ACTIVÉ

  Active le suivi des modifications pour la base de données Lorsque vous activez le suivi des modifications, vous pouvez également définir les options AUTO CLEANUP et CHANGE RETENTION.

  • AUTO_CLEANUP = { ON | OFF }

    • ACTIVÉ

      Les informations de suivi des modifications sont supprimées automatiquement à l'issue de la période de rétention spécifiée.

    • OFF

      Les données de suivi des modifications ne sont pas supprimées de la base de données.

  • CHANGE_RETENTION = période_conservation { DAYS | HOURS | MINUTES }

    Spécifie la période minimale de conservation des informations de suivi des modifications dans la base de données. Les données sont supprimées uniquement lorsque AUTO_CLEANUP a la valeur ON.

    retention_period est un entier qui spécifie la composante numérique de la période de rétention.

    La période de conservation par défaut est 2 jours. La période de rétention minimale est 1 minute. Le type de conservation par défaut est DAYS.

• OFF

  Désactive le suivi des modifications pour la base de données. Désactivez le suivi des modifications sur toutes les tables avant de le désactiver sur la base de données.

<cursor_option> ::=

Contrôle les options de curseur.

CURSOR_CLOSE_ON_COMMIT { ON | OFF }

• ACTIVÉ

  Les curseurs ouverts quand vous validez ou restaurez une transaction sont fermés.

• OFF

  Les curseurs restent ouverts lorsqu’une transaction est validée ; La restauration d’une transaction ferme tous les curseurs, à l’exception des curseurs définis comme INSENSITIVE ou STATIC.

Les paramètres de niveau connexion définis à l'aide de l'instruction SET se substituent au paramètre de base de données par défaut de CURSOR_CLOSE_ON_COMMIT. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui désactive l’option CURSOR_CLOSE_ON_COMMIT pour la session (valeur OFF) par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET CURSOR_CLOSE_ON_COMMIT.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_cursor_close_on_commit_on de la vue de catalogue sys.databases ou la propriété IsCloseCursorsOnCommitEnabled de la fonction DATABASEPROPERTYEX. Le curseur n'est libéré implicitement qu'au moment de la déconnexion. Pour plus d’informations, voir DECLARE CURSOR.

<db_encryption_option> ::=

Contrôle l'état de chiffrement de la base de données.

ENCRYPTION { ON | OFF }

Spécifie si la base de données doit être chiffrée (ON) ou non chiffrée (OFF). Pour plus d’informations sur le chiffrement de base de données, consultez Transparent Data Encryption (TDE) et Transparent Data Encryption pour Azure SQL Database, Azure SQL Managed Instance et Azure Synapse Analytics.

Lorsque le chiffrement est activé au niveau de la base de données, tous les groupes de fichiers sont chiffrés. Les nouveaux groupes de fichiers héritent de la propriété chiffrée. Si des groupes de fichiers de la base de données sont définis sur READ ONLY, l’opération de chiffrement de base de données échoue.

Vous pouvez voir l’état de chiffrement de la base de données en utilisant la vue de gestion dynamique sys.dm_database_encryption_keys.

<db_update_option> ::=

Contrôle si des mises à jour sont autorisées dans la base de données.

• READ_ONLY

  Les utilisateurs peuvent lire des données dans la base de données mais ils n'ont pas le droit de les modifier.

  Notes

  Pour améliorer les performances des requêtes, mettez à jour les statistiques avant de définir une base de données à READ_ONLY. Si des statistiques supplémentaires sont nécessaires une fois qu’une base de données est définie sur READ_ONLY, le moteur de base de données crée des statistiques dans tempdb. Pour plus d’informations sur les statistiques pour une base de données en lecture seule, consultez Statistiques.

• READ_WRITE

  La base de données est accessible aux opérations de lecture et d'écriture.

Pour modifier cet état, vous devez bénéficier d'un accès exclusif à la base de données. Pour plus d'informations, consultez la clause SINGLE_USER.

<db_user_access_option> ::=

Contrôle l'accès utilisateur à la base de données.

• RESTRICTED_USER

  Autorise seulement les membres du rôle de base de données fixe db_owner et des rôles serveur fixes dbcreator et sysadmin à se connecter à la base de données, mais ne limite pas leur nombre. Toutes les connexions à la base de données sont déconnectées dans la plage de temps spécifiée par la clause d'arrêt de l'instruction ALTER DATABASE. Après que la base est passée à l'état RESTRICTED_USER, toute tentative de connexion par des utilisateurs non qualifiés est refusée. Dans Azure SQL Database, doit être exécuté à partir de la base de données utilisateur. À partir de la base de données master, vous pouvez rencontrer un message d’erreur Msg 42008, Level 16, State 3, Line 1 ODBC error: State: 28000: Error: 18456 Message:'[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user '##MS_InstanceCertificate##'.'.

• MULTI_USER

  Tous les utilisateurs qui bénéficient des autorisations appropriées peuvent se connecter à la base de données. Vous pouvez déterminer l’état de cette option en consultant la colonne user_access de la vue de catalogue sys.databases ou la propriété UserAccess de la fonction DATABASEPROPERTYEX. Dans Azure SQL Database, doit être exécuté à partir de la base de données utilisateur. À partir de la base de données master, vous pouvez rencontrer un message d’erreur Msg 42008, Level 16, State 3, Line 1 ODBC error: State: 28000: Error: 18456 Message:'[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user '##MS_InstanceCertificate##'.'.

<delayed_durability_option> ::=

Contrôle si les transactions sont validées de manière entièrement durable ou durable différée.

• DISABLED

  Toutes les transactions suivant SET DISABLED sont entièrement durables. Toutes les options de durabilité définies dans une instruction de validation ou de bloc atomique sont ignorées.

• ALLOWED

  Toutes les transactions suivant SET ALLOWED sont soit entièrement durables, soit retardées durables, en fonction de l’option de durabilité définie dans l’instruction de validation ou de bloc atomique.

• FORCED

  Toutes les transactions suivant SET FORCED sont retardées durables. Toutes les options de durabilité définies dans une instruction de validation ou de bloc atomique sont ignorées.

<PARAMETERIZATION_option> ::=

Contrôle l'option de paramétrage.

PARAMETERIZATION { SIMPLE | FORCED }

• SIMPLE

  Les requêtes sont paramétrables en fonction du comportement par défaut de la base de données.

• FORCED

  SQL Server paramètre toutes les requêtes de la base de données.

La valeur actuelle de cette option peut être déterminée en examinant la colonne is_parameterization_forced dans la vue de catalogue sys.databases.

<query_store_options> ::=

• ON | OFF | CLEAR [ ALL ]

  Contrôle si le Magasin des requêtes est activé dans cette base de données et contrôle la suppression du contenu du Magasin des requêtes.

  • ACTIVÉ

    Active le magasin des requêtes. ON est la valeur par défaut.

  • OFF

    Désactive le magasin des requêtes.

    Notes

    Le Magasin des requêtes ne peut pas être désactivé dans la base de données unique Azure SQL Database et le pool élastique. L’exécution de ALTER DATABASE [database] SET QUERY_STORE = OFF retourne le 'QUERY_STORE=OFF' is not supported in this version of SQL Server.d’avertissement.

  • CLEAR

    Supprime le contenu du magasin des requêtes.

OPERATION_MODE

Décrit le mode de fonctionnement du magasin des requêtes. Les valeurs valides sont READ_ONLY et READ_WRITE. En mode READ_WRITE, le magasin des requêtes collecte et conserve les informations sur le plan de requête et les statistiques d’exécution. En mode READ_ONLY, les informations peuvent être lues à partir du Magasin des requêtes, mais les nouvelles informations ne sont pas ajoutées. Si l’espace alloué maximal du Magasin des requêtes a été épuisé, le Magasin des requêtes change son mode d’opération pour READ_ONLY.

CLEANUP_POLICY

Décrit la stratégie de conservation des données du magasin des requêtes. STALE_QUERY_THRESHOLD_DAYS détermine le nombre de jours pendant lesquels les informations d’une requête sont conservées dans le magasin des requêtes. STALE_QUERY_THRESHOLD_DAYS est de type bigint. La valeur par défaut est 30. Pour l’édition SQL Database De base, la valeur par défaut est 7 jours.

DATA_FLUSH_INTERVAL_SECONDS

Détermine la fréquence à laquelle les données écrites dans le magasin des requêtes sont stockées sur le disque. Pour optimiser les performances, les données collectées par le magasin des requêtes sont écrites de façon asynchrone sur le disque. La fréquence à laquelle ce transfert asynchrone se produit est configurée à l'aide de l'argument DATA_FLUSH_INTERVAL_SECONDS. DATA_FLUSH_INTERVAL_SECONDS est de type bigint. La valeur par défaut est 900 (15 minutes).

MAX_STORAGE_SIZE_MB

Détermine l’espace alloué au magasin des requêtes. MAX_STORAGE_SIZE_MB est de type bigint.

INTERVAL_LENGTH_MINUTES

Détermine l’intervalle de temps auquel les données des statistiques d’exécution du runtime sont agrégées dans le magasin des requêtes. Pour optimiser l'espace, les statistiques d'exécution du runtime du magasin de statistiques du runtime sont agrégées sur une période fixe. Cette fenêtre de temps fixe est configurée à l'aide de l'argument INTERVAL_LENGTH_MINUTES. INTERVAL_LENGTH_MINUTES est de type bigint. La valeur par défaut est 60.

SIZE_BASED_CLEANUP_MODE = { AUTO | OFF }

Contrôle si le nettoyage est automatiquement activé lorsque la quantité totale de données est proche de la taille maximale.

• OFF

  Le nettoyage basé sur la taille n’est pas activé automatiquement.

• AUTO

  Le nettoyage basé sur la taille est automatiquement activé lorsque la taille sur le disque atteint 90% de max_storage_size_mb. Le nettoyage basé sur la taille supprime les requêtes les moins coûteuses et les plus anciennes en premier. Il s’arrête à environ 80 % de max_storage_size_mb. Il s’agit de la valeur de configuration par défaut.

SIZE_BASED_CLEANUP_MODE est de type nvarchar.

QUERY_CAPTURE_MODE { ALL | AUTO | CUSTOM | NONE }

Désigne le mode de capture de requête actif actuellement. Chaque mode définit des stratégies de capture de requête spécifiques.

• ALL

  Capture toutes les requêtes.

• AUTO

  Capturer les requêtes pertinentes en fonction du nombre d’exécutions et de la consommation de ressources. Il s’agit de la valeur de configuration par défaut pour Azure SQL Database.

• Aucune

  Cesser la capture des nouvelles requêtes. Le Magasin des requêtes continue de collecter des statistiques de compilation et d’exécution pour les requêtes qui ont déjà été capturées. Utilisez cette configuration avec précaution, car vous risquez de ne pas capturer des requêtes importantes.

• CUSTOM

  Permet de contrôler les options QUERY_CAPTURE_POLICY.

QUERY_CAPTURE_MODE est de type nvarchar.

max_plans_per_query

Définit le nombre maximal de plans gérés pour chaque requête. MAX_PLANS_PER_QUERY est de type int. La valeur par défaut est 200.

WAIT_STATS_CAPTURE_MODE { ON | OFF }

Contrôle si les statistiques d’attente sont capturées par requête.

• ACTIVÉ

  Les informations des statistiques d’attente par requête sont capturées. Il s’agit de la valeur de configuration par défaut.

• OFF

  Les informations de statistiques d’attente par requête ne sont pas capturées.

<query_capture_policy_option_list> :: =

Contrôle les options de stratégie de capture du magasin des requêtes. Sauf pour STALE_CAPTURE_POLICY_THRESHOLD, ces options définissent dans la valeur de la durée de validité de la stratégie de capture les conditions OR qui doivent intervenir pour que les requêtes soient capturées.

STALE_CAPTURE_POLICY_THRESHOLD = entier { DAYS | HOURS }

Définit la période d’évaluation pour déterminer si une requête doit être capturée. La valeur par défaut est de 1 jour, les valeurs possibles oscillant entre 1 heure et sept jours. number est de type int.

EXECUTION_COUNT = entier

Définit le nombre d’exécutions d’une requête pendant la période d’évaluation. La valeur par défaut est 30, ce qui signifie que pour la durée de validité par défaut de la stratégie de capture, une requête doit s’exécuter au moins 30 fois dans la même journée pour devenir persistante dans le Magasin des requêtes. EXECUTION_COUNT est de type int.

TOTAL_COMPILE_CPU_TIME_MS = entier

Définit le temps UC de compilation écoulé total utilisé par une requête pendant la période d’évaluation. La valeur par défaut est 1000. Ainsi, pour le seuil par défaut de la stratégie de capture obsolète, une requête doit présenter au total au moins une seconde de temps processeur écoulé pendant la compilation de la requête dans une même journée pour devenir persistante dans le Magasin des requêtes. TOTAL_COMPILE_CPU_TIME_MS est de type int.

TOTAL_EXECUTION_CPU_TIME_MS = entier

Définit le temps UC d’exécution écoulé total utilisé par une requête pendant la période d’évaluation. La valeur par défaut est 100 ; ainsi, pour la durée de validité par défaut de la stratégie de capture, une requête doit avoir un total d’au moins 100 ms de temps processeur écoulé pendant l’exécution dans une même journée pour devenir persistante dans le Magasin des requêtes. TOTAL_EXECUTION_CPU_TIME_MS est de type int.

<snapshot_option> ::=

Détermine le niveau d'isolation de la transaction.

ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

• ACTIVÉ

  Active l'option Instantané au niveau de la base de données. Lorsque cette option est activée, les instructions DML commencent à générer des versions de ligne même quand aucune transaction n’utilise l’isolement d’instantané. Une fois que cette option est activée, les transactions peuvent spécifier le niveau d'isolement des transactions SNAPSHOT. Quand une transaction s'exécute au niveau d'isolement SNAPSHOT, toutes les instructions voient un instantané des données tel qu'il existe au début de la transaction. Si une transaction exécutée au niveau d'isolement SNAPSHOT accède à des données dans plusieurs bases de données, l'option ALLOW_SNAPSHOT_ISOLATION doit avoir la valeur ON dans toutes les bases de données ou chaque instruction de la transaction doit utiliser des indicateurs de verrouillage sur toute référence d'une clause FROM à une table de la base de données dont l'option ALLOW_SNAPSHOT_ISOLATION a la valeur OFF.

• OFF

  Désactive l'option Instantané au niveau de la base de données. Les transactions peuvent spécifier le niveau d’isolation de la transaction SNAPSHOT.

Lorsque vous modifiez l'état de ALLOW_SNAPSHOT_ISOLATION (de ON à OFF ou inversement), ALTER DATABASE ne retourne pas le contrôle à l'appelant tant que toutes les transactions existantes dans la base de données n'ont pas été validées. Si la base de données présente déjà l'état spécifié dans l'instruction ALTER DATABASE, le contrôle est immédiatement retourné à l'appelant. Si l’instruction ALTER DATABASE n’est pas retournée rapidement, utilisez sys.dm_tran_active_snapshot_database_transactions pour déterminer si certaines transactions sont de longue durée. Si l'instruction ALTER DATABASE est annulée, la base de données conserve l'état qu'elle présentait au démarrage de ALTER DATABASE. la vue de catalogue sys.databases indique l’état des transactions d’isolement d’instantané dans la base de données. Si snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, l’instruction ALTER DATABASE .... ALLOW_SNAPSHOT_ISOLATION OFF interrompt six secondes et réessaye l’opération.

Vous ne pouvez pas modifier l’état de ALLOW_SNAPSHOT_ISOLATION si la base de données est hors connexion (OFFLINE).

Si vous définissez ALLOW_SNAPSHOT_ISOLATION dans une base de données READ_ONLY, le paramètre est conservé si la base de données est définie ultérieurement sur READ_WRITE.

La valeur actuelle de cette option peut être déterminée en examinant la colonne snapshot_isolation_state dans la vue de catalogue sys.databases.

READ_COMMITTED_SNAPSHOT { ON | OFF }

• ACTIVÉ

  Active l'option d'instantané de lecture validée au niveau de la base de données. Lorsque cette option est activée, les instructions DML commencent à générer des versions de ligne même quand aucune transaction n’utilise l’isolement d’instantané. Une fois que cette option est activée, les transactions qui définissent le niveau d’isolation READ COMMITTED utilisent le versioning de ligne au lieu du verrouillage. Toutes les instructions voient un instantané des données telles qu’elles existent au début de l’instruction quand une transaction est exécutée au niveau d’isolation READ COMMITTED.

• OFF

  Désactive l'option d'instantané de lecture validée au niveau de la base de données. Les transactions spécifiant le niveau d'isolation READ COMMITTED utilisent le verrouillage.

Pour définir READ_COMMITTED_SNAPSHOT sur ON ou sur OFF, il ne doit exister aucune connexion active à la base de données, à l’exception de la connexion exécutant la commande ALTER DATABASE. Toutefois, il n’est pas nécessaire que la base de données soit en mode mono-utilisateur. Vous ne pouvez pas modifier l’état de cette option si la base de données est hors connexion (OFFLINE).

Si vous définissez READ_COMMITTED_SNAPSHOT dans une base de données READ_ONLY, le paramètre est conservé lorsque la base de données est définie ultérieurement sur READ_WRITE.

Il n’est pas possible de définir READ_COMMITTED_SNAPSHOT sur ON pour les bases de données système master, tempdb et msdb. Si le paramètre est modifié pour model, il devient le paramètre par défaut de toutes les bases de données créées à l’exception de tempdb.

La valeur actuelle de cette option peut être déterminée en examinant la colonne is_read_committed_snapshot_on dans la vue de catalogue sys.databases.

MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT { ON | OFF }

• ACTIVÉ

  Quand le niveau d’isolation de la transaction est défini sur un niveau inférieur à SNAPSHOT, toutes les opérations en Transact-SQL interprété sur les tables à mémoire optimisée sont exécutées avec l’isolation SNAPSHOT. Des niveaux d’isolation inférieurs à snapshot sont, par exemple, READ COMMITTED ou READ UNCOMMITTED. Ces opérations sont exécutées si le niveau d’isolation de la transaction est défini explicitement sur le niveau de la session, ou si la valeur par défaut est utilisée implicitement.

• OFF

  N’élève pas le niveau d’isolation pour les opérations en Transact-SQL interprété sur les tables à mémoire optimisée.

Vous ne pouvez pas modifier l’état de MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT si la base de données est hors connexion (OFFLINE).

La valeur par défaut est OFF.

La valeur actuelle de cette option peut être déterminée en examinant la colonne is_memory_optimized_elevate_to_snapshot_on dans la vue de catalogue sys.databases.

<sql_option> ::=

Contrôle les options de conformité ANSI au niveau de la base de données.

ANSI_NULL_DEFAULT { ON | OFF }

Détermine la valeur par défaut, NULL ou NOT NULL, d’une colonne, d’un type CLR défini par l’utilisateur dont le paramètre d’acceptation des valeurs NULL n’est pas défini de façon explicite dans les instructions CREATE TABLE ou ALTER TABLE. Les colonnes définies avec des contraintes suivent les règles de contrainte, quel que soit ce paramètre.

• ACTIVÉ

  La valeur par défaut est NULL.

• OFF

  La valeur par défaut est NOT NULL.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre défini pour ANSI_NULL_DEFAULT au niveau de la base de données par défaut. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_NULL_DEFAULT pour la session par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET ANSI_NULL_DFLT_ON.

Pour garantir la compatibilité ANSI, l'activation (ON) de l'option de base de données ANSI_NULL_DEFAULT entraîne la définition de NULL comme valeur par défaut de la base de données.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_ansi_null_default_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAnsiNullDefault de la fonction DATABASEPROPERTYEX.

ANSI_NULLS { ON | OFF }

• ACTIVÉ

  Toutes les comparaisons avec une valeur Null produisent le résultat UNKNOWN (inconnu).

• OFF

  Les comparaisons de valeurs non Unicode avec une valeur nulle génèrent la valeur TRUE si les deux valeurs sont NULL.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut défini pour ANSI_NULLS au niveau de la base de données. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_NULLS pour la session par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET ANSI_NULLS.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_ansi_nulls_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAnsiNullsEnabled de la fonction DATABASEPROPERTYEX.

ANSI_PADDING { ON | OFF }

• ACTIVÉ

  Les chaînes sont complétées pour avoir la même longueur avant leur conversion. Elles sont également complétées pour avoir la même longueur avant leur insertion dans un type de données varchar ou nvarchar.

• OFF

  Cette option insère des espaces à droite dans les valeurs de type character dans des colonnes varchar ou nvarchar. Cette option laisse également les zéros à droite dans les valeurs de type binary insérées dans des colonnes varbinary. Les valeurs ne sont pas complétées à concurrence de la longueur de la colonne.

  Lorsque cette option a la valeur OFF, elle affecte uniquement la définition des nouvelles colonnes.

Les colonnes char(n) et binary(n) qui acceptent des valeurs NULL sont complétées à concurrence de la longueur de la colonne lorsque ANSI_PADDING a la valeur ON. Les espaces à droite et les zéros sont tronqués lorsque ANSI_PADDING a la valeur OFF. Les colonnes char(n) et binary(n) qui n’acceptent pas les valeurs NULL sont toujours complétées à concurrence de la longueur de la colonne.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre de ANSI_PADDING au niveau de la base de données par défaut. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_PADDING pour la session par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET ANSI_PADDING.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_ansi_padding_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAnsiPaddingEnabled de la fonction DATABASEPROPERTYEX.

ANSI_WARNINGS { ON | OFF }

• ACTIVÉ

  Des erreurs ou avertissements sont émis si des conditions telles que « division par zéro » sont vérifiées. Des erreurs et avertissements sont également générés lorsque des valeurs Null apparaissent dans des fonctions d’agrégation.

• OFF

  Aucun avertissement n'est généré et des valeurs Null sont retournées lorsque des conditions telles qu'une division par zéro se manifestent.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut de ANSI_NULLS défini au niveau de la base de données. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_WARNINGS pour la session par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET ANSI_WARNINGS.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_ansi_warnings_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAnsiWarningsEnabled de la fonction DATABASEPROPERTYEX.

ARITHABORT { ON | OFF }

• ACTIVÉ

  Arrête une requête lorsqu'un dépassement de capacité ou une division par zéro se produit durant son exécution.

• OFF

  Un message d’avertissement s’affiche quand l’une de ces erreurs se produit. Le traitement de la requête, du lot ou de la transaction se poursuit comme s’il n’y avait pas d’erreur, même si un message d’avertissement s’affiche.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_arithabort_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsArithmeticAbortEnabled de la fonction DATABASEPROPERTYEX.

COMPATIBILITY_LEVEL = { 160 | 150 | 140 | 130 | 120 | 110 | 100 }

Pour plus d’informations, consultez niveau de compatibilité ALTER DATABASE.

CONCAT_NULL_YIELDS_NULL { ON | OFF }

• ACTIVÉ

  Le résultat d'une concaténation est NULL lorsque l'un des deux opérandes est NULL. Par exemple, la concaténation de la chaîne de caractères « Ceci est » et NULL donne la valeur NULL et non la valeur « Ceci est ».

• OFF

  La valeur Null est considérée comme une chaîne de caractères vide.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut de CONCAT_NULL_YIELDS_NULL défini au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à CONCAT_NULL_YIELDS_NULL pour la session lors de la connexion à une instance de SQL Server. Pour plus d’informations, voir SET CONCAT_NULL_YIELDS_NULL.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_concat_null_yields_null_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsNullConcat de la fonction DATABASEPROPERTYEX.

NUMERIC_ROUNDABORT { ON | OFF }

• ACTIVÉ

  Une erreur est générée lors d'une perte de précision dans une expression.

• OFF

  La perte de précision ne génère pas de message d’erreur et le résultat est arrondi en fonction de la précision de la colonne ou de la variable stockant le résultat.

Vous pouvez déterminer l’état de cette option dans la colonne is_numeric_roundabort_on de l’affichage catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsNumericRoundAbortEnabled de la fonction DATABASEPROPERTYEX.

QUOTED_IDENTIFIER { ON | OFF }

• ACTIVÉ

  Des guillemets doubles peuvent être utilisés pour entourer des identificateurs délimités.

  Toutes les chaînes délimitées par des guillemets doubles sont considérées comme des identificateurs d'objet. Les identificateurs entre guillemets n’ont pas à respecter les règles Transact-SQL propres aux identificateurs. Ils peuvent être des mots clés et contenir des caractères non autorisés dans les identificateurs Transact-SQL. Si un guillemet double (") fait partie de l’identificateur, il peut être représenté par deux guillemets doubles ("").

• OFF

  Les identificateurs ne peuvent pas être mis entre guillemets et doivent respecter toutes les règles Transact-SQL applicables aux identificateurs. Les chaînes littérales peuvent être délimitées par des guillemets simples ou doubles.

SQL Server permet également de délimiter les identificateurs par des crochets ([ et ]). Les identificateurs entre crochets peuvent toujours être utilisés, quel que soit le paramètre QUOTED_IDENTIFIER. Pour plus d’informations, consultez identificateurs de base de données.

Lors de la création d’une table, l’option QUOTED IDENTIFIER est toujours stockée avec la valeur ON dans les métadonnées de la table. L’option est stockée même si elle a la valeur OFF au moment de la création de la table.

Les paramètres définis au niveau de la connexion à l'aide de l'instruction SET se substituent au paramètre de base de données par défaut de QUOTED_IDENTIFIER. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à QUOTED_IDENTIFIER par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET QUOTED_IDENTIFIER.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_quoted_identifier_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsQuotedIdentifiersEnabled de la fonction DATABASEPROPERTYEX.

RECURSIVE_TRIGGERS { ON | OFF }

• ACTIVÉ

  L'activation récursive des déclencheurs AFTER est autorisée.

• OFF

  Vous pouvez déterminer l’état de cette option en consultant la colonne is_recursive_triggers_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsRecursiveTriggersEnabled de la fonction DATABASEPROPERTYEX.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_recursive_triggers_on de la vue de catalogue sys.databases ou la propriété IsRecursiveTriggersEnabled de la fonction DATABASEPROPERTYEX.

<target_recovery_time_option> ::=

Spécifie la fréquence des points de contrôle indirects en fonction de chaque base de données. À compter de SQL Server 2016 (13.x), la valeur par défaut des nouvelles bases de données est 1 minute, ce qui indique que la base de données utilise des points de contrôle indirects. Pour les versions antérieures, la valeur par défaut est 0, ce qui indique que la base de données utilise des points de contrôle automatiques, dont la fréquence dépend du paramètre d’intervalle de récupération de l’instance de serveur. Microsoft recommande une valeur d’une minute pour la plupart des systèmes.

TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }

• target_recovery_time

  Spécifie la limite maximale de durée de récupération de la base de données spécifiée en cas de sinistre. target_recovery_time est de type int.

• SECONDS

  Indique que target_recovery_time correspond au nombre de secondes.

• MINUTES

  Indique que target_recovery_time correspond au nombre de minutes.

Pour plus d’informations sur les points de contrôle indirects, consultez points de contrôle de base de données.

WITH <termination> ::=

Spécifie le(s) cas où une transaction incomplète doit être restaurée lors d'un changement d'état de la base de données. Lorsque la clause de fin est omise, l’instruction ALTER DATABASE attend indéfiniment s’il existe un verrou quelconque sur la base de données. Une seule clause de fin peut être spécifiée, à la suite des clauses SET.

• ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE

  Indique si la restauration intervient après le nombre de secondes spécifié ou immédiatement.

• NO_WAIT

  Spécifie que la requête échoue si l’état de la base de données ou la modification d’option demandé ne peut pas se terminer immédiatement. Une exécution immédiate signifie ne pas attendre la validation ou la restauration des transactions.

<temporal_history_retention> ::=

• TEMPORAL_HISTORY_RETENTION { ON | OFF }

  Défini par défaut sur ON, mais défini automatiquement sur la valeur OFF après une opération de limite de restauration dans le temps. Pour plus d’informations, notamment sur la façon d’activer ce paramètre, consultez Configuration d’une stratégie de rétention.

  • ACTIVÉ

    Par défaut. Active la stratégie de rétention de table temporelle. Pour plus d’informations, consultez Gérer la conservation des données d’historique dans les tables temporelles versionnées par le système.

  • OFF

    N’effectuez pas de stratégie de rétention historique temporelle.

Options définies

Pour récupérer les paramètres actuels des options de base de données, utilisez la vue de catalogue sys.databases ou DATABASEPROPERTYEX.

Quand vous définissez une option de base de données, la nouvelle valeur prend effet immédiatement.

Vous pouvez modifier les valeurs par défaut de l’une des options de base de données afin qu’elles s’appliquent à toutes les nouvelles bases de données créées. Pour cela, modifiez l’option de base de données appropriée dans la base de données model.

Toutes les options de base de données n’utilisent pas la clause WITH <termination> et ne peuvent pas être combinées avec d’autres options. Le tableau suivant répertorie ces options ainsi que l'état de l'option et d'arrêt.

Catégorie d'options	Peut être spécifiée avec d'autres options	Peut utiliser la clause WITH <termination>
<auto_option>	Oui	Non
<change_tracking_option>	Oui	Oui
<cursor_option>	Oui	Non
<db_encryption_option>	Oui	Non
<db_update_option>	Oui	Oui
<db_user_access_option>	Oui	Oui
<delayed_durability_option>	Oui	Oui
<parameterization_option>	Oui	Oui
ALLOW_SNAPSHOT_ISOLATION	Non	Non
READ_COMMITTED_SNAPSHOT	Non	Oui
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT	Oui	Oui
DATE_CORRELATION_OPTIMIZATION	Oui	Oui
<sql_option>	Oui	Non
<target_recovery_time_option>	Non	Oui

Exemples

R. Définir la base de données en READ_ONLY

La modification de l’état d’une base de données ou d’un groupe de fichiers en READ_ONLY ou READ_WRITE nécessite un accès exclusif à la base de données et peut prendre quelques secondes. L’exemple suivant illustre le basculement de la base de données en mode RESTRICTED_USER pour limiter l’accès. L'exemple affecte ensuite à la base de données AdventureWorks2022 l'état READ_ONLY et rend à tous les utilisateurs l'accès à la base de données.

--Connect to [database_name];
GO
ALTER DATABASE [database_name]
SET RESTRICTED_USER;
GO
ALTER DATABASE [database_name]
SET READ_ONLY
--`SET READ_ONLY` command might take a few seconds to complete.
GO
ALTER DATABASE [database_name]
SET MULTI_USER;
GO

Pour remettre la base de données en mode lecture-écriture :

--Connect to [database_name];
GO
ALTER DATABASE [database_name]
SET READ_WRITE
GO

Pour vérifier :

SELECT [name], user_access_desc, is_read_only FROM sys.databases
WHERE [name] = 'database_name'
GO

B. Activer l’isolement d’instantané sur une base de données

L'exemple ci-dessous illustre l'activation de l'option d'infrastructure d'isolement d'instantané pour la base de données AdventureWorks2022 .

--Connect to [database_name]
GO
ALTER DATABASE [database_name]
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

Vérifiez l’état de la snapshot_isolation_framework base de données.

--Connect to [database_name]
SELECT name, snapshot_isolation_state,
    snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'database_name';
GO

Le jeu de résultats montre que l'infrastructure d'isolement d'instantané est activée.

name	snapshot_isolation_state	description
[nom_base_de_données]	1	ACTIVÉ

C. Activer, modifier ou désactiver le suivi des modifications

L'exemple ci-dessous illustre l'activation du suivi des modifications pour la base de données AdventureWorks2022 et la définition d'une période de rétention de 2 jours.

--Connect to [database_name]
ALTER DATABASE [database_name]
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

L’exemple suivant montre comment changer la période de conservation en 3 jours.

--Connect to [database_name]
ALTER DATABASE [database_name]
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

L'exemple ci-dessous illustre comment désactiver le suivi des modifications pour la base de données AdventureWorks2022 .

--Connect to [database_name]
ALTER DATABASE [database_name]
SET CHANGE_TRACKING = OFF;

D. Activer le magasin des requêtes

L’exemple suivant active le magasin des requêtes et configure ses paramètres.

--Connect to [database_name]
ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60
    );

E. Activer le Magasin des requêtes avec des statistiques d’attente

L’exemple suivant active le magasin des requêtes et configure ses paramètres.

--Connect to [database_name]
ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

F. Activer le Magasin des requêtes avec des options de stratégie de capture personnalisées

L’exemple suivant active le magasin des requêtes et configure ses paramètres.

--Connect to [database_name]
ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Contenu connexe

• Statistiques
• DATABASEPROPERTYEX (Transact-SQL)
• sys.databases
• sys.data_spaces
• sys.database_automatic_tuning_options
• sys.database_automatic_tuning_mode
• niveau de compatibilité ALTER DATABASE
• Mise en miroir de bases de données ALTER DATABASE
• CREATE DATABASE
• activer et désactiver le suivi des modifications (SQL Server)
• DROP DATABASE (Transact-SQL)
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
• sp_configure
• Meilleures pratiques pour la surveillance des charges de travail avec le magasin des requêtes
• Indicateurs du Magasin des requêtes

SQL Server

Base de données SQL

* SQL Managed Instance *  

Azure Synapse
Analytics

Microsoft Fabric

 

Azure SQL Managed Instance

Les niveaux de compatibilité sont SET options, mais sont décrits dans niveau de compatibilité ALTER DATABASE.

Syntaxe

ALTER DATABASE { database_name | Current }
SET
{
    <optionspec> [ ,...n ]
}
;

<optionspec> ::=
{
    <auto_option>
  | <change_tracking_option>
  | <cursor_option>
  | <db_encryption_option>
  | <delayed_durability_option>
  | <parameterization_option>
  | <query_store_options>
  | <snapshot_option>
  | <sql_option>
  | <target_recovery_time_option>
  | <termination>
  | <temporal_history_retention>
}
;
<auto_option> ::=
{
    AUTO_CREATE_STATISTICS { OFF | ON [ ( INCREMENTAL = { ON | OFF } ) ] }
  | AUTO_SHRINK { ON | OFF }
  | AUTO_UPDATE_STATISTICS { ON | OFF }
  | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<automatic_tuning_option> ::=
{
    AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF } )
}

<change_tracking_option> ::=
{
    CHANGE_TRACKING
    {
       = OFF
     | = ON [ ( <change_tracking_option_list > [,...n] ) ]
     | ( <change_tracking_option_list> [,...n] )
    }
}

<change_tracking_option_list> ::=
   {
       AUTO_CLEANUP = { ON | OFF }
     | CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }
   }

<cursor_option> ::=
{
    CURSOR_CLOSE_ON_COMMIT { ON | OFF }
}

<db_encryption_option> ::=
  ENCRYPTION { ON | OFF }

<delayed_durability_option> ::=DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

<parameterization_option> ::=
  PARAMETERIZATION { SIMPLE | FORCED }

<query_store_options> ::=
{
  QUERY_STORE
  {
    = OFF
    | = ON [ ( <query_store_option_list> [,... n] ) ]
    | ( < query_store_option_list> [,... n] )
    | CLEAR [ ALL ]
  }
}

<query_store_option_list> ::=
{
  OPERATION_MODE = { READ_WRITE | READ_ONLY }
  | CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = number )
  | DATA_FLUSH_INTERVAL_SECONDS = number
  | MAX_STORAGE_SIZE_MB = number
  | INTERVAL_LENGTH_MINUTES = number
  | SIZE_BASED_CLEANUP_MODE = { AUTO | OFF }
  | QUERY_CAPTURE_MODE = { ALL | AUTO | CUSTOM | NONE }
  | MAX_PLANS_PER_QUERY = number
  | WAIT_STATS_CAPTURE_MODE = { ON | OFF }
  | QUERY_CAPTURE_POLICY = ( <query_capture_policy_option_list> [,...n] )
}

<query_capture_policy_option_list> :: =
{
    STALE_CAPTURE_POLICY_THRESHOLD = number { DAYS | HOURS }
    | EXECUTION_COUNT = number
    | TOTAL_COMPILE_CPU_TIME_MS = number
    | TOTAL_EXECUTION_CPU_TIME_MS = number
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
  | READ_COMMITTED_SNAPSHOT { ON | OFF }
  | MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT { ON | OFF }
}
<sql_option> ::=
{
    ANSI_NULL_DEFAULT { ON | OFF }
  | ANSI_NULLS { ON | OFF }
  | ANSI_PADDING { ON | OFF }
  | ANSI_WARNINGS { ON | OFF }
  | ARITHABORT { ON | OFF }
  | COMPATIBILITY_LEVEL = { 160 | 150 | 140 | 130 | 120 | 110 | 100 }
  | CONCAT_NULL_YIELDS_NULL { ON | OFF }
  | NUMERIC_ROUNDABORT { ON | OFF }
  | QUOTED_IDENTIFIER { ON | OFF }
  | RECURSIVE_TRIGGERS { ON | OFF }
}

<temporal_history_retention>::= TEMPORAL_HISTORY_RETENTION { ON | OFF }

Arguments

database_name

Nom de la base de données à modifier.

CURRENT

CURRENT exécute l’action dans la base de données active. CURRENT n’est pas pris en charge pour toutes les options dans tous les contextes. Si CURRENT échoue, fournissez le nom de la base de données.

<auto_option> ::=

Contrôle les options automatiques.

AUTO_CREATE_STATISTICS { ON | OFF }

• ACTIVÉ

  L’optimiseur de requête crée si nécessaire des statistiques sur les colonnes uniques des prédicats de requête, afin d’améliorer les plans de requête et les performances des requêtes. Ces statistiques de colonnes uniques sont créées quand l’optimiseur de requête compile les requêtes. Les statistiques de colonnes uniques sont créées uniquement sur les colonnes qui ne constituent pas déjà la première colonne d’un objet de statistiques existant.

  La valeur par défaut est ON. Nous vous recommandons d'utiliser le paramètre par défaut pour la plupart des bases de données.

• OFF

  L’optimiseur de requête ne crée pas de statistiques sur les colonnes uniques des prédicats de requête quand il compile les requêtes. Si cette option a la valeur OFF, il peut en résulter des plans de requête non optimisés et une dégradation des performances des requêtes.

  Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_create_stats_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAutoCreateStatistics de la fonction DATABASEPROPERTYEX.

  Pour plus d’informations, consultez la section « Options des statistiques » dans Statistiques.

INCREMENTAL = ON | OFF

Définissez AUTO_CREATE_STATISTICS sur la valeur ON, et INCREMENTAL sur la valeur ON. Ce paramètre crée automatiquement des statistiques incrémentielles sur les statistiques incrémentielles sont prises en charge. La valeur par défaut est OFF. Pour plus d’informations, voir CREATE STATISTICS.

AUTO_SHRINK { ON | OFF }

• ACTIVÉ

  Les fichiers de base de données peuvent faire l'objet d'une réduction périodique. Sauf si vous avez une exigence spécifique, ne définissez pas l’option de base de données AUTO_SHRINK sur ON. Pour plus d’informations, consultez Réduction d’une base de données.

  Les fichiers de données et les fichiers journaux peuvent être automatiquement réduits. AUTO_SHRINK ne réduit la taille du journal des transactions que si vous définissez la base de données sur le mode de récupération SIMPLE ou si vous sauvegardez le journal. Si la valeur spécifiée est OFF, les fichiers de base de données ne sont pas réduits automatiquement lors des vérifications périodiques de l’espace inutilisé.

  L'option AUTO_SHRINK provoque un compactage dès qu'un fichier comprend plus de 25 % d'espace inutilisé. L’option provoque une réduction du fichier à l’une de deux tailles. Il est réduit à la taille plus grande retenue :

  • La taille où 25 pour cent du fichier correspond à l’espace inutilisé
  • La taille du fichier quand il a été créé

  Vous ne pouvez pas réduire une base de données en lecture seule.

• OFF

  Les fichiers de base de données ne sont pas automatiquement réduits pendant les vérifications périodiques de l’espace inutilisé.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_shrink_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAutoShrink de la fonction DATABASEPROPERTYEX.

AUTO_UPDATE_STATISTICS { ON | OFF }

• ACTIVÉ

  Spécifie que l’optimiseur de requête met à jour les statistiques quand elles sont utilisées par une requête et quand elles sont susceptibles d’être obsolètes. Les statistiques deviennent obsolètes après que des opérations d'insertion, de mise à jour, de suppression ou de fusion ont modifié la distribution des données dans la table ou la vue indexée. L’optimiseur de requête détermine si les statistiques sont obsolètes en comptant le nombre de modifications de données depuis la dernière mise à jour des statistiques et en comparant le nombre de modifications à un seuil. Ce seuil est basé sur le nombre de lignes contenues dans la table ou la vue indexée.

  L’optimiseur de requête vérifie s’il existe des statistiques obsolètes avant de compiler une requête et d’exécuter un plan de requête mis en cache. L’optimiseur de requête utilise les colonnes, les tables et les vues indexées du prédicat de requête pour identifier les statistiques susceptibles d’être obsolètes. L’optimiseur de requête détermine ces informations avant de compiler une requête. Avant d’exécuter un plan de requête mis en cache, le Moteur de base de données vérifie que le plan de requête référence des statistiques à jour.

  L'option AUTO_UPDATE_STATISTICS s'applique aux statistiques créées pour les index, aux colonnes uniques contenues dans les prédicats de requête et aux statistiques créées à l'aide de l'instruction CREATE STATISTICS. Cette option s'applique également aux statistiques filtrées.

  La valeur par défaut est ON. Nous vous recommandons d'utiliser le paramètre par défaut pour la plupart des bases de données.

  Utilisez l'option AUTO_UPDATE_STATISTICS_ASYNC pour spécifier si les statistiques doivent être mises à jour en mode synchrone ou asynchrone.

• OFF

  Spécifie que l’optimiseur de requête ne met pas à jour les statistiques quand elles sont utilisées par une requête. L’optimiseur de requête ne met pas non plus à jour les statistiques quand elles sont susceptibles d’être obsolètes. Si cette option a la valeur OFF, il peut en résulter des plans de requête non optimisés et une dégradation des performances des requêtes.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_update_stats_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAutoUpdateStatistics de la fonction DATABASEPROPERTYEX.

Pour plus d’informations, consultez la section « Utilisation des options de statistiques à l’échelle de la base de données » dans Statistiques.

AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

• ACTIVÉ

  Spécifie que les mises à jour des statistiques pour l'option AUTO_UPDATE_STATISTICS sont asynchrones. L’optimiseur de requête n’attend pas la fin des mises à jour des statistiques pour compiler les requêtes.

  Affecter la valeur ON à cette option n'a aucun effet à moins que AUTO_UPDATE_STATISTICS n'ait également la valeur ON.

  Par défaut, l’option AUTO_UPDATE_STATISTICS_ASYNC est définie sur OFF ; l’optimiseur de requête met à jour les statistiques en mode synchrone.

• OFF

  Spécifie que les mises à jour des statistiques pour l'option AUTO_UPDATE_STATISTICS sont synchrones. L’optimiseur de requête attend la fin des mises à jour des statistiques pour compiler les requêtes.

  Affecter la valeur OFF à cette option n'a aucun effet à moins que AUTO_UPDATE_STATISTICS n'ait également la valeur ON.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_update_stats_async_on de la vue de catalogue sys.databases.

Pour plus d’informations sur l’utilisation des mises à jour de statistiques synchrones ou asynchrones, consultez la section « Utilisation des options de statistiques à l’échelle de la base de données » dans Statistiques.

<automatic_tuning_option> ::=

Contrôle les options automatiques pour Optimisation automatique.

FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF }

Active ou désactive l’option FORCE_LAST_GOOD_PLANOptimisation automatique.

• DEFAULT

  La valeur par défaut pour Azure SQL Managed Instance est ON.

• ACTIVÉ

  Le moteur de base de données force automatiquement le dernier plan correct connu sur les requêtes Transact-SQL là où le nouveau plan de requête provoque des régressions des performances. Le moteur de base de données supervise en permanence les performances de la requête Transact-SQL avec le plan forcé. S’il existe des gains de performances, le moteur de base de données continue d’utiliser le dernier bon plan connu. Si les gains de performances ne sont pas détectés, le moteur de base de données produit un nouveau plan de requête. L’instruction échoue si le Magasin des requêtes n’est pas activé ou n’est pas en mode en lecture-écriture. Il s’agit de la valeur par défaut.

• OFF

  Le Moteur de base de données indique les régressions des performances de requêtes potentielles dues à des changements de plan de requête dans la vue sys.dm_db_tuning_recommendations. Toutefois, ces recommandations ne sont pas appliquées automatiquement. Les utilisateurs peuvent superviser les recommandations actives et résoudre les problèmes identifiés en appliquant les scripts Transact-SQL qui sont montrés dans la vue.

<change_tracking_option> ::=

Contrôle les options de suivi des modifications. Vous pouvez activer le suivi des modifications, définir des options, modifier des options et désactiver le suivi des modifications. Pour obtenir des exemples, consultez la section Exemples plus loin dans cet article.

• ACTIVÉ

  Active le suivi des modifications pour la base de données Lorsque vous activez le suivi des modifications, vous pouvez également définir les options AUTO CLEANUP et CHANGE RETENTION.

AUTO_CLEANUP = { ON | OFF }

• ACTIVÉ

  Les informations de suivi des modifications sont supprimées automatiquement à l'issue de la période de rétention spécifiée.

• OFF

  Les données de suivi des modifications ne sont pas supprimées de la base de données.

CHANGE_RETENTION = période_conservation { DAYS | HOURS | MINUTES }

Spécifie la période minimale de conservation des informations de suivi des modifications dans la base de données. Les données sont supprimées uniquement lorsque AUTO_CLEANUP a la valeur ON.

retention_period est un entier qui spécifie la composante numérique de la période de rétention.

La période de conservation par défaut est 2 jours. La période de rétention minimale est 1 minute. Le type de conservation par défaut est DAYS.

• OFF

  Désactive le suivi des modifications pour la base de données. Désactivez le suivi des modifications sur toutes les tables avant de le désactiver sur la base de données.

<cursor_option> ::=

Contrôle les options de curseur.

CURSOR_CLOSE_ON_COMMIT { ON | OFF }

• ACTIVÉ

  Les curseurs ouverts quand vous validez ou restaurez une transaction sont fermés.

• OFF

  Les curseurs restent ouverts lorsqu'une transaction est validée. La restauration d'une transaction ferme tous les curseurs à l'exception de ceux définis avec la valeur INSENSITIVE ou STATIC.

Les paramètres de niveau connexion définis à l'aide de l'instruction SET se substituent au paramètre de base de données par défaut de CURSOR_CLOSE_ON_COMMIT. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui désactive l’option CURSOR_CLOSE_ON_COMMIT pour la session (valeur OFF) par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET CURSOR_CLOSE_ON_COMMIT.

Vous pouvez déterminer l’état de cette option en examinant la colonne is_cursor_close_on_commit_on de l’affichage catalogue sys.databases ou la propriété IsCloseCursorsOnCommitEnabled de la fonction DATABASEPROPERTYEX. Le curseur n'est libéré implicitement qu'au moment de la déconnexion. Pour plus d’informations, voir DECLARE CURSOR.

<db_encryption_option> ::=

Contrôle l'état de chiffrement de la base de données.

ENCRYPTION { ON | OFF }

Spécifie si la base de données doit être chiffrée (ON) ou non chiffrée (OFF). Pour plus d’informations sur le chiffrement de base de données, consultez Transparent Data Encryption (TDE) et Transparent Data Encryption pour Azure SQL Database, Azure SQL Managed Instance et Azure Synapse Analytics.

Lorsque le chiffrement est activé au niveau de la base de données, tous les groupes de fichiers sont chiffrés. Les nouveaux groupes de fichiers héritent de la propriété chiffrée. Si des groupes de fichiers de la base de données sont définis sur READ ONLY, l’opération de chiffrement de base de données échoue.

Vous pouvez voir l’état de chiffrement de la base de données en utilisant la vue de gestion dynamique sys.dm_database_encryption_keys.

<delayed_durability_option> ::=

Contrôle si les transactions sont validées de manière entièrement durable ou durable différée.

• DISABLED

  Toutes les transactions suivant SET DISABLED sont entièrement durables. Toutes les options de durabilité définies dans une instruction de validation ou de bloc atomique sont ignorées.

• ALLOWED

  Toutes les transactions suivant SET ALLOWED sont soit entièrement durables, soit retardées durables, en fonction de l’option de durabilité définie dans l’instruction de validation ou de bloc atomique.

• FORCED

  Toutes les transactions suivant SET FORCED sont retardées durables. Toutes les options de durabilité définies dans une instruction de validation ou de bloc atomique sont ignorées.

<PARAMETERIZATION_option> ::=

Contrôle l'option de paramétrage.

PARAMETERIZATION { SIMPLE | FORCED }

• SIMPLE

  Les requêtes sont paramétrables en fonction du comportement par défaut de la base de données.

• FORCED

SQL Server paramètre toutes les requêtes de la base de données.

La valeur actuelle de cette option peut être déterminée en examinant la colonne is_parameterization_forced dans la vue de catalogue sys.databases.

<query_store_options> ::=

• ON | OFF | CLEAR [ ALL ]

  Contrôle si le Magasin des requêtes est activé dans cette base de données et contrôle la suppression du contenu du Magasin des requêtes.

  • ACTIVÉ

    Active le magasin des requêtes.

  • OFF

    Désactive le magasin des requêtes. Il s’agit de la valeur par défaut.

  • CLEAR

    Supprime le contenu du magasin des requêtes.

OPERATION_MODE

Décrit le mode de fonctionnement du magasin des requêtes. Les valeurs valides sont READ_ONLY et READ_WRITE. En mode READ_WRITE, le magasin des requêtes collecte et conserve les informations sur le plan de requête et les statistiques d’exécution. En mode READ_ONLY, les informations peuvent être lues à partir du Magasin des requêtes, mais les nouvelles informations ne sont pas ajoutées. Si l’espace alloué maximal du Magasin des requêtes a été épuisé, le Magasin des requêtes change son mode d’opération pour READ_ONLY.

CLEANUP_POLICY

Décrit la stratégie de conservation des données du magasin des requêtes. STALE_QUERY_THRESHOLD_DAYS détermine le nombre de jours pendant lesquels les informations d’une requête sont conservées dans le magasin des requêtes. STALE_QUERY_THRESHOLD_DAYS est de type bigint. La valeur par défaut est 30. Pour l’édition SQL Database De base, la valeur par défaut est 7 jours.

DATA_FLUSH_INTERVAL_SECONDS

Détermine la fréquence à laquelle les données écrites dans le magasin des requêtes sont stockées sur le disque. Pour optimiser les performances, les données collectées par le magasin des requêtes sont écrites de façon asynchrone sur le disque. La fréquence à laquelle ce transfert asynchrone se produit est configurée à l'aide de l'argument DATA_FLUSH_INTERVAL_SECONDS. DATA_FLUSH_INTERVAL_SECONDS est de type bigint. La valeur par défaut est 900 (15 minutes).

MAX_STORAGE_SIZE_MB

Détermine l’espace alloué au magasin des requêtes. MAX_STORAGE_SIZE_MB est de type bigint. La valeur par défaut est 100 Mo.

La limite MAX_STORAGE_SIZE_MB n’est pas strictement appliquée. La taille de stockage est vérifiée seulement quand le Magasin des requêtes écrit des données sur le disque. Cet intervalle est défini par l’option DATA_FLUSH_INTERVAL_SECONDS ou par l’option de la boîte de dialogue Magasin des requêtes de Management Studio Intervalle de vidage des données. La valeur par défaut de l’intervalle est 900 secondes (ou 15 minutes).

Si le Magasin des requêtes a enfreint la limite de MAX_STORAGE_SIZE_MB entre les vérifications de taille de stockage, il passe en mode lecture seule. Si l’option SIZE_BASED_CLEANUP_MODE est activée, le mécanisme de nettoyage permettant d’appliquer la limite MAX_STORAGE_SIZE_MB est également déclenché.

Une fois que suffisamment d’espace a été effacé, le mode Magasin des requêtes bascule automatiquement en lecture-écriture.

INTERVAL_LENGTH_MINUTES

Détermine l’intervalle de temps auquel les données des statistiques d’exécution du runtime sont agrégées dans le magasin des requêtes. Pour optimiser l'espace, les statistiques d'exécution du runtime du magasin de statistiques du runtime sont agrégées sur une période fixe. Cette fenêtre de temps fixe est configurée à l'aide de l'argument INTERVAL_LENGTH_MINUTES. INTERVAL_LENGTH_MINUTES est de type bigint. La valeur par défaut est 60.

SIZE_BASED_CLEANUP_MODE = { AUTO | OFF }

Contrôle si le nettoyage est automatiquement activé lorsque la quantité totale de données est proche de la taille maximale.

• OFF

  Le nettoyage basé sur la taille n’est pas activé automatiquement.

• AUTO

  Le nettoyage basé sur la taille est automatiquement activé lorsque la taille sur le disque atteint 90% de max_storage_size_mb. Le nettoyage basé sur la taille supprime les requêtes les moins coûteuses et les plus anciennes en premier. Il s’arrête à environ 80 % de max_storage_size_mb. Il s’agit de la valeur de configuration par défaut.

SIZE_BASED_CLEANUP_MODE est de type nvarchar.

QUERY_CAPTURE_MODE { ALL | AUTO | CUSTOM | NONE }

Désigne le mode de capture de requête actif actuellement.

• ALL

  Toutes les requêtes sont capturées.

• AUTO

  Capturer les requêtes pertinentes en fonction du nombre d’exécutions et de la consommation de ressources. Il s’agit de la valeur de configuration par défaut pour Azure SQL Database.

• Aucune

  Cesser la capture des nouvelles requêtes. Le Magasin des requêtes continue de collecter des statistiques de compilation et d’exécution pour les requêtes qui ont déjà été capturées. Utilisez cette configuration avec précaution, car vous risquez de ne pas capturer des requêtes importantes.

QUERY_CAPTURE_MODE est de type nvarchar.

max_plans_per_query

Entier représentant le nombre maximal de plans gérés pour chaque requête. MAX_PLANS_PER_QUERY est de type int. La valeur par défaut est 200.

WAIT_STATS_CAPTURE_MODE { ON | OFF }

Contrôle si les statistiques d’attente sont capturées par requête.

• ACTIVÉ

  Les informations des statistiques d’attente par requête sont capturées. Il s’agit de la valeur de configuration par défaut.

• OFF

  Les informations de statistiques d’attente par requête ne sont pas capturées.

<query_capture_policy_option_list> :: =

Contrôle les options de stratégie de capture du magasin des requêtes. Sauf pour STALE_CAPTURE_POLICY_THRESHOLD, ces options définissent dans la valeur de la durée de validité de la stratégie de capture les conditions OR qui doivent intervenir pour que les requêtes soient capturées.

STALE_CAPTURE_POLICY_THRESHOLD = entier { DAYS | HOURS }

Définit la période d’évaluation pour déterminer si une requête doit être capturée. La valeur par défaut est de 1 jour, les valeurs possibles oscillant entre 1 heure et sept jours.

EXECUTION_COUNT = entier

Définit le nombre d’exécutions d’une requête pendant la période d’évaluation. La valeur par défaut est 30, ce qui signifie que pour la durée de validité par défaut de la stratégie de capture, une requête doit s’exécuter au moins 30 fois dans la même journée pour devenir persistante dans le Magasin des requêtes. EXECUTION_COUNT est de type int.

TOTAL_COMPILE_CPU_TIME_MS = entier

Définit le temps UC de compilation écoulé total utilisé par une requête pendant la période d’évaluation. La valeur par défaut est 1000. Ainsi, pour le seuil par défaut de la stratégie de capture obsolète, une requête doit présenter au total au moins une seconde de temps processeur écoulé pendant la compilation de la requête dans une même journée pour devenir persistante dans le Magasin des requêtes. TOTAL_COMPILE_CPU_TIME_MS est de type int.

TOTAL_EXECUTION_CPU_TIME_MS = entier

Définit le temps UC d’exécution écoulé total utilisé par une requête pendant la période d’évaluation. La valeur par défaut est 100. Ainsi, pour le seuil par défaut de la stratégie de capture obsolète, une requête doit présenter au total au moins 100 ms de temps processeur écoulé pendant l’exécution dans une même journée pour devenir persistante dans le Magasin des requêtes. TOTAL_EXECUTION_CPU_TIME_MS est de type int.

<snapshot_option> ::=

Détermine le niveau d'isolation de la transaction.

ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

• ACTIVÉ

  Active l’option Instantané au niveau de la base de données. Lorsque cette option est activée, les instructions DML commencent à générer des versions de ligne même quand aucune transaction n’utilise l’isolement d’instantané. Une fois que cette option est activée, les transactions peuvent spécifier le niveau d’isolement des transactions SNAPSHOT. Quand une transaction s'exécute au niveau d'isolement SNAPSHOT, toutes les instructions voient un instantané des données tel qu'il existe au début de la transaction. Si une transaction exécutée au niveau d'isolement SNAPSHOT accède à des données dans plusieurs bases de données, l'option ALLOW_SNAPSHOT_ISOLATION doit avoir la valeur ON dans toutes les bases de données ou chaque instruction de la transaction doit utiliser des indicateurs de verrouillage sur toute référence d'une clause FROM à une table de la base de données dont l'option ALLOW_SNAPSHOT_ISOLATION a la valeur OFF.

• OFF

  Désactive l'option Instantané au niveau de la base de données. Les transactions peuvent spécifier le niveau d’isolation de la transaction SNAPSHOT.

Lorsque vous modifiez l'état de ALLOW_SNAPSHOT_ISOLATION (de ON à OFF ou inversement), ALTER DATABASE ne retourne pas le contrôle à l'appelant tant que toutes les transactions existantes dans la base de données n'ont pas été validées. Si la base de données présente déjà l'état spécifié dans l'instruction ALTER DATABASE, le contrôle est immédiatement retourné à l'appelant. Si l’instruction ALTER DATABASE n’est pas retournée rapidement, utilisez sys.dm_tran_active_snapshot_database_transactions pour déterminer si certaines transactions sont de longue durée. Si l'instruction ALTER DATABASE est annulée, la base de données conserve l'état qu'elle présentait au démarrage de ALTER DATABASE. la vue de catalogue sys.databases indique l’état des transactions d’isolement d’instantané dans la base de données. Si snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, l’instruction ALTER DATABASE ... ALLOW_SNAPSHOT_ISOLATION OFF interrompt six secondes et réessaye l’opération.

Vous ne pouvez pas modifier l’état de ALLOW_SNAPSHOT_ISOLATION si la base de données est hors connexion (OFFLINE).

Vous pouvez modifier les paramètres ALLOW_SNAPSHOT_ISOLATION pour les bases de données master, model, msdb et tempdb. Le paramètre est conservé à chaque arrêt et redémarrage de l’instance du Moteur de base de données si vous changez le paramètre pour tempdb. Si le paramètre est modifié pour la base de données système model, il devient le paramètre par défaut de toutes les bases de données créées à l’exception de tempdb.

Cette option a la valeur ON par défaut pour les bases de données master et msdb.

La valeur actuelle de cette option peut être déterminée en examinant la colonne snapshot_isolation_state dans la vue de catalogue sys.databases.

READ_COMMITTED_SNAPSHOT { ON | OFF }

• ACTIVÉ

  Active l’option Instantané Read-Committed au niveau de la base de données. Lorsque cette option est activée, les instructions DML commencent à générer des versions de ligne même quand aucune transaction n’utilise l’isolement d’instantané. Une fois que cette option est activée, les transactions spécifiant le niveau d’isolement READ COMMITTED utilisent le versioning de ligne au lieu du verrouillage. Toutes les instructions voient un instantané des données telles qu’elles existent au début de l’instruction quand une transaction est exécutée au niveau d’isolation READ COMMITTED.

• OFF

  Désactive l’option Instantané Read-Committed au niveau de la base de données. Les transactions spécifiant le niveau d'isolation READ COMMITTED utilisent le verrouillage.

Pour définir READ_COMMITTED_SNAPSHOT sur ON ou sur OFF, il ne doit exister aucune connexion active à la base de données, à l’exception de la connexion exécutant la commande ALTER DATABASE. Toutefois, il n’est pas nécessaire que la base de données soit en mode mono-utilisateur. Vous ne pouvez pas modifier l’état de cette option si la base de données est hors connexion (OFFLINE).

Il n’est pas possible de définir READ_COMMITTED_SNAPSHOT sur ON pour les bases de données système master, tempdb et msdb. Si le paramètre est modifié pour la base de données système model, il devient le paramètre par défaut de toutes les bases de données créées à l’exception de tempdb.

La valeur actuelle de cette option peut être déterminée en examinant la colonne is_read_committed_snapshot_on dans la vue de catalogue sys.databases.

MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT { ON | OFF }

• ACTIVÉ

  Quand le niveau d’isolation de la transaction est défini sur un niveau inférieur à SNAPSHOT, toutes les opérations en Transact-SQL interprété sur les tables à mémoire optimisée sont exécutées avec l’isolation SNAPSHOT. Des niveaux d’isolation inférieurs à snapshot sont, par exemple, READ COMMITTED ou READ UNCOMMITTED. Ces opérations sont exécutées si le niveau d’isolation de la transaction est défini explicitement sur le niveau de la session, ou si la valeur par défaut est utilisée implicitement.

• OFF

  N’élève pas le niveau d’isolation pour les opérations en Transact-SQL interprété sur les tables à mémoire optimisée.

Vous ne pouvez pas modifier l’état de MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT si la base de données est hors connexion (OFFLINE).

La valeur par défaut est OFF.

La valeur actuelle de cette option peut être déterminée en examinant la colonne is_memory_optimized_elevate_to_snapshot_on dans la vue de catalogue sys.databases.

<sql_option> ::=

Contrôle les options de conformité ANSI au niveau de la base de données.

ANSI_NULL_DEFAULT { ON | OFF }

Détermine la valeur par défaut, NULL ou NOT NULL, d’une colonne, d’un type CLR défini par l’utilisateur dont le paramètre d’acceptation des valeurs NULL n’est pas défini de façon explicite dans les instructions CREATE TABLE ou ALTER TABLE. Les colonnes définies avec des contraintes suivent les règles de contrainte, quel que soit ce paramètre.

• ACTIVÉ

  La valeur par défaut est NULL.

• OFF

  La valeur par défaut est NOT NULL.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre défini pour ANSI_NULL_DEFAULT au niveau de la base de données par défaut. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_NULL_DEFAULT pour la session par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET ANSI_NULL_DFLT_ON.

Pour garantir la compatibilité ANSI, l'activation (ON) de l'option de base de données ANSI_NULL_DEFAULT entraîne la définition de NULL comme valeur par défaut de la base de données.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_ansi_null_default_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAnsiNullDefault de la fonction DATABASEPROPERTYEX.

ANSI_NULLS { ON | OFF }

• ACTIVÉ

  Toutes les comparaisons avec une valeur Null produisent le résultat UNKNOWN (inconnu).

• OFF

  Les comparaisons de valeurs non Unicode avec une valeur nulle génèrent la valeur TRUE si les deux valeurs sont NULL.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut défini pour ANSI_NULLS au niveau de la base de données. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_NULLS pour la session par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET ANSI_NULLS.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_ansi_nulls_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAnsiNullsEnabled de la fonction DATABASEPROPERTYEX.

ANSI_PADDING { ON | OFF }

• ACTIVÉ

  Les chaînes sont complétées pour avoir la même longueur avant leur conversion. Elles sont également complétées pour avoir la même longueur avant leur insertion dans un type de données varchar ou nvarchar.

• OFF

  Cette option insère des espaces à droite dans les valeurs de type character dans des colonnes varchar ou nvarchar. Cette option laisse également les zéros à droite dans les valeurs de type binary insérées dans des colonnes varbinary. Les valeurs ne sont pas complétées à concurrence de la longueur de la colonne.

  Lorsque cette option a la valeur OFF, elle affecte uniquement la définition des nouvelles colonnes.

Les colonnes char(n) et binary(n) qui acceptent des valeurs NULL sont complétées à concurrence de la longueur de la colonne lorsque ANSI_PADDING a la valeur ON. Les espaces à droite et les zéros sont tronqués lorsque ANSI_PADDING a la valeur OFF. Les colonnes char(n) et binary(n) qui n’acceptent pas les valeurs NULL sont toujours complétées à concurrence de la longueur de la colonne.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre de ANSI_PADDING au niveau de la base de données par défaut. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_PADDING pour la session par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET ANSI_PADDING.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_ansi_padding_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAnsiPaddingEnabled de la fonction DATABASEPROPERTYEX.

ANSI_WARNINGS { ON | OFF }

• ACTIVÉ

  Des erreurs ou avertissements sont émis si des conditions telles que « division par zéro » sont vérifiées. Des erreurs et avertissements sont également générés lorsque des valeurs Null apparaissent dans des fonctions d’agrégation.

• OFF

  Aucun avertissement n'est généré et des valeurs Null sont retournées lorsque des conditions telles qu'une division par zéro se manifestent.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut de ANSI_NULLS défini au niveau de la base de données. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_WARNINGS pour la session par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET ANSI_WARNINGS.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_ansi_warnings_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAnsiWarningsEnabled de la fonction DATABASEPROPERTYEX.

ARITHABORT { ON | OFF }

• ACTIVÉ

  Arrête une requête lorsqu'un dépassement de capacité ou une division par zéro se produit durant son exécution.

• OFF

  Un message d’avertissement s’affiche quand l’une de ces erreurs se produit. Le traitement de la requête, du lot ou de la transaction se poursuit comme s’il n’y avait pas d’erreur, même si un message d’avertissement s’affiche.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_arithabort_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsArithmeticAbortEnabled de la fonction DATABASEPROPERTYEX.

COMPATIBILITY_LEVEL = { 160 | 150 | 140 | 130 | 120 | 110 | 100 }

Pour plus d’informations, consultez niveau de compatibilité ALTER DATABASE.

CONCAT_NULL_YIELDS_NULL { ON | OFF }

• ACTIVÉ

  Le résultat d'une concaténation est NULL lorsque l'un des deux opérandes est NULL. Par exemple, la concaténation de la chaîne de caractères « Ceci est » et NULL donne la valeur NULL et non la valeur « Ceci est ».

• OFF

  La valeur Null est considérée comme une chaîne de caractères vide.

Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut de CONCAT_NULL_YIELDS_NULL défini au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à CONCAT_NULL_YIELDS_NULL pour la session lors de la connexion à une instance de SQL Server. Pour plus d’informations, voir SET CONCAT_NULL_YIELDS_NULL.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_concat_null_yields_null_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsNullConcat de la fonction DATABASEPROPERTYEX.

NUMERIC_ROUNDABORT { ON | OFF }

• ACTIVÉ

  Une erreur est générée lors d'une perte de précision dans une expression.

• OFF

  La perte de précision ne génère pas de message d’erreur et le résultat est arrondi en fonction de la précision de la colonne ou de la variable stockant le résultat.

Vous pouvez déterminer l’état de cette option dans la colonne is_numeric_roundabort_on de l’affichage catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsNumericRoundAbortEnabled de la fonction DATABASEPROPERTYEX.

QUOTED_IDENTIFIER { ON | OFF }

• ACTIVÉ

  Des guillemets doubles peuvent être utilisés pour entourer des identificateurs délimités.

  Toutes les chaînes délimitées par des guillemets doubles sont considérées comme des identificateurs d'objet. Les identificateurs entre guillemets n’ont pas à respecter les règles Transact-SQL propres aux identificateurs. Ils peuvent être des mots clés et contenir des caractères non autorisés dans les identificateurs Transact-SQL. Si un guillemet double (") fait partie de l’identificateur, il peut être représenté par deux guillemets doubles ("").

• OFF

  Les identificateurs ne peuvent pas être mis entre guillemets et doivent respecter toutes les règles Transact-SQL applicables aux identificateurs. Les chaînes littérales peuvent être délimitées par des guillemets simples ou doubles.

SQL Server permet également de délimiter les identificateurs par des crochets ([ et ]). Les identificateurs entre crochets peuvent toujours être utilisés, quel que soit le paramètre QUOTED_IDENTIFIER. Pour plus d’informations, consultez identificateurs de base de données.

Lors de la création d’une table, l’option QUOTED IDENTIFIER est toujours stockée avec la valeur ON dans les métadonnées de la table. L’option est stockée même si elle a la valeur OFF au moment de la création de la table.

Les paramètres définis au niveau de la connexion à l'aide de l'instruction SET se substituent au paramètre de base de données par défaut de QUOTED_IDENTIFIER. Les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à QUOTED_IDENTIFIER par défaut. Les clients exécutent l’instruction lorsque vous vous connectez à une instance de SQL Server. Pour plus d’informations, voir SET QUOTED_IDENTIFIER.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_quoted_identifier_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsQuotedIdentifiersEnabled de la fonction DATABASEPROPERTYEX.

RECURSIVE_TRIGGERS { ON | OFF }

• ACTIVÉ

  L'activation récursive des déclencheurs AFTER est autorisée.

• OFF

  Vous pouvez déterminer l’état de cette option en consultant la colonne is_recursive_triggers_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsRecursiveTriggersEnabled de la fonction DATABASEPROPERTYEX.

  Notes

  Seule la récursivité directe est désactivée lorsque RECURSIVE_TRIGGERS a la valeur OFF. Pour désactiver la récursivité indirecte, vous devez aussi affecter la valeur 0 à l'option serveur nested triggers.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_recursive_triggers_on de la vue de catalogue sys.databases ou la propriété IsRecursiveTriggersEnabled de la fonction DATABASEPROPERTYEX.

<target_recovery_time_option> ::=

target_recovery_time_option n’est pas pris en charge sur Azure SQL Managed Instance.

Spécifie la fréquence des points de contrôle indirects en fonction de chaque base de données. À compter de SQL Server 2016 (13.x), la valeur par défaut des nouvelles bases de données est 1 minute, ce qui indique que la base de données utilise des points de contrôle indirects. Pour les versions antérieures, la valeur par défaut est 0, ce qui indique que la base de données utilise des points de contrôle automatiques, dont la fréquence dépend du paramètre d’intervalle de récupération de l’instance de serveur. Microsoft recommande une valeur d’une minute pour la plupart des systèmes.

WITH <termination> ::=

Spécifie le(s) cas où une transaction incomplète doit être restaurée lors d'un changement d'état de la base de données. Lorsque la clause de fin est omise, l’instruction ALTER DATABASE attend indéfiniment s’il existe un verrou quelconque sur la base de données. Une seule clause de fin peut être spécifiée, à la suite des clauses SET.

• ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE

  Indique si la restauration intervient après le nombre de secondes spécifié ou immédiatement.

• NO_WAIT

  Spécifie que la requête échoue si l’état de la base de données ou la modification d’option demandé ne peut pas se terminer immédiatement. Une exécution immédiate signifie ne pas attendre la validation ou la restauration des transactions.

<temporal_history_retention> ::=

• TEMPORAL_HISTORY_RETENTION { ON | OFF }

  Défini par défaut sur ON, mais défini automatiquement sur la valeur OFF après une opération de limite de restauration dans le temps. Pour plus d’informations, notamment sur la façon d’activer ce paramètre, consultez Configuration d’une stratégie de rétention.

  • ACTIVÉ

    Par défaut. Active la stratégie de rétention de table temporelle. Pour plus d’informations, consultez Gérer la conservation des données d’historique dans les tables temporelles versionnées par le système.

  • OFF

    N’effectuez pas de stratégie de rétention historique temporelle.

Options définies

Pour récupérer les paramètres actuels des options de base de données, utilisez la vue de catalogue sys.databases ou DATABASEPROPERTYEX.

Quand vous définissez une option de base de données, la nouvelle valeur prend effet immédiatement.

Vous pouvez modifier les valeurs par défaut de l’une des options de base de données afin qu’elles s’appliquent à toutes les nouvelles bases de données créées. Pour cela, modifiez l’option de base de données appropriée dans la base de données système model.

Exemples

R. Activer l’isolement d’instantané sur une base de données

L'exemple ci-dessous illustre l'activation de l'option d'infrastructure d'isolement d'instantané pour la base de données AdventureWorks2022 .

USE master;
GO
ALTER DATABASE [database_name]
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
    snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'[database_name]';
GO

Le jeu de résultats montre que l'infrastructure d'isolement d'instantané est activée.

name	snapshot_isolation_state	description
[nom_base_de_données]	1	ACTIVÉ

B. Activer, modifier ou désactiver le suivi des modifications

L'exemple ci-dessous illustre l'activation du suivi des modifications pour la base de données AdventureWorks2022 et la définition d'une période de rétention de 2 jours.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

L'exemple suivant illustre comment modifier la période de rétention en spécifiant 3 jours.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

L'exemple ci-dessous illustre comment désactiver le suivi des modifications pour la base de données AdventureWorks2022 .

ALTER DATABASE [database_name]
SET CHANGE_TRACKING = OFF;

C. Activer le magasin des requêtes

L’exemple suivant active le magasin des requêtes et configure ses paramètres.

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60
    );

D. Activer le Magasin des requêtes avec des statistiques d’attente

L’exemple suivant active le magasin des requêtes et configure ses paramètres.

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

E. Activer le Magasin des requêtes avec des options de stratégie de capture personnalisées

L’exemple suivant active le magasin des requêtes et configure ses paramètres.

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Contenu connexe

• Statistiques
• DATABASEPROPERTYEX (Transact-SQL)
• sys.databases
• sys.data_spaces
• sys.database_automatic_tuning_options
• sys.database_automatic_tuning_mode
• niveau de compatibilité ALTER DATABASE
• Mise en miroir de bases de données ALTER DATABASE
• CREATE DATABASE
• activer et désactiver le suivi des modifications (SQL Server)
• DROP DATABASE (Transact-SQL)
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
• sp_configure
• Meilleures pratiques pour la surveillance des charges de travail avec le magasin des requêtes

SQL Server

Base de données SQL

SQL Managed Instance

* Azure Synapse
Analytics *  

Microsoft Fabric

 

Azure Synapse Analytics

Syntaxe

ALTER DATABASE { database_name }
SET
{
    <optionspec> [ ,...n ]
}
;

<option_spec>::=
{
    <auto_option>
  | <db_encryption_option>
  | <query_store_options>
  | <result_set_caching>
  | <snapshot_option>
}
;

<auto_option> ::=
{
    AUTO_CREATE_STATISTICS { OFF | ON }
}

<db_encryption_option> ::=
{
    ENCRYPTION { ON | OFF }
}

<query_store_option> ::=
{
    QUERY_STORE { OFF | ON }
}

<result_set_caching_option> ::=
{
    RESULT_SET_CACHING { ON | OFF }
}

<snapshot_option> ::=
{
    READ_COMMITTED_SNAPSHOT { ON | OFF }
}

Arguments

database_name

Nom de la base de données à modifier.

<auto_option> ::=

Contrôle les options automatiques.

AUTO_CREATE_STATISTICS { ON | OFF }

• ACTIVÉ

  L’optimiseur de requête crée si nécessaire des statistiques sur les colonnes uniques des prédicats de requête, afin d’améliorer les plans de requête et les performances des requêtes. Ces statistiques de colonnes uniques sont créées quand l’optimiseur de requête compile les requêtes. Les statistiques de colonnes uniques sont créées uniquement sur les colonnes qui ne constituent pas déjà la première colonne d’un objet de statistiques existant.

  La valeur par défaut est ON. Nous vous recommandons d'utiliser le paramètre par défaut pour la plupart des bases de données.

• OFF

  L’optimiseur de requête ne crée pas de statistiques sur les colonnes uniques des prédicats de requête quand il compile les requêtes. Si cette option a la valeur OFF, il peut en résulter des plans de requête non optimisés et une dégradation des performances des requêtes.

Cette commande doit être exécutée lorsque vous êtes connecté à la base de données utilisateur.

Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_create_stats_on de la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriété IsAutoCreateStatistics de la fonction DATABASEPROPERTYEX.

Pour plus d’informations, consultez la section « Utilisation des options de statistiques à l’échelle de la base de données » dans Statistiques.

<db_encryption_option> ::=

Contrôle l'état de chiffrement de la base de données.

ENCRYPTION { ON | OFF }

• ACTIVÉ

  Indique que la base de données doit être chiffrée.

• OFF

  Indique que la base de données ne doit pas être chiffrée.

Pour plus d’informations sur le chiffrement de base de données, consultez Transparent Data Encryption (TDE) et Transparent Data Encryption pour Azure SQL Database, Azure SQL Managed Instance et Azure Synapse Analytics.

Lorsque le chiffrement est activé au niveau de la base de données, tous les groupes de fichiers sont chiffrés. Les nouveaux groupes de fichiers héritent de la propriété chiffrée. Si des groupes de fichiers de la base de données sont définis sur READ ONLY, l’opération de chiffrement de base de données échoue.

Vous pouvez visualiser l’état de chiffrement de la base de données et l’état de l’analyse du chiffrement dans la vue de gestion dynamique sys.dm_database_encryption_keys.

<query_store_option> ::=

Contrôle si le Magasin des requêtes est activé dans cet entrepôt de données.

QUERY_STORE { ON | OFF }

• ACTIVÉ

  Active le magasin des requêtes.

• OFF

  Désactive le magasin des requêtes. OFF est la valeur par défaut.

<result_set_caching_option> ::=

S’applique à : Azure Synapse Analytics

Contrôle si le résultat de la requête est mis en cache dans la base de données.

RESULT_SET_CACHING { ON | OFF }

• ACTIVÉ

  Spécifie que les jeux de résultats de requête retournés à partir de cette base de données sont mis en cache dans la base de données.

• OFF

  Spécifie que les jeux de résultats de requête retournés à partir de cette base de données ne sont pas mis en cache dans la base de données.

Cette commande doit être exécutée quand vous êtes connecté à la base de données master. La modification de ce paramètre de base de données prend effet immédiatement. Des coûts de stockage sont facturés en mettant en cache des jeux de résultats de requête. Après avoir désactivé la mise en cache des résultats pour une base de données, le cache de résultats précédemment persistant est immédiatement supprimé du stockage Azure Synapse.

Exécutez cette commande pour vérifier la configuration de la mise en cache de l’ensemble des résultats d’une base de données. Si la mise en cache du jeu de résultats est activée, is_result_set_caching_on retourne 1.

SELECT name, is_result_set_caching_on FROM sys.databases
WHERE name = <'Your_Database_Name'>

Exécutez cette commande pour vérifier si une requête a été exécutée avec un résultat mis en cache. La colonne result_cache_hit retourne 1 pour l’accès au cache, 0 pour les erreurs de cache et les valeurs négatives pour des raisons pour lesquelles la mise en cache du jeu de résultats n’a pas été utilisée. Pour plus d’informations, consultez sys.dm_pdw_exec_requests.

SELECT request_id, command, result_cache_hit FROM sys.dm_pdw_exec_requests
WHERE request_id = <'Your_Query_Request_ID'>

Pour plus d’informations sur le réglage des performances avec la mise en cache des jeux de résultats, consultez Conseils de réglage des performances.

Autorisations

Pour définir l’option RESULT_SET_CACHING, un utilisateur a besoin d’une connexion du principal au niveau du serveur (celle créée par le processus de provisionnement) ou doit être membre du rôle de base de données dbmanager.

<snapshot_option> ::=

S’applique à : Azure Synapse Analytics

Contrôle le niveau d’isolation des transactions d’une base de données.

READ_COMMITTED_SNAPSHOT { ON | OFF }

• ACTIVÉ

  Active l’option READ_COMMITTED_SNAPSHOT au niveau de la base de données.

• OFF

  Désactive l’option READ_COMMITTED_SNAPSHOT au niveau de la base de données.

Cette commande doit être exécutée quand vous êtes connecté à la base de données master. Activer ou désactiver READ_COMMITTED_SNAPSHOT pour une base de données utilisateur tue toutes les connexions ouvertes à cette base de données. Vous devez effectuer cette modification pendant une fenêtre de maintenance de base de données ou attendre qu’il n’y ait pas de connexion active à la base de données, à l’exception de la connexion exécutant la commande ALTER DATABASE. Il n’est pas nécessaire que la base de données soit en mode mono-utilisateur. La modification du paramètre READ_COMMITTED_SNAPSHOT au niveau de la session n’est pas prise en charge. Pour vérifier ce paramètre dans une base de données, examinez la colonne is_read_committed_snapshot_on dans sys.databases.

Dans une base de données avec READ_COMMITTED_SNAPSHOT activé, les requêtes peuvent rencontrer des performances plus lentes en raison de l’analyse des versions si plusieurs versions de données sont présentes. Les transactions longues peuvent également entraîner une augmentation de la taille de la base de données. Ce problème se produit s’il existe des modifications de données effectuées par ces transactions qui bloquent le nettoyage de la version.

Autorisations

Pour définir l’option READ_COMMITTED_SNAPSHOT, un utilisateur doit disposer de l’autorisation ALTER sur la base de données.

Exemples

Vérifier le paramètre des statistiques pour une base de données

SELECT name, is_auto_create_stats_on FROM sys.databases

Activer Magasin des requêtes pour une base de données

ALTER DATABASE [database_name]
SET QUERY_STORE = ON;

Activer la mise en cache d’un jeu de résultats pour une base de données

-- Run this command when connecting to the MASTER database

ALTER DATABASE [database_name]
SET RESULT_SET_CACHING ON;

Vérifier le paramètre de mise en cache d’un jeu de résultats pour une base de données

SELECT name, is_result_set_caching_on
FROM sys.databases;

Activer l’option Read_Committed_Snapshot pour une base de données

Exécutez cette commande lors de la connexion à la base de données master.

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON;

Contenu connexe

• DATABASEPROPERTYEX (Transact-SQL)
• sys.databases
• Éléments du langage d’Azure Synapse Analytics
• DROP DATABASE (Transact-SQL)
• Meilleures pratiques pour Azure Synapse Analytics
• Concevoir de tables dans Azure Synapse Analytics

SQL Server

Base de données SQL

SQL Managed Instance

Azure Synapse
Analytics

Microsoft Fabric

 

Microsoft Fabric

Permet ALTER DATABASE ... SET de gérer un entrepôt Microsoft Fabric.

Syntaxe

-- Microsoft Fabric

ALTER DATABASE { warehouse_name | CURRENT }
SET
{
    <option_spec> [ ,...n ] 
}

<option_spec> ::=
{
    <data_lake_log_publishing>
  | <vorder>
}
;

<data_lake_log_publishing> ::=
{
    DATA_LAKE_LOG_PUBLISHING { PAUSED | AUTO }
}

<vorder> ::=
{
    VORDER = OFF
}

Notes

Actuellement, la suspension de la publication des journaux Delta Lake et la désactivation du comportement de commande V-Order dans un entrepôt sont les seules utilisations pour ALTER DATABASE ... SET Microsoft Fabric.

Autorisations

L’utilisateur doit être membre des rôles Administrateur, Membre ou Contributeur dans l’espace de travail Fabric.

Exemples

R. Suspension de la publication de Delta Lake Logs

La commande T-SQL suivante interrompt la publication Delta Lake Log dans le contexte actuel de l’entrepôt.

ALTER DATABASE CURRENT SET DATA_LAKE_LOG_PUBLISHING = PAUSED;

Pour vérifier l’état actuel de la publication Delta Lake Log sur tous les entrepôts, de votre espace de travail, utilisez le code T-SQL suivant pour interroger sys.databases dans une nouvelle fenêtre de requête :

SELECT [name], [DATA_LAKE_LOG_PUBLISHING_DESC] FROM sys.databases;

Contenu connexe

• Qu’est-ce que l’entreposage de données dans Microsoft Fabric ?
• journaux Delta Lake dans Warehouse dans Microsoft Fabric
• Comprendre la commande V pour Microsoft Fabric Warehouse