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

  • Article

Cette rubrique fournit une vue d'ensemble des points de contrôle de base de données SQL Server. Un point de contrôle permet la création d'un point de référence connu et fiable à partir duquel le Moteur de base de données SQL Server peut, lors d'une récupération faisant suite à une panne ou à un arrêt imprévu, commencer à appliquer les modifications contenues dans le journal.

Dans cette rubrique :

  • Vue d'ensemble des points de contrôle

  • Tâches associées

  • Contenu connexe

Vue d'ensemble des points de contrôle

Pour des raisons de performances, le Moteur de base de données procède aux modifications des pages de base de données en mémoire (dans le cache des tampons) sans les écrire à chaque fois sur le disque. En revanche, le Moteur de base de données publie périodiquement un point de contrôle dans chaque base de données. Un point de contrôle écrit les pages modifiées en mémoire actuelles (appelées pages de modifications), les informations du journal des transactions de la mémoire vers le disque et enregistre également les informations sur 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 accélérés en fonction du nombre d'écritures en attente et si le Moteur de base de données détecte une augmentation de latence d'écriture supérieure à 20 millisecondes.

Pour plus d'informations, consultez Configurer l'option de configuration du serveur recovery interval.

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. Le temps de récupération cible par défaut est 0, ce qui provoque l'utilisation de l'heuristique du point de contrôle automatique sur la base de données. Si vous avez utilisé ALTER DATABASE pour définir TARGET_RECOVERY_TIME à >0, cette valeur est utilisée, plutôt que l'intervalle de récupération spécifié pour l'instance de serveur.

Pour plus d'informations, consultez Modifier 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 Transact-SQL. 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. Éventuellement, le paramètre checkpoint_duration spécifie une durée demandée, en secondes, pour l'exécution du point de contrôle.

Pour plus d'informations, consultez CHECKPOINT (Transact-SQL).

Interne

Aucun.

É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.

[!REMARQUE]

L'option d'installation avancée -k SQL Server permet à un administrateur de base de données d'accélérer le comportement d'E/S de point 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 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.

[Haut de la page]

Dans cette section :

  • Interaction des options TARGET_RECOVERY_TIME et « recovery interval »

  • Points de contrôle automatiques

  • Points de contrôle indirects

  • Point de contrôle interne

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 ALTER 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 la durée spécifiée dans l'option de configuration de serveur recovery interval. Dans chaque base de données sans temps 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 des points de contrôle automatiques 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 maximal d'enregistrements de journal qu'il peut traiter dans l'intervalle de récupération. Lorsqu'une base de données qui utilise les points de contrôle automatiques atteint ce nombre maximal d'enregistrements du 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.

De plus, 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. À l'inverse, en mode de récupération utilisant les journaux de transactions, une fois qu'une chaîne de sauvegarde du journal a été établie, les points de contrôle automatiques ne provoquent pas la troncation 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 précise de récupération. De plus, lorsqu'un point de contrôle automatique est en cours, l'activité d'E/S générale des données augmente considérablement et de manière imprévisible.

[Haut de la page]

Impact de l'intervalle de récupération sur les performances 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 l'option 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 la durée de récupération, consultez Journal des transactions (SQL Server).

En général, les valeurs par défaut fournissent les 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 configurez recovery interval à 10, la récupération prend environ 10 fois plus de temps que si recovery interval est défini à zéro.

[Haut de la page]

Points de contrôle indirects

Les points de contrôle indirects, nouveauté de SQL Server 2012, fournissent une alternative au niveau de la base de données configurable aux points de contrôle automatiques. 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 peuvent réduire le temps de récupération global de base de données.

  • 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 lorsqu'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.

Toutefois, 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.

[Haut de la page]

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.

  • Un instantané de base de données est créé, 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 par l'arrêt du service SQL Server (MSSQLSERVER). Ces opérations provoquent la création d'un point de contrôle dans chaque base de données dans l'instance de SQL Server.

  • La mise hors connexion d'une instance de cluster de basculement (FCI) SQL Server.

[Haut de la page]

Tâches associées

Pour modifier l'intervalle de récupération sur une instance de serveur

Pour configurer des points de contrôle indirects sur une base de données

Pour émettre un point de contrôle manuel sur une base de données

[Haut de la page]

Contenu connexe

[Haut de la page]

Voir aussi

Concepts

Journal des transactions (SQL Server)