Points de contrôle de base de données (SQL Server)

  • Article

S’applique à :SQL ServerAzure SQL Database

Un point de contrôle crée un bon point connu à partir duquel le moteur de base de données SQL Server peut commencer à appliquer les modifications contenues dans le journal pendant la récupération après un arrêt ou un crash inattendu.

Vue d’ensemble

Pour des raisons de performances, le moteur de base de données effectue les modifications des pages de la base de données en mémoire, dans le cache tampon, et n'écrit pas ces pages sur le disque après chaque modification. Au contraire, le moteur de base de données émet périodiquement un point de contrôle sur chaque base de données. Un point de contrôle écrit les pages modifiées en mémoire actuelles (appelées pages de modifications) et les informations du journal des transactions de la mémoire vers le disque, et il enregistre également les informations dans le journal des transactions.

Le moteur de base de données prend en charge plusieurs types de points de contrôle : automatique, indirect, manuel et interne. Le tableau suivant récapitule les types de points de contrôle.

Nom Interface Transact-SQL Description
Automatique EXEC sp_configure 'recovery interval', ' seconds' Émis automatiquement en arrière-plan pour respecter la limite de durée supérieure suggérée par l'option de configuration de serveur recovery interval . Les points de contrôle automatiques s'exécutent jusqu'à la fin. Les points de contrôle automatiques sont limités en fonction du nombre d'écritures en cours et si le moteur de base de données détecte une augmentation de la latence d'écriture supérieure à 50 millisecondes.

Pour plus d'informations, consultez Configure the recovery interval Server Configuration Option.
Indirect ALTER DATABASE ... SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES } Émis en arrière-plan pour obtenir un temps de récupération cible spécifié par l'utilisateur pour une base de données. À compter de SQL Server 2016 (13.x), la valeur par défaut est de 1 minute. La valeur par défaut est 0 pour les anciennes versions, ce qui indique que la base de données utilisera 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.

Pour plus d’informations, consultez Changer la durée de récupération cible d’une base de données (SQL Server).
Manuel CHECKPOINT [ checkpoint_duration ] Émis lorsque vous exécutez une commande CHECKPOINT. Le point de contrôle manuel intervient dans la base de données active pour votre connexion. Par défaut, les points de contrôle manuels s'exécutent jusqu'à la fin. L'accélération fonctionne de la même manière que pour les points de contrôle automatiques. Le paramètre checkpoint_duration peut aussi spécifier la durée demandée (en secondes) de l’exécution du point de contrôle.

Pour plus d'informations, voir CHECKPOINT (Transact-SQL).
Interne None Émis par différentes opérations de serveur, telles que la création de sauvegarde et d'instantané de base de données pour garantir que les images de disque correspondent à l'état actuel du journal.

L'option de configuration avancée de SQL Server-k permet à un administrateur de base de données de limiter le comportement d'E/S des points de contrôle en fonction du débit du sous-système d'E/S pour certains types de points de contrôle. L’option d’installation -k s’applique aux points de contrôle automatiques, ainsi qu’à tous les points de contrôle manuels et internes non accélérés.

Pour les points de contrôle automatiques, manuels et internes, seules les modifications apportées après le dernier point de contrôle doivent être restaurées par progression lors de la récupération de la base de données. Cela réduit le temps nécessaire pour récupérer une base de données.

Important

Les transactions non validées longues augmentent le temps de récupération pour tous les types de points de contrôle.

Interaction des options TARGET_RECOVERY_TIME et « recovery interval »

Le tableau suivant résume l'interaction entre le paramètre à sp_configure 'recovery interval'l'échelle du serveur et le paramètre spécifique à la base de donnéesALTER DATABASE ... TARGET_RECOVERY_TIME.

target_recovery_time 'recovery interval' Type de point de contrôle utilisé
0 0 Points de contrôle automatiques dont l'intervalle de récupération cible est de 1 minute.
0 > 0 Points de contrôle automatiques dont l’intervalle de récupération cible est spécifié par le paramètre défini par l’utilisateur de l’option sp_configure 'recovery interval' .
> 0 Non applicable Points de contrôle indirects dont le temps de récupération cible est déterminé par le paramètre TARGET_RECOVERY_TIME, exprimé en secondes.

Points de contrôle automatiques

Un point de contrôle automatique se produit chaque fois que le nombre d'enregistrements de journal atteint le nombre que le moteur de base de données estime pouvoir traiter pendant le temps spécifié dans l'option de configuration de serveur intervalle de récupération. Pour plus d'informations, consultez Configure the recovery interval Server Configuration Option.

Dans chaque base de données sans délai de récupération cible défini par l'utilisateur, le moteur de base de données génère des points de contrôle automatiques. La fréquence dépend de l’option de configuration de serveur avancée recovery interval , qui spécifie la durée maximale qu’une instance de serveur donnée doit utiliser pour récupérer une base de données lors d’un redémarrage du système. Le moteur de base de données estime le nombre maximum d'enregistrements de journal qu'il peut traiter dans l'intervalle de récupération. Lorsqu'une base de données utilisant des points de contrôle automatiques atteint ce nombre maximal d'enregistrements de journal, le moteur de base de données émet un point de contrôle sur la base de données.

L’intervalle de temps entre les points de contrôle automatiques peut varier fortement . Une base de données avec une charge de travail transactionnelle substantielle aura des points de contrôle plus fréquents qu’une base de données utilisée principalement pour des opérations en lecture seule. En mode de récupération simple, un point de contrôle automatique est également mis en file d’attente si le journal est rempli à 70 %.

En mode de récupération simple, à moins qu'un facteur retarde la troncation du journal, un point de contrôle automatique tronque la section inutilisée du journal des transactions. En revanche, dans le cadre des modèles de récupération complète et en masse, une fois qu'une chaîne de sauvegarde du journal a été établie, les points de contrôle automatiques ne provoquent pas la troncature du journal. Pour plus d’informations, consultez Journal des transactions (SQL Server).

Après une panne système, le temps nécessaire pour récupérer une base de données dépend principalement de la quantité d’E/S aléatoire nécessaire aux pages de restauration par progression qui ont été modifiées au moment de l’incident. Cela signifie que le paramètre recovery interval n'est pas fiable. Il ne peut pas déterminer une durée de récupération précise. En outre, lorsqu'un point de contrôle automatique est en cours, l'activité générale d'E/S pour les données augmente de manière significative et imprévisible.

Effet de l'intervalle de récupération sur la performance de récupération

Pour un système de traitement transactionnel en ligne (OLTP, online transaction processing), qui utilise de petites transactions, le paramètre recovery interval est le principal facteur déterminant le temps de récupération. Toutefois, l’option recovery interval n’affecte pas le temps nécessaire pour annuler une transaction longue. La récupération d’une base de données avec une transaction longue peut prendre beaucoup plus de temps que la valeur spécifiée dans le paramètre recovery interval .

Par exemple, si une transaction d’exécution longue a mis deux heures pour effectuer des mises à jour avant la défaillance de l’instance de serveur, la récupération elle-même prendra beaucoup plus de temps pour restaurer la transaction longue que la valeur spécifiée pour l’option recovery interval . Pour plus d'informations sur l'impact d'une transaction longue sur le temps de récupération, consultez la section Le journal des transactions (SQL Server). Pour plus d’informations sur le processus de récupération, consultez Vue d’ensemble de la restauration et de la récupération (SQL Server).

En général, les valeurs par défaut offrent des performances de récupération optimales. Toutefois, modifier l'intervalle de récupération peut améliorer les performances dans les circonstances suivantes :

  • Si la récupération prend régulièrement beaucoup plus d'une minute lorsque les transactions longues ne sont pas restaurées.

  • Si vous remarquez que les points de contrôle fréquents altèrent les performances sur une base de données.

Si vous décidez d'augmenter le paramètre recovery interval , nous vous recommandons de l'augmenter progressivement par de petits incréments et d'évaluer l'effet de chaque augmentation incrémentielle sur les performances de récupération. Cette approche est importante, car à mesure que le paramètre recovery interval augmente, la récupération de la base de données prend plus de longtemps. Par exemple, si vous indiquez un recovery interval de 10 minutes, la récupération prend environ 10 fois plus de temps que si le paramètre recovery interval est défini sur 1 minute.

Points de contrôle indirects

Les points de contrôle indirects, introduits dans SQL Server 2012 (11.x), offrent une alternative configurable aux points de contrôle automatiques au niveau de la base de données. Ceci peut être configuré en spécifiant l’option de configuration de base de données Temps de récupération cible. Pour plus d’informations, consultez Changer la durée de récupération cible d’une base de données (SQL Server). En cas de panne système, les points de contrôle indirects fournissent un temps de récupération plus prédictible et plus rapide que les points de contrôle automatiques.

Les points de contrôle indirects offrent les avantages suivants :

  • Les points de contrôle indirects permettent de s’assurer que le nombre de pages de modifications est inférieur à un certain seuil afin que la récupération de la base de données se termine dans le temps de récupération cible.

    L’option de configuration recovery interval utilise le nombre de transactions pour déterminer le temps de récupération, contrairement aux points de contrôle indirects, qui utilisent le nombre de pages de modifications. Quand des points de contrôle indirects sont activés sur une base de données recevant un grand nombre d’opérations DML, l’enregistreur en arrière-plan peut commencer à vider de manière intense les mémoires tampons modifiées sur le disque afin de s’assurer que le délai nécessaire à la récupération se situe dans le temps de récupération cible défini de la base de données. Cela peut entraîner une activité supplémentaire en termes d’E/S sur certains systèmes, ce qui peut contribuer à un goulot d’étranglement des performances si le sous-système du disque fonctionne au-delà du seuil d’E/S ou s’en rapproche.

  • Les points de contrôle indirects vous permettent de contrôler le temps de récupération de base de données de manière fiable en factorisant le coût des E/S aléatoires lors des récupérations de type REDO. Cela permet à une instance de serveur de rester dans la limite supérieure de temps de récupération pour une base de données (sauf quand une transaction longue entraîne des temps excessifs de récupération de type UNDO).

  • Les points de contrôle indirects réduisent les pics d'E/S associées au point de contrôle en entrant en continu des pages de modifications sur le disque en arrière-plan.

Une charge de travail transactionnelle en ligne sur une base de données configurée pour les points de contrôle indirects peut rencontrer une dégradation des performances. Cela est dû au fait que l'enregistreur en arrière-plan utilisé par le point de contrôle indirect augmente parfois la charge d'écriture totale pour une instance de serveur.

Important

Le point de contrôle indirect est le comportement par défaut des nouvelles bases de données créées dans SQL Server 2016 (13.x), y compris les bases de données model et tempdb.

Les bases de données qui ont été mises à niveau sur place, ou restaurées à partir d'une version précédente de SQL Server, utiliseront le comportement précédent de point de contrôle automatique à moins d'être explicitement modifiées pour utiliser le point de contrôle indirect.

Scalabilité du point de contrôle indirect améliorée

Avant SQL Server 2019 (15.x), il se peut que vous rencontriez des erreurs de planificateur non cédées lorsqu'une base de données génère un grand nombre de pages sales, telles que tempdb. SQL Server 2019 (15.x) introduit une meilleure évolutivité pour le point de contrôle indirect, ce qui devrait permettre d'éviter ces erreurs sur les bases de données qui ont une charge de travail UPDATE/INSERTimportante.

Points de contrôle internes

Les points de contrôle internes sont générés par les divers composants serveur pour garantir que les images de disque correspondent à l'état actuel du journal. Les points de contrôle internes sont générés en réponse aux événements suivants :

  • Des fichiers de base de données ont été ajoutés ou supprimés à l'aide de l'instruction ALTER DATABASE.

  • Une sauvegarde de la base de données est effectuée.

  • Une capture instantanée de base de données est créée, explicitement ou en interne pour DBCC CHECK.

  • Une activité nécessitant l'arrêt de la base de données est effectuée. Par exemple, la valeur ON est attribuée à AUTO_CLOSE et la dernière connexion utilisateur à la base de données est fermée, ou une modification d'une option de base de données nécessitant un redémarrage de la base de données est effectuée.

  • Une instance de SQL Server est arrêtée en arrêtant le service SQL Server (MSSQLSERVER). Cette action provoque un point de contrôle dans chaque base de données de l'instance de SQL Server.

  • Mise hors ligne d'une instance de cluster de basculement (FCI) SQL Server.

Étapes suivantes

Voir aussi