Forum aux questions SQL : Maintenance des journaux et des index
Pour garantir une exécution efficace de SQL Server, vous pouvez notamment conserver des sauvegardes des journaux et maintenir correctement les index.
Paul s. Randal
Ne pas rompre la chaîne
Q : J'ai défini une stratégie de sauvegarde pour nos bases de données. Mon projet implique des sauvegardes du journal des transactions afin que nous pouvons effectuer la récupération d'urgence avec peu de perte de données. J'ai été étudie certains des problèmes que je peux rencontrer et j'ai lu à plusieurs reprises que je dois faire attention à ne pas rompre la chaîne de sauvegarde du journal. Pouvez-vous expliquer ce qu'il s'agit et comment il pourrait être rompu ?
R : C'est une question très et il est quelque chose que de nombreuses personnes négligent. La chaîne de sauvegarde de journal (appelée parfois simplement la chaîne du journal) fait référence à une série ininterrompue de sauvegardes du journal des transactions qui couvrent le temps à partir de la dernière sauvegarde de données (complète ou différentielle) et le point auquel vous voulez restaurer. Une séquence de restauration exemple serait la suivante :
- La dernière sauvegarde complète de la base de données
- Puis la dernière sauvegarde différentielle de base de données
- Puis toutes celles prises par la suite
La plupart des gens conservez des sauvegardes de journal autour de transaction plus au cas où un de la sauvegarde est endommagé et vous devez restaurer une sauvegarde des données moins récentes. Vous pouvez obtenir plus d'informations sur les sauvegardes et restaurations dans les deux articles de de TechNet Magazine de que j'ai écrit l'année dernière, “ Présentation de sauvegardes SQL Server ” et “ récupération d'urgence Using Backups . ”
Si une des sauvegardes du journal nécessaire sont endommagée ou pas disponible pour la séquence de restauration que vous avez choisi, la chaîne de sauvegarde du journal sera cassée et vous ne serez pas en mesure de restaurer au-delà de ce point. Si seul un des sauvegardes du journal est endommagé, il est possible de le forcer à le restaurer en utilisant l'option WITH CONTINUE_AFTER_ERROR. Qui forcerait une restauration d'enregistrements de journal des transactions est endommagé, entraînant une corruption de la base de données. Je serais très incertain sur forcer ce type de restauration.
Une seule opération qui risque d'une sauvegarde du journal nécessaire en cours non disponibles est une sauvegarde du journal “ de bande ”, sans s'assurer qu'une sauvegarde du journal est conservée. Vous pouvez procéder à une copie à un développeur, par exemple. Cette sauvegarde du journal fait partie de la chaîne de sauvegarde de journal, comme c'est la seule qui contient les enregistrements de journal générés depuis la dernière sauvegarde de journal.
Autrement dit, sauf si vous utilisez l'option WITH COPY_ONLY qui effectue la sauvegarde du journal, mais permet également de manière efficace la sauvegarde du journal suivant sauvegarde le même jeu d'enregistrements de journal. Consultez mon blog à la validation, “ BACKUP WITH COPY_ONLY, ” pour voir plus de détails sur la façon d'éviter le piratage de la chaîne de sauvegarde .
Un exemple plus commune d'une opération rompre la chaîne de sauvegarde du journal est une qui vous empêche d'effectuer une sauvegarde du journal des transactions au cours des opérations régulières. Ces types d'opérations sont les suivantes :
- Passer à la récupération SIMPLE du modèle, puis de nouveau à FULL ou BULK_LOGGED
- Immersion le journal dans SQL Server 2005 et les versions antérieures à l'aide de la … BACKUP LOG WITH NO_LOG ou TRUNCATE_ONLY options
- Rétablissement d'une base de données à partir d'une capture instantanée de base de données
Vous devrez peut-être effectuer une sauvegarde des données (complète ou différentielle) après toutes ces opérations pour permettre aux sauvegardes de journaux continuer. Il s'agit de redémarrage de la chaîne de sauvegarde du journal.
Une dernière chose : Contrairement à un mythe populaire, exécution d'une sauvegarde complète ou différentielle pas fractionne la chaîne de sauvegarde du journal et en fait, n'a aucun effet sur les sauvegardes du journal que ce soit.
Les index de cluster
Q : Nombre de tables dans notre base de données SQL Server 2008 Don ’t ont un index ordonné en clusters. J'ai entendu dire que je pourrais ont des problèmes de performances avec les enregistrements transférés à l'origine d'e/S supplémentaires. Pouvez-vous me dire comment je peux vérifier ceci, et ce que je peux le faire à ce sujet ?
R : Un segment de mémoire est une table qui n'a pas d'un index ordonné en clusters. Il est par nature sans classement. Pour les lecteurs qui Don ’t savoir sur les enregistrements transférés dans des segments de mémoire et comment ils sont utilisés, consultez mon billet de blog, “ transfert et les enregistrements transférés et la taille du pointeur de sauvegarde , ” pour plus d'informations. Enregistrements transférés dans des segments de mémoire peuvent entraîner des opérations d'e/S aléatoires supplémentaires au cours de traitement de requête, qui à son tour entraîne une baisse des performances.
Pour vérifier si vous utilisez des requêtes de traitement sont transmis les enregistrements, la plus simple consiste à examiner le compteur de performance d'enregistrements transmis/s dans l'objet de performance de méthodes d'accès. Puis utilisez la fonction de gestion dynamique sys.dm_db_index_physical_stats avec le mode détaillée sur certaines tables dans la base de données, et il renvoie le nombre d'enregistrements transférés pour chaque table dans la colonne forwarded_record_count de la sortie. Voir la rubrique cette rubrique dans la documentation en ligne pour plus d'informations.
La pire façon de supprimer des enregistrements transférés consiste à créer un index ordonné en clusters et faites-le glisser à nouveau. Ainsi, tous les index non en cluster sur la table d'être reconstruit automatiquement deux fois, ce qui constitue une perte considérable de ressources. Consultez mon blog publier pour plus d'informations : “Que se passe-t-il pour les index non ordonné en clusters lors de la modification de la structure des tables?”
Pour définitivement supprimer et empêcher des enregistrements transférés dans des segments de mémoire, la plus simple consiste à créer des index ordonnés en clusters. Je ne veux pas obtenir dans le rapport à un index ordonné en clusters “. tas ” débat ici sur pourquoi vous devez avoir index ordonnés en clusters dans la plupart des cas au lieu de segments de mémoire. Reportez-vous à “ de la clé de clustering ” blog du ma femme Kimberly Tripp publier série sur cela pour plus d'informations. Je vous encourage à évaluer l'utilisation d'index ordonné en clusters.
Lorsque les enregistrements de la table augmentent la taille, cela peut entraîner des enregistrements transférés lorsque l'espace est insuffisant. Un autre moyen d'empêcher transféré les enregistrements, est par conséquent, pour éviter que les enregistrements à partir de la modification de taille. Cela peut signifier, par exemple, utiliser les valeurs par défaut pour les colonnes de longueur variable.
Dans SQL Server 2008, il existe une nouvelle instruction ALTER REBUILD de … TABLE vous permet de reconstruire les segments de mémoire. Cela fonctionne de la même façon que l'instruction ALTER INDEX … REBUILD vous permet de reconstruire des index. Microsoft a ajouté cette instruction pour prendre en charge la fonctionnalité de compression de données, mais il fonctionnera pour nos besoins. Voir la rubrique cette rubrique dans la documentation en ligne pour plus d'informations.
Gestion des index
Q : J'ai changé nos routines de maintenance des index à utiliser les reconstructions d'index en ligne, mais j'aperçois toujours des problèmes de blocage parfois lors de l'exécutent des routines de maintenance. Pourquoi ? J'ai pensé que les opérations d'indexation en ligne Don ’t utiliser des verrous, afin que je shouldn't voir tout blocage. Est-ce comportement attendu ou si je fais un problème ?
R : Vous voyez le comportement attendu. Il existe un verrou de table partagé requis au début de l'opération pendant l'opération d'initialisation (un processus très rapide). Il est supprimé immédiatement. Ce verrou doit être mis en attente comme n'importe quel autre type de verrou et qu'il empêche les nouvelles requêtes d'apporter des modifications à la table jusqu'à ce que vous pouvez accorder et libérer le verrou à nouveau.
Impossible d'acquérir ce verrou jusqu'à ce que vous avez terminé toutes les requêtes de modification en cours d'exécution. Cette opération peut prendre un certain temps, en fonction de votre charge de travail. Cela signifie que le blocage peut se produire au début d'une opération d'index en ligne.
À la fin de l'opération, vous devez prendre un verrou de modification du schéma : Considérez ceci comme un verrou exclusif, afin qu'il puisse terminer. Dans ce cas également extrêmement rapide. Ensuite vous la déposerez immédiatement. Ce verrou empêche n'importe quel type de nouvelles requêtes sur la table (en lecture ou écriture) jusqu'à ce que vous octroi et la libération du verrou.
Une fois encore, vous ne pouvez pas acquérir ce verrou jusqu'à ce que SQL est terminée, tout en cours d'exécution en lecture ou écrire des requêtes. Cela signifie à nouveau qu'est la possibilité de blocage.
Pour résumer, bien que le nom de la fonctionnalité d'opérations d'indexation en ligne, elle toujours nécessite deux verrous à court terme qui peuvent provoquer des problèmes de blocage. Le gain sur les opérations d'index en mode hors connexion classique est que pour la grande majorité de l'opération d'index, il n'y a aucuns verrous détenus, et donc globalement simultanéité est augmentée. Le livre blanc “ Operations d'indexation en ligne de SQL Server 2005, ” a beaucoup plus de détails sur le fonctionnement de ces opérations.
Réduction des temps de gestion des index
Q : J'ai hérité certains systèmes où les tâches de maintenance régulière des index prennent beaucoup de temps pour exécuter et générer beaucoup d'e/S, mais je n'exécutent pas les reconstructions d'index dans la mesure où les index ne sont pas lors de l'obtention de fragmentation. J'aimerais réduire le travail en cours de réalisation, que je n'obtiens pas tout gain de performances. Pouvez vous recommander une stratégie pour vous aider ?
R : Il s'agit d'un problème relativement courant. Il provient de la façon dont les tâches de maintenance des index déterminent les index à reconstruire ou réorganiser.
La plupart des gens à exécuter la fonction de gestion dynamique sys.dm_db_index_physical_stats (mentionnée précédemment) sur tous les index de la base de données, puis choisissez si vous souhaitez reconstruire, réorganiser ou ne rien faire. Ils basent cette décision sur l'avg_fragmentation_in_percent, le page_count et les valeurs avg_page_space_used_in_percent à l'aide d'une clause WHERE dans la sortie.
Le problème est que la fragmentation d'index n'est pas stockée en mémoire telles que d'autres statistiques. Cette fonction doit lire et traiter chaque index afin de déterminer l'étendue de la fragmentation. Si la plupart des index dans la base de données statiques ou les modifier très lentement (en termes de la fragmentation), puis qu'ils ne sont pas être reconstruits ou réorganisées. Vérification de leur fragmentation chaque fois que vous exécutez un travail de maintenance des index est essentiellement une perte de temps.
Vues de gestion plus dynamiques prennent en charge “ prédicat push-down, ” où les données traitées sont que qui correspond au prédicat de clause WHERE. Toutefois, sys.dm_db_index_physical_stats est une fonction, pas un affichage, afin qu'il ne peut pas faire cela. Cela signifie que vous devez filtrer manuellement et demander seulement la fonction pour traiter ces index que sont susceptibles d'être fragmentés et devrez reconstruire ou réorganiser.
Je vous recommande de surveillance de la fragmentation au cours des quelques semaines. Ainsi que vous faire une idée dont les index sont judicieux de vérifier la fragmentation, plutôt que de vérifier tous les éléments. Une fois cette liste d'index, créer une table portant le nom de table, le seuil de la fragmentation et le nom d'index de prise de mesures. Vous pouvez constater que certains index peut avoir plus de la fragmentation avant d'affecter les performances que d'autres. Il s'agit “ table pilote ” vous ensuite utiliser pour la tâche de maintenance d'index du lecteur. Devant être une boucle sur tous les index décrits par la table et exécutez uniquement la fonction sys.dm_db_index_physical_stats sur les.
J'ai implémenté ceci pour plusieurs clients. Dans certains cas, il a réduit le runtime de la tâche de maintenance des index à partir des heures à 15 minutes ou moins. C'est simplement à partir des s'exécute ne pas cette fonction sur index statiques. Vous pouvez également revenir à une étape supplémentaire et effectuer le suivi de la fréquence à laquelle un index est reconstruit et potentiellement modifier FILLFACTOR de l'index automatiquement, nous espérons qu'entraînant une réduction pouvant aller au travail effectué par la tâche de maintenance des index.
Pour plus d'informations sur les diverses méthodes de l'exécution de la maintenance des index, voir mon billet de blog, les “ importance de la maintenance des indexde ” et pour une explication détaillée de ce qui se passe en coulisses de la fonction, consultez également mon blog publier, “ Inside sys.dm_db_index_physical_stats, . ”
Grâce à Kimberly l. Tripp de SQLskills.com pour son étude technique de mois-ci ce.
.jpg)
Paul s. Randal est le directeur général de SQLskills.com, un directeur régional Microsoft et SQL Server MVP. Il a travaillé dans l'équipe SQL Server Storage Engine chez Microsoft depuis 1999 à 2007. Il a écrit DBCC CHECKDB/réparation pour SQL Server 2005 et était responsable du Core Storage Engine pendant le développement de SQL Server 2008. Expert de la récupération d'urgence, de la haute disponibilité et de la maintenance de base de données, il anime fréquemment des conférences. Il blogs à SQLskills.com/blogs/paul et que vous en trouverez lui sur Twitter à Twitter.com/PaulRandal.