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

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 point de contrôle connu à partir duquel le moteur de base de données SQL Server peut commencer à appliquer des modifications contenues dans le journal pendant la récupération après un arrêt ou un blocage inattendu.

Vue d’ensemble des points de contrôle

Pour des raisons de performances, le moteur de base de données effectue des modifications dans les pages de base de données dans le cache de mémoire tampon et n’écrit pas ces pages sur le disque après chaque modification. Au lieu de cela, le moteur de base de données émet régulièrement un point de contrôle sur chaque base de données. Un point de contrôle écrit les pages modifiées actuellement en mémoire (appelées pages modifiées) et les informations du journal de transactions de la mémoire sur le disque, et enregistre également des informations sur le journal de 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	Descriptif
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 supérieure à 20 millisecondes. Pour plus d’informations, consultez Configurer l’option de configuration du serveur d’intervalle de récupération.
Indirect	ALTER DATABASE ... SET TARGET_RECOVERY_TIME =target_recovery_time { SECONDS \| MINUTES }	Émis en arrière-plan pour respecter le temps de récupération cible spécifié par l’utilisateur pour une base de données donnée. Le temps de récupération cible par défaut est 0, ce qui entraîne l’utilisation d’heuristiques automatiques de point de contrôle 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 l’instance de serveur. Pour plus d’informations, consultez Modifier le temps de récupération cible d’une base de données (SQL Server).
Manuel	POINT DE CONTRÔLE [ checkpoint_duration ]	Émis lorsque vous exécutez une commande Transact-SQL CHECKPOINT. Le point de contrôle manuel se produit 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. La gestion du débit fonctionne de manière identique à celle des points de contrôle automatiques. Si vous le souhaitez, le paramètre checkpoint_duration spécifie une durée demandée, en secondes, pour que le point de contrôle se termine. Pour plus d’informations, consultez CHECKPOINT (Transact-SQL).
Interne	Aucun.	Émis par diverses opérations de serveur, telles que la création de sauvegardes et de captures instantanées de bases de données, pour garantir que les images disque reflètent l'état actuel du journal.

Remarque

L’option -kd’installation avancée de SQL Server permet à un administrateur de base de données de limiter le comportement des 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 -k d’installation s’applique aux points de contrôle automatiques et à tous les points de contrôle manuels et internes non dérotté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 transférées pendant la récupération de la base de données. Cela réduit le temps nécessaire à la récupération d’une base de données.

Important

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

Interaction des options TARGET_RECOVERY_TIME et « intervalle de récupération »

Le tableau suivant récapitule l’interaction entre le paramètre sp_configurerecovery interval à l’échelle du serveur et le paramètre ALTER DATABASE spécifique à la base de données ... TARGET_RECOVERY_TIME paramètre.

TEMPS_CIBLE_DE_RÉCUPÉRATION	'intervalle de récupération'	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 la valeur définie 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 le temps spécifié dans l’option de configuration du recovery interval serveur. 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 recovery interval de configuration de serveur avancée, 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 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 être très variable. Une base de données avec une charge de travail de transaction importante aura des points de contrôle plus fréquents qu’une base de données utilisée principalement pour les opérations en lecture seule.

En outre, sous le modèle de récupération simple, un point de contrôle automatique est également mis en file d’attente si le journal devient plein à 70 %.

Sous le modèle de récupération simple, sauf si un facteur retarde la troncation du journal, un point de contrôle automatique tronque la section inutilisée du journal des transactions. En revanche, sous les modèles de récupération complète et journalisée en bloc, une fois qu’une chaîne de sauvegarde de journal a été établie, les points de contrôle automatiques n’entraînent pas la troncation du journal. Pour plus d’informations, consultez Journal des transactions (SQL Server).

Après un incident système, la durée nécessaire à la récupération d’une base de données donnée dépend en grande partie de la quantité d’E/S aléatoires nécessaires pour rétablir les pages qui étaient sales au moment de l’incident. Cela signifie que le recovery interval paramètre n’est pas fiable. Elle 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 considérablement et de manière assez imprévisible.

Impact de l’intervalle de récupération sur les performances de récupération

Pour un système OLTP (Online Transaction Processing) à l’aide de transactions courtes, recovery interval est le facteur principal 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 longue peut prendre beaucoup plus de temps que celle spécifiée dans l’option recovery interval . Par exemple, si une transaction longue a pris deux heures pour effectuer des mises à jour avant la désactivation de l’instance de serveur, 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 le journal des transactions (SQL Server).

En règle générale, les valeurs par défaut offrent des performances de récupération optimales. Toutefois, la modification de l’intervalle de récupération peut améliorer les performances dans les circonstances suivantes :

Si la récupération prend considérablement plus de 1 minute lorsque les transactions longues ne sont pas restaurées.
Si vous remarquez que les points de contrôle fréquents affectent les performances sur une base de données.

Si vous décidez d’augmenter le recovery interval paramètre, nous vous recommandons de l’augmenter progressivement par petits incréments et d’évaluer l’effet de chaque augmentation incrémentielle des performances de récupération. Cette approche est importante, car à mesure que le paramètre augmente, la recovery interval récupération de base de données prend plusieurs fois plus de temps. Par exemple, si vous modifiez recovery interval 10, la récupération prend environ 10 fois plus de temps que lorsque la valeur recovery interval zéro est définie.

Points de contrôle indirects

Les points de contrôle indirects, nouveaux dans SQL Server 2012, fournissent une alternative configurable au niveau de la base de données aux points de contrôle automatiques. En cas d’incident du système, les points de contrôle indirects fournissent un temps de récupération potentiellement plus rapide et plus prévisible 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 vérifient que le nombre de pages incorrectes est inférieur à un certain seuil afin que la récupération de la base de données se termine dans le délai 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 par opposition aux points de contrôle indirects qui utilisent le nombre de pages incorrectes. Lorsque 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 agressive les mémoires tampons sales sur le disque pour s’assurer que le temps nécessaire à l’exécution de la récupération se trouve dans le jeu de temps de récupération cible de la base de données. Cela peut entraîner une activité d’E/S supplémentaire sur certains systèmes qui peuvent contribuer à un goulot d’étranglement des performances si le sous-système de disque fonctionne au-dessus ou à proximité du seuil d’E/S.
Les points de contrôle indirects vous permettent de contrôler de manière fiable le temps de récupération de la base de données en factorisant le coût des E/S aléatoires pendant REDO. Cela permet à une instance de serveur de rester dans une limite supérieure des temps de récupération pour une base de données donnée (sauf lorsqu’une transaction de longue durée provoque des temps d’ANNULATION excessifs).
Les points de contrôle indirects réduisent les E/S liées au point de contrôle en écrivant continuellement des pages sales sur le disque en arrière-plan.

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

Points de contrôle internes

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

Les fichiers de base de données ont été ajoutés ou supprimés à l’aide de ALTER DATABASE.
Une sauvegarde de base de données est effectuée.
Un instantané de base de données est créé, que ce soit explicitement ou en interne pour DBCC CHECK.
Une activité nécessitant un arrêt de base de données est effectuée. Par exemple, AUTO_CLOSE est activé et la dernière connexion utilisateur à la base de données est fermée, ou une modification d’option de base de données est apportée qui nécessite un redémarrage de la base de données.
Une instance de SQL Server est arrêtée en arrêtant le service SQL Server (MSSQLSERVER). L’une ou l’autre action provoque un point de contrôle dans chaque base de données dans l’instance de SQL Server.
Mise hors connexion d’une instance de cluster de basculement SQL Server (FCI).