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

Article
10/13/2015

Cette rubrique fournit une vue d’ensemble des points de contrôle de base de données SQL Server. 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 des points de contrôle

Pour des raisons de performances, le moteur de base de données apporte des modifications aux pages de base de données en mémoire 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), les informations du journal des transactions de la mémoire vers le disque et enregistre également les informations concernant 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	Transact-SQL Interface	Description
Automatique	EXEC sp_configure *'`recovery interval`',`seconds`'*	Émis automatiquement en arrière-plan pour respecter la limite de temps supérieure suggérée par l’option de configuration du `recovery interval` serveur. 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 attente et du fait que le moteur de base de données détecte une augmentation de la latence d’écriture au-delà de 20 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. 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 sur >0, cette valeur est utilisée, plutôt que l’intervalle de récupération spécifié pour le serveur instance. 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	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.

Notes

L’option -kd’installation avancée SQL Server permet à un administrateur de base de données de limiter le comportement des points de contrôle d’E/S 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écapitule l’interaction entre le paramètre sp_configure à l’échellerecovery interval du serveur et le paramètre ALTER DATABASE spécifique à la base de données ... TARGET_RECOVERY_TIME paramètre.

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_configurerecovery 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 estimé par le Moteur de base de données qu’il peut traiter pendant la durée spécifiée dans l’option de configuration du recovery interval serveur. 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 des points de contrôle automatiques dépend de l’option recovery interval de configuration de serveur avancée, qui spécifie la durée maximale qu’un serveur donné instance 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 qui utilise 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.

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 recovery interval paramètre 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.

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) utilisant des transactions courtes, 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 de longue durée. La récupération d’une base de données avec une transaction de longue durée peut prendre beaucoup plus de temps que celle spécifiée dans l’option recovery interval . Par exemple, si une transaction de longue durée a mis deux heures à effectuer des mises à jour avant que le serveur instance ne soit désactivé, la récupération réelle prend beaucoup plus de temps que la recovery interval valeur pour récupérer la transaction longue. 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).

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 augmente, la recovery interval récupération de la base de données prend beaucoup plus de temps. Par exemple, si vous modifiez recovery interval 10, la récupération prend environ 10 fois plus de temps que lorsque recovery interval est défini sur zéro.

Points de contrôle indirects

Les points de contrôle indirects, nouveautés de SQL Server 2012, offrent une alternative configurable au niveau de la base de données 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 :

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. 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 de l’intervalle de récupération 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 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.

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 en arrêtant le 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.
Mise hors ligne d'une instance de cluster de basculement (FCI) SQL Server.