Options SET d’ALTER DATABASE (Transact-SQL)
Définit les options de base de données dans Microsoft SQL Server, Azure SQL Database et Azure Synapse Analytics. Pour connaître les autres options d’ALTER DATABASE, voir ALTER DATABASE.
Notes
La définition de certaines options avec ALTER DATABASE peut demander un accès exclusif à la base de données. Si l’instruction ALTER DATABASE ne se termine pas en temps voulu, vérifiez si d’autres sessions dans la base de données bloquent la session ALTER DATABASE.
Pour plus d’informations sur les conventions de la syntaxe, consultez Conventions de la syntaxe Transact-SQL.
Sélectionner un produit
Dans la ligne suivante, sélectionnez le nom du produit qui vous intéresse. Ceci affiche un contenu différent ici dans cette page web, approprié pour le produit que vous sélectionnez.
* SQL Server *
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.
Notes
De nombreuses options SET de base de données sont configurables pour la session en cours avec des Instructions SET, et sont souvent configurées par les applications quand elles se connectent. Les options SET de niveau session remplacent les valeurs ALTER DATABASE SET. Les options de base de données décrites dans les sections suivantes sont des valeurs que vous pouvez définir pour les sessions qui ne fournissent pas explicitement d’autres valeurs pour les options SET.
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>
| <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
}
}
<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 la récupération de base de données accélérée pour chaque base de données. ADR est défini par défaut sur OFF dans SQL Server 2019 (15.x). Avec cette syntaxe, vous pouvez désigner un groupe de fichiers spécifique pour les données du magasin de versions persistantes. Si aucun groupe de fichiers n’est spécifié, le magasin de versions persistantes est stocké dans le groupe de fichiers PRIMARY. Pour obtenir des exemples et des informations supplémentaires, consultez 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. Ce comportement de réouverture se produit par exemple quand un utilisateur émet une instruction
USE database_name. La base de données peut être arrêtée proprement avec AUTO_CLOSE défini sur la valeur ON. Dans ce cas, la base de données ne se rouvre que lorsqu’un utilisateur tente d’utiliser la base de données au redémarrage suivant du Moteur de base de données.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.
Notes
L’option AUTO_CLOSE n’est pas disponible dans une base de données autonome, ni sur SQL Database.
Vous pouvez déterminer l’état de cette option en consultant la colonne is_auto_close_on de la vue de catalogue sys.databases ou la propriété IsAutoClose de la fonction DATABASEPROPERTYEX.
Quand AUTO_CLOSE est défini sur ON, certaines colonnes de la vue de catalogue sys.databases et la fonction DATABASEPROPERTYEX retournent une valeur NULL, car la base de données est indisponible pour l’extraction des données. Pour résoudre ce problème, exécutez une instruction USE pour ouvrir la base de données.
La mise en miroir de bases de données nécessite que AUTO_CLOSE soit défini sur OFF.
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é pratique dans certaines situations rares, par exemple dans une instance SQL Server qui n’a pas suffisamment de mémoire 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 mémoire nécessaires pour maintenir une base de données ouverte quand aucune application ne l’utilise. 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 en cas de besoin précis, n'attribuez pas la valeur ON à l'option de base de données AUTO_SHRINK. 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 réduits automatiquement lors des 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.
Notes
L’option AUTO_SHRINK n’est pas disponible dans une base de données autonome.
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 à utiliser le dernier plan correct connu. Si aucun gain de performances n’est détecté, le Moteur de base de données génère un nouveau plan de requête. L’instruction échoue si le Magasin des requêtes n’est pas activé ou s’il n’est pas en mode 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 la relation contenant-contenu de base de données sur la valeur partielle échouera si l'option de réplication, de capture des données modifiées ou de suivi des modifications est activée. 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 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 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’effectue pas la 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))
Permet de suspendre l’analyse du chiffrement après l’activation ou la désactivation du chiffrement TDE, ou après un changement 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, voir Transparent Data Encryption et Transparent Data Encryption avec Azure SQL Database.
Quand le chiffrement est activé au niveau de la base de données, tous les groupes de fichiers sont chiffrés. Tous les nouveaux groupes de fichiers héritent de la propriété chiffrée. Si des groupes de fichiers dans la base de données sont définis sur READ ONLY, l’opération de chiffrement de la 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 à l'état RESTORING durant une opération de restauration active, ou lorsqu'une opération de restauration d'un fichier de base de données ou d'un fichier journal échoue car un fichier de sauvegarde est corrompu.
<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 la base de données 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.
Notes
SET { READ_ONLY | READ_WRITE } est désactivé sur les bases de données fédérées Azure SQL Database.
<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. Si la valeur spécifiée est ON, le thread d’arrière-plan utilisé pour mettre à jour les statistiques se connecte à la base de données et vous ne pourrez pas accéder à celle-ci 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 :
Affectez la valeur OFF à AUTO_UPDATE_STATISTICS_ASYNC.
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 DISABLEDsont 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 ALLOWEDsont 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 FORCEDsont 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.
Important
L'instance de SQL Server reconnaît ce paramètre lorsque l'option de serveur cross db ownership chaining a la valeur 0 (OFF). Lorsque cross db ownership chaining a la valeur 1 (ON), toutes les bases de données utilisateur peuvent participer aux chaînages des propriétés des bases de données croisées, quelle que soit la valeur de cette option. Cette option est configurée à l’aide de sp_configure.
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.
Important
Cette option est autorisée uniquement lorsque CONTAINMENT a été défini sur PARTIAL. Si CONTAINMENT est défini sur NONE, des erreurs se produiront.
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 produiront.
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 produiront.
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 produiront.
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 produiront.
<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. Les bases de données restaurées à partir d’autres instances SQL Server et les bases de données ayant fait l’objet d’une mise à niveau sur place vers SQL Server 2022 (16.x) conservent les paramètres précédents du Magasin des requêtes. Si la surcharge pouvant être introduite par le Magasin des requêtes est source de préoccupation, 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 la base de données unique Azure SQL Database et le pool élastique. L’exécution de
ALTER DATABASE [database] SET QUERY_STORE = OFFrenverra l’avertissement'QUERY_STORE=OFF' is not supported in this version of SQL Server..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 maximal alloué au magasin des requêtes est atteinte, le mode d’opération du magasin des requêtes passe 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 MAX_STORAGE_SIZE_MB entre des vérifications de la 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é libéré, le mode Magasin des requêtes revient automatiquement en lecture-écriture.
Important
Si vous pensez que la capture de votre charge de travail nécessite plus de 10 Go d’espace disque, vous devez probablement repenser et optimiser votre charge de travail pour réutiliser les plans de requête (par exemple, en utilisant le paramétrage forcé) ou ajuster les configurations du Magasin des requêtes.
À compter de SQL Server 2019 (15.x) et dans Azure SQL Database, vous pouvez définir QUERY_CAPTURE_MODE sur CUSTOM pour disposer d’un contrôle supplémentaire sur la stratégie de capture des requêtes.
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 activé automatiquement quand 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.
Notes
Les curseurs, les requêtes dans les procédures stockées et les requêtes compilées en mode natif sont toujours capturés lorsque le mode de capture de requête est défini sur All (Tous), Auto ou Custom (Personnalisé).
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 manquer la capture de 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 seront 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 des statistiques d’attente par requête ne seront 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 paramètre QUERY_CAPTURE_MODE = AUTO capture les détails du Magasin des requêtes lorsque 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, voir Modes de récupération.
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 faire l’objet d’une journalisation minimale, voir 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, voir Modes de récupération.
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, voir Modes de récupération.
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 le choix du modèle de récupération, voir 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.
Important
La structure syntaxique TORN_PAGE_DETECTION ON | OFF sera supprimée dans une version ultérieure de SQL Server. Évitez d'utiliser cette structure syntaxique dans le développement de nouvelles applications et envisagez de modifier celles qui l'utilisent actuellement. Utilisez l'option PAGE_VERIFY à la place.
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 page de base de données ne génèrent pas de valeur CHECKSUM ni TORN_PAGE_DETECTION. SQL Server ne vérifie pas une somme de contrôle ou une page endommagée au cours d'une lecture, même si l'en-tête de page comporte une valeur CHECKSUM ou TORN_PAGE_DETECTION.
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éestempdbest 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éestempdb.Même si la valeur TORN_PAGE_DETECTION utilise moins de ressources, elle ne fournit qu'un sous-ensemble limité de la protection offerte par 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 permette de résoudre le problème d'endommagement des données, la cause première, par exemple une défaillance matérielle du disque, doit être identifiée et corrigée le plus rapidement possible pour éviter que ces erreurs persistent.
SQL Server procède à quatre nouvelles tentatives pour une lecture qui échoue avec une erreur de somme de contrôle, de page endommagée ou d'E/S disque. Si la lecture réussit lors de l’une de ces tentatives, un message est écrit dans le journal des erreurs. La commande qui a déclenché la lecture continue. La commande échoue avec le message d’erreur 824 si les tentatives de lecture échouent.
Pour plus d’informations sur les messages d’erreur 823, 824 et 825, consultez :
- How to troubleshoot a Msg 823 error in SQL Server (Comment faire pour dépanner une erreur Msg 823 dans SQL Server)
- How to troubleshoot a Msg 824 error in SQL Server (Comment faire pour dépanner une erreur Msg 824 dans SQL Server)
- Comment résoudre les problèmes de Msg – 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.
Important
Stretch Database est déprécié dans SQL Server 2022 (16.x) et Azure SQL Database. Cette fonctionnalité sera supprimée dans une version future de moteur de base de données. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.
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_ownerpour activer Stretch Database pour une table. Nécessite les autorisationsdb_owneretCONTROL DATABASEpour activer Stretch Database pour une base de données.SERVER = <server_name>
Spécifie l’adresse du serveur Azure. Incluez la partie
.database.windows.netdu 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 (anciennement Azure Active Directory).
- 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
dbmanagerousysadminsur 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.
<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.
Notes
ENABLE_BROKER requiert un verrou de base de données exclusif. Si d'autres sessions ont verrouillé des ressources dans la base de données, ENABLE_BROKER attend jusqu'à ce que les autres sessions libèrent leurs verrous. Pour activer Service Broker dans une base de données utilisateur, veillez à ce qu’aucune autre session n’utilise la base de données avant d’exécuter l’instruction ALTER DATABASE SET ENABLE_BROKER, par exemple en mettant la base de données en mode mono-utilisateur. Pour activer Service Broker dans la base de données msdb, arrêtez d’abord SQL Server Agent, afin que Service Broker puisse obtenir le verrou nécessaire.
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 dialogues avec des messages à envoyer lorsque l’instruction ALTER DATABASE est exécutée ne récupéreront pas le nouveau paramètre tant que certains messages pour le 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 s’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 configurez ALLOW_SNAPSHOT_ISOLATION dans une base de données en lecture seule (READ_ONLY), le paramètre est conservé si la base de données devient par la suite accessible en lecture et en écriture (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 configurez READ_COMMITTED_SNAPSHOT dans une base de données en lecture seule (READ_ONLY), le paramètre est conservé lorsque la base de données devient par la suite accessible en lecture et en écriture (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.
Avertissement
Quand une table est créée avec DURABILITY = SCHEMA_ONLY et que READ_COMMITTED_SNAPSHOT est changé par la suite à l’aide d’ALTER DATABASE, les données de la table sont perdues.
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 respectent 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.
Important
Dans une future version de SQL Server, ANSI_NULLS aura toujours la valeur ON et toute application qui définira explicitement l'option à OFF produira une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.
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.
Important
SET ANSI_NULLS doit également avoir la valeur ON 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 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.
Important
Dans une future version de SQL Server, ANSI_PADDING aura toujours la valeur ON et toute application qui définira explicitement l'option à OFF produira une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Il est recommandé de toujours affecter la valeur ON à l'option ANSI_PADDING. Par ailleurs, ANSI_PADDING doit avoir la valeur ON lorsque vous créez ou manipulez des index dans des colonnes calculées ou des vues indexées.
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.
Important
SET ANSI_WARNINGS doit avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.
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.
Important
SET ARITHABORT doit avoir la valeur ON 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 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, voir 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.
Important
CONCAT_NULL_YIELDS_NULL doit avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.
Dans une future version de SQL Server, CONCAT_NULL_YIELDS_NULL sera toujours ON et toute application qui définira explicitement l’option à OFF déclenchera une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.
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 simple (') fait partie de la chaîne littérale, il pourra être représenté par un guillemet double ('').
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 Database Identifiers.
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_onde la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriétéIsRecursiveTriggersEnabledde 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.
<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 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 pour les nouvelles bases de données est 1 minute, ce qui signifie 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 les 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, voir 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.
Notes
Toutes les options de base de données n’utilisent pas la clause WITH <termination>. Pour plus d’informations, consultez le tableau sous « Options de configuration » dans la section « Remarques » de cet article.
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 le changement d’état ou d’option de base de données demandé ne peut pas être effectué 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.
Notes
Cet exemple utilise l'option de fin WITH ROLLBACK IMMEDIATE dans la première instruction ALTER DATABASE . Toutes les transactions incomplètes seront restaurées et les autres connexions à la base de données AdventureWorks2022 immédiatement déconnecté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
)
);
Voir aussi
- Statistiques
- DATABASEPROPERTYEX
- sys.databases
- sys.data_spaces
- sys.database_automatic_tuning_options
- sys.database_automatic_tuning_mode
Étapes suivantes
* SQL Database *
SQL Database
Bien que les niveaux de compatibilité soient des options SET, ils sont décrits dans Niveau de compatibilité ALTER DATABASE.
Notes
La plupart des options SET de base de données sont configurables pour la session en cours avec les Instructions SET, souvent par des applications au moment de la connexion. Les options SET de niveau session remplacent les valeurs ALTER DATABASE SET. Les options de base de données décrites dans les sections suivantes sont des valeurs que vous pouvez définir pour les sessions qui ne fournissent pas explicitement d’autres valeurs pour les options SET.
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
CURRENTexécute l’action dans la base de données active.CURRENTn’est pas pris en charge pour toutes les options dans tous les contextes. SiCURRENTé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 en cas de besoin précis, n'attribuez pas la valeur ON à l'option de base de données AUTO_SHRINK. 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 réduits automatiquement lors des 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.
Notes
L’option AUTO_SHRINK n’est pas disponible dans une base de données autonome.
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_onde la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriétéIsAutoUpdateStatisticsde 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 l’optimisation automatique sur AUTO applique les paramètres de configuration Azure par défaut à l’optimisation 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. Veuillez noter que, pour que l’héritage fonctionne, les trois options de réglage FORCE_LAST_GOOD_PLAN, CREATE_INDEX et DROP_INDEX doivent être définies sur DEFAULT dans les bases de données.
CUSTOM
Avec la valeur CUSTOM, vous devrez personnaliser la configuration de chaque option de l’optimisation automatique disponible 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 pour les nouveaux serveurs Azure SQL est ON. Par défaut, les nouvelles bases de données héritent donc 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 à utiliser le dernier plan correct connu. Si aucun gain de performances n’est détecté, le Moteur de base de données génère un nouveau plan de requête. L’instruction échoue si le Magasin des requêtes n’est pas activé ou s’il n’est pas en mode 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 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 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, voir Transparent Data Encryption et Transparent Data Encryption avec Azure SQL Database.
Quand le chiffrement est activé au niveau de la base de données, tous les groupes de fichiers sont chiffrés. Tous les nouveaux groupes de fichiers héritent de la propriété chiffrée. Si des groupes de fichiers dans la base de données sont définis sur READ ONLY, l’opération de chiffrement de la 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 la base de données 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.
Notes
SET { READ_ONLY | READ_WRITE } est désactivé sur les bases de données fédérées Azure SQL Database.
<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_owneret des rôles serveur fixesdbcreatoretsysadminà 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 lamasterbase de données, vous pouvez rencontrer un message d’erreurMsg 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_accessde la vue de catalogue sys.databases ou la propriétéUserAccessde la fonction DATABASEPROPERTYEX. Dans Azure SQL Database, doit être exécuté à partir de la base de données utilisateur. À partir de lamasterbase de données, vous pouvez rencontrer un message d’erreurMsg 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 DISABLEDsont 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 ALLOWEDsont 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 FORCEDsont 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 = OFFrenverra l’avertissement'QUERY_STORE=OFF' is not supported in this version of SQL Server..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 est épuisé, le mode d’opération du magasin des requêtes passe à 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.
Notes
Dans Azure SQL Database, la valeur par défaut MAX_STORAGE_SIZE_MB diffère selon le niveau de service, comme suit : Premium, critique pour l'entreprise et Hyperscale : 1 024 Mo ; Standard et usage général : 100 Mo ; De base : 10 Mo La valeur maximale autorisée MAX_STORAGE_SIZE_MB est de 10 240 Mo.
Notes
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 MAX_STORAGE_SIZE_MB entre des vérifications de la 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é libéré, le mode Magasin des requêtes revient automatiquement en lecture-écriture.
Important
Si vous pensez que la capture de votre charge de travail nécessite plus de 10 Go d’espace disque, vous devez probablement repenser et optimiser votre charge de travail pour réutiliser les plans de requête (par exemple, en utilisant le paramétrage forcé) ou ajuster les configurations du Magasin des requêtes.
À compter de SQL Server 2019 (15.x) et dans Azure SQL Database, vous pouvez définir QUERY_CAPTURE_MODE sur CUSTOM pour disposer d’un contrôle supplémentaire sur la stratégie de capture des requêtes.
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 activé automatiquement quand la quantité totale de données approche de la taille maximale.
OFF
Le nettoyage basé sur la taille n’est pas activé automatiquement.
AUTO
Le nettoyage basé sur la taille est activé automatiquement quand 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.
Notes
Les curseurs, les requêtes dans les procédures stockées et les requêtes compilées en mode natif sont toujours capturés lorsque le mode de capture de requête est défini sur All (Tous), Auto ou Custom (Personnalisé).
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 manquer la capture de 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 seront 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 des statistiques d’attente par requête ne seront 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 s’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 configurez ALLOW_SNAPSHOT_ISOLATION dans une base de données en lecture seule (READ_ONLY), le paramètre est conservé si la base de données devient par la suite accessible en lecture et en écriture (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 configurez READ_COMMITTED_SNAPSHOT dans une base de données en lecture seule (READ_ONLY), le paramètre est conservé lorsque la base de données devient par la suite accessible en lecture et en écriture (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.
Avertissement
Quand une table est créée avec DURABILITY = SCHEMA_ONLY, et READ_COMMITTED_SNAPSHOT est ensuite modifié à l’aide de ALTER DATABASE, les données de la table seront perdues.
Conseil
Dans Azure SQL Database, la commande ALTER DATABASE permettant d’activer ou de désactiver READ_COMMITTED_SNAPSHOT pour une base de données doit être exécutée dans la base de données master.
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 respectent 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.
Important
Dans une future version de SQL Server, ANSI_NULLS aura toujours la valeur ON et toute application qui définira explicitement l'option à OFF produira une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.
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.
Notes
SET ANSI_NULLS doit également avoir la valeur ON 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 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.
Important
Dans une future version de SQL Server, ANSI_PADDING aura toujours la valeur ON et toute application qui définira explicitement l'option à OFF produira une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Il est recommandé de toujours affecter la valeur ON à l'option ANSI_PADDING. Par ailleurs, ANSI_PADDING doit avoir la valeur ON lorsque vous créez ou manipulez des index dans des colonnes calculées ou des vues indexées.
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.
Notes
SET ANSI_WARNINGS doit avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.
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.
Notes
SET ARITHABORT doit avoir la valeur ON 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 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, voir 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.
Notes
CONCAT_NULL_YIELDS_NULL doit avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.
Dans une future version de SQL Server, CONCAT_NULL_YIELDS_NULL aura toujours la valeur ON et toute application qui définira explicitement l'option à OFF produira une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.
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 interdits dans les identificateurs Transact-SQL. Si un guillemet simple (') fait partie de la chaîne littérale, il pourra être représenté par un guillemet double ('').
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 ([ ]). Les identificateurs entre crochets peuvent toujours être utilisés, quel que soit le paramètre QUOTED_IDENTIFIER. Pour plus d'informations, consultez Database Identifiers.
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_onde la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriétéIsRecursiveTriggersEnabledde 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> ::=
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 pour les nouvelles bases de données est 1 minute, ce qui signifie 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 les 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, voir 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.
Notes
Toutes les options de base de données n’utilisent pas la clause WITH <termination>. Pour plus d’informations, consultez le tableau sous « Options de configuration » dans la section « Remarques » de cet article.
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 le changement d’état ou d’option de base de données demandé ne peut pas être effectué 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’effectue pas la 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 may 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
)
);
Voir aussi
- Statistiques
- DATABASEPROPERTYEX
- sys.databases
- sys.data_spaces
- sys.database_automatic_tuning_options
- sys.database_automatic_tuning_mode
Étapes suivantes
* SQL Managed Instance *
Azure SQL Managed Instance
Bien que les niveaux de compatibilité soient des options SET, ils sont décrits dans Niveau de compatibilité ALTER DATABASE.
Notes
La plupart des options SET de base de données sont configurables pour la session en cours avec les Instructions SET, souvent par des applications au moment de la connexion. Les options SET de niveau session remplacent les valeurs ALTER DATABASE SET. Les options de base de données décrites dans les sections suivantes sont des valeurs que vous pouvez définir pour les sessions qui ne fournissent pas explicitement d’autres valeurs pour les options SET.
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_onde la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriétéIsAutoCreateStatisticsde 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 en cas de besoin précis, n'attribuez pas la valeur ON à l'option de base de données AUTO_SHRINK. 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 réduits automatiquement lors des 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.
Notes
L’option AUTO_SHRINK n’est pas disponible dans une base de données autonome.
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 à utiliser le dernier plan correct connu. Si aucun gain de performances n’est détecté, le Moteur de base de données génère un nouveau plan de requête. L’instruction échoue si le Magasin des requêtes n’est pas activé ou s’il n’est pas en mode 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, voir Transparent Data Encryption et Transparent Data Encryption avec Azure SQL Database.
Quand le chiffrement est activé au niveau de la base de données, tous les groupes de fichiers sont chiffrés. Tous les nouveaux groupes de fichiers héritent de la propriété chiffrée. Si des groupes de fichiers dans la base de données sont définis sur READ ONLY, l’opération de chiffrement de la 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 DISABLEDsont 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 ALLOWEDsont 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 FORCEDsont 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 est épuisé, le mode d’opération du magasin des requêtes passe à 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 MAX_STORAGE_SIZE_MB entre des vérifications de la 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é libéré, le mode Magasin des requêtes revient automatiquement en lecture-écriture.
Important
- Si vous pensez que la capture de votre charge de travail nécessite plus de 10 Go d’espace disque, vous devez probablement repenser et optimiser votre charge de travail pour réutiliser les plans de requête (par exemple, en utilisant le paramétrage forcé) ou ajuster les configurations du Magasin des requêtes.
- À compter de SQL Server 2019 (15.x) et dans Azure SQL Database, vous pouvez définir
QUERY_CAPTURE_MODEsur CUSTOM pour disposer d’un contrôle supplémentaire sur la stratégie de capture des requêtes. - La limite du paramètre
MAX_STORAGE_SIZE_MBest de 10 240 Mo sur Azure SQL Managed Instance.
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 activé automatiquement quand la quantité totale de données approche de la taille maximale.
OFF
Le nettoyage basé sur la taille n’est pas activé automatiquement.
AUTO
Le nettoyage basé sur la taille est activé automatiquement quand 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 manquer la capture de 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 seront 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 des statistiques d’attente par requête ne seront 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 s’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.
Avertissement
Quand une table est créée avec DURABILITY = SCHEMA_ONLY et que READ_COMMITTED_SNAPSHOT est changé par la suite à l’aide d’ALTER DATABASE, les données de la table sont perdues.
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 respectent 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.
Important
Dans une future version de SQL Server, ANSI_NULLS aura toujours la valeur ON et toute application qui définira explicitement l'option à OFF produira une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.
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.
Important
SET ANSI_NULLS doit également avoir la valeur ON 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 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.
Important
Dans une future version de SQL Server, ANSI_PADDING aura toujours la valeur ON et toute application qui définira explicitement l'option à OFF produira une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Il est recommandé de toujours affecter la valeur ON à l'option ANSI_PADDING. Par ailleurs, ANSI_PADDING doit avoir la valeur ON lorsque vous créez ou manipulez des index dans des colonnes calculées ou des vues indexées.
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.
Important
SET ANSI_WARNINGS doit avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.
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.
Important
SET ARITHABORT doit avoir la valeur ON 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 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, voir 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.
Important
CONCAT_NULL_YIELDS_NULL doit avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.
Dans une future version de SQL Server, CONCAT_NULL_YIELDS_NULL aura toujours la valeur ON et toute application qui définira explicitement l'option à OFF produira une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.
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 interdits dans les identificateurs Transact-SQL. Si un guillemet simple (') fait partie de la chaîne littérale, il pourra être représenté par un guillemet double ('').
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 ([ ]). Les identificateurs entre crochets peuvent toujours être utilisés, quel que soit le paramètre QUOTED_IDENTIFIER. Pour plus d'informations, consultez Database Identifiers.
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_onde la vue de catalogue sys.databases. Vous pouvez également déterminer l’état en consultant la propriétéIsRecursiveTriggersEnabledde 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 pour les nouvelles bases de données est 1 minute, ce qui signifie 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 les 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.
Notes
Toutes les options de base de données n’utilisent pas la clause WITH <termination>. Pour plus d’informations, consultez le tableau sous « Options de configuration » dans la section « Remarques » de cet article.
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 le changement d’état ou d’option de base de données demandé ne peut pas être effectué 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’effectue pas la 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
)
);
Voir aussi
- Statistiques
- DATABASEPROPERTYEX
- sys.databases
- sys.data_spaces
- sys.database_automatic_tuning_options
- sys.database_automatic_tuning_mode
Étapes suivantes
* Azure Synapse
Analytics *
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 Chiffrement transparent des données (TDE) et Chiffrement transparent des données pour SQL Database, SQL Managed Instance et Azure Synapse Analytics.
Quand le chiffrement est activé au niveau de la base de données, tous les groupes de fichiers sont chiffrés. Tous les nouveaux groupes de fichiers héritent de la propriété chiffrée. Si des groupes de fichiers dans la base de données sont définis sur READ ONLY, l’opération de chiffrement de la 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.
Notes
Pour Azure Synapse Analytics, vous devez exécuter ALTER DATABASE SET QUERY_STORE à partir de la base de données utilisateur. L’exécution de l’instruction à partir d’une autre instance d’entrepôt de données n’est pas prise en charge.
Notes
Pour Azure Synapse Analytics, le Magasin des requêtes peut être activé comme sur les autres plateformes, mais les options de configuration supplémentaires ne sont pas prises en charge.
<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 seront 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 seront 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 de résultats pour une base de données, le cache de résultats rendu persistant auparavant sera immédiatement supprimé depuis le 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 des 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 la correspondance dans le cache, 0 pour l’absence dans le cache et des valeurs négatives reflétant les 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'>
Notes
La mise en cache du jeu de résultats ne doit pas être utilisée conjointement avec DECRYPTBYKEY. Si cette fonction de chiffrement doit être utilisée, vérifiez que la mise en cache du jeu de résultats est désactivée (au niveau de la session ou au niveau de la base de données) au moment de l’exécution.
Important
Les opérations de création du cache des jeux de résultats et de récupération des données à partir du cache ont lieu sur le nœud de contrôle d’une instance Data Warehouse. Quand la mise en cache des jeux de résultats est activée (ON), l’exécution de requêtes qui retournent un jeu de résultats volumineux (par exemple, > 1 million de lignes) peut entraîner une utilisation intensive du processeur sur le nœud de contrôle et allonger ainsi le temps de réponse de toutes les requêtes sur l’instance. Ces requêtes sont couramment utilisées lors de l’exploration de données et des opérations ETL. Pour éviter une utilisation trop intensive du nœud de contrôle et les problèmes de performances qui en découlent, les utilisateurs doivent désactiver (OFF) la mise en cache des jeux de résultats sur la base de données avant d’exécuter ces types de requêtes.
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. La définition de READ_COMMITTED_SNAPSHOT sur ON ou sur OFF pour une base de données utilisateur entraîne la fermeture de toutes les connexions ouvertes à cette base de données. Vous pouvez effectuer cette modification pendant la fenêtre de maintenance de la base de données ou attendre qu’il n’existe plus 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 la fonction READ_COMMITTED_SNAPSHOT activée, les requêtes peuvent avoir 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 le Magasin des requêtes sur 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;
Voir aussi
Étapes suivantes
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour