SQL Q & A: Réparer les dégâts
Jetez un coup d'œil à la sélection du plan de requête, aux sauvegardes trop lentes et au processus de réparation d'une base de données de réplication.
Paul S. Randal
Plan de votre requête
Q. Lorsque le SQL Server est de choisir un plan de requête pour exécuter mes requêtes, il prend en compte les données qui sont actuellement en mémoire ?
**R :**La réponse est simple considère que l'optimiseur de requête jamais que le buffer pool contenu lors du choix d'un plan de requête. L'optimiseur de requête évalue divers plans car il réduit les choix possibles. Il recherche le meilleur plan, qu'il peut déterminer dans un délai raisonnable. L'optimiseur de requête n'est pas toujours d'identifier le meilleur plan d'absolu. L'optimiseur de requête ne peut pas dépenser énormément de temps sur la compilation du plan, mais elle choisit toujours un plan « assez bon ».
L'optimiseur de requête doit connaître les diverses tables impliquées dans la requête. Il considère que ces informations dans les métadonnées relationnelle pour les tableaux. Les métadonnées relationnelle décrivent les colonnes de la table, index et contraintes, ainsi que statistiques sur la répartition de la valeur dans les différentes colonnes (si ces statistiques ont été créées). Les métadonnées de stockage décrivent comment la table et l'index sont en fait stockées dans les fichiers de données. L'optimiseur de requête n'utilise pas cette information pour établir un plan.
SQL Server ne garder trace des quelles parties d'une table et ses index est en mémoire à tout moment. Le pool de mémoires tampons suit les pages de fichier de données d'une base de données en mémoire. Toutefois, rien dans le SQL Server ne tout type d'agrégation automatisée. Il ne sera pas, par exemple, pouvoir déterminer que 50 pour cent d'indice 2 du tableau X est en mémoire, alors que seulement 5 % de l'indice trois du tableau X est en mémoire.
L'optimiseur de requête suppose que rien n'est en mémoire, il est donc probable à choisir un plan de requête qui implique la plus faible quantité d'e/s physiques. Plans avec beaucoup d'e/s physiques sont long et coûteux. Considérez une table avec deux index non-cluster qui pourrait satisfaire une requête SELECT. Le premier index a toutes les colonnes requises. Le second indice dispose de toutes les colonnes requises, ainsi que plusieurs colonnes supplémentaires.
Le premier index aura des lignes d'index plus petits, donc il aura plusieurs lignes d'index par page de fichier de données. L'optimiseur de requête choisit cet indice. Pour accéder aux lignes d'index nécessaires pour satisfaire que la requête, il faudra lire moins pages de fichier de données en mémoire à l'aide d'e/s physiques, comparé avec l'aide de l'indice de la deuxième. Le second a des dossiers plus grands et moins d'enregistrements par page de fichier de données. Ce raisonnement est appelé optimisation de coût, et c'est la façon dont le processeur de requêtes de SQL Server est conçu.
Toutefois, si une grande partie de l'indice de la deuxième, plus large est déjà en mémoire et aucun du premier indice est en mémoire ? La requête nécessite des e/s physiques de lire l'index choisi en mémoire. Ce sera beaucoup plus lent que l'utilisation de l'indice plus large déjà en mémoire. Dans ce cas, le plan de requête n'est effectivement pas optimale. Toutefois, l'optimiseur de requête n'a aucun moyen de savoir ce qui est en mémoire, et c'est seulement un exemple.
Même avec cela à l'esprit, cependant, que se passe-t-il si l'optimiseur de requête a reconnu ce qui était dans la mémoire et a généré un plan qui utilisé l'index moins efficace car il était déjà en mémoire ? Ce plan serait optimal tant que cette situation persiste. Si l'index plu efficace a été lu dans la mémoire pour une autre requête, la requête aurait alors un plan sous-optimal. Comment le plan pourrait être invalidé afin qu'il pourrait à nouveau être recompilé ?
Ingénieur logiciel ancien sur l'équipe de SQL Server , je sais que la complexité technique de garder une vue dont tables et les index sont en mémoire à l'aide en invalidation et sélection de plan de requête est extrêmement difficile. Cela ajouterait probablement une performance indésirable généraux pour seulement une prestation occasionnelle, et cela n'arrivera probablement jamais.
Si vous êtes intéressé à voir le pool de mémoires tampons, Découvrez la vue de gestion dynamique (DMV) de sys.dm_os_buffer_descriptors et les diverses requêtes, j'ai mis en place sur le section Pool de mémoires tampons de mon blog SQLskills.
Sauvegardes longues
Q. L'envoi de journaux nous permet de fournir une base de données secondaire pour générer des rapports. Chaque tellement souvent, nous courons dans un problème où appliquer une sauvegarde du journal à la base de données secondaire prend beaucoup plus longtemps que d'habitude. Avez-vous une idée ce qui pourrait provoquer cela ?
**R :**Oui, j'ai vu cette situation plusieurs fois. Si vous utilisez une base secondaire expédition de journaux pour avoir signalé, cela signifie que vous utilisez l'option WITH STANDBY Lorsque vous restaurez les sauvegardes du journal sur la base de données secondaire. Cela fonctionne en trois étapes :
- Écrire tous les enregistrements du journal de la sauvegarde dans le fichier journal de base de données.
- Effectuer la partie refaire de récupération (en s'assurant que toutes les opérations de transactions validées sont présentes dans la base de données).
- Effectuer la partie UNDO de la récupération (en s'assurant que toutes les opérations de transactions non validées ne sont pas présentes dans la base de données).
Étape 3 écrit tous les enregistrements de journal générés par les opérations d'annulation dans un fichier spécial appelé le fichier d'annulation. Cela signifie que la base de données est en mode lecture seule. C'est également la cohérence transactionnelle afin que les utilisateurs peuvent avoir accès. La raison des enregistrements de journal sont écrites dans le fichier d'annulation est donc le journal des transactions de la base de données n'est pas modifié d'aucune façon. Cela vous permet de restaurer des sauvegardes de journaux suivantes.
Lorsque le processus de restauration commence sur la base de données secondaire, s'il existe un fichier d'annulation, il y a une autre étape effectuée avant les trois premières étapes. Cette étape initiale prend tous les enregistrements du journal dans le fichier d'annulation et annule les effets sur eux. Cela met essentiellement la base de données de retour dans l'État dans lequel il se trouvait à la fin de l'étape 2. Cet base de données a le même État que si la sauvegarde de journal antérieure avait été restaurée à l'aide de WITH NORECOVERY, au lieu de WITH STANDBY.
Le problème que vous rencontrez est lorsqu'une sauvegarde de fichier journal en cours de restauration contient une transaction de longue durée qui n'est pas s'engager avant la fin de la sauvegarde du journal. Autrement dit, qu'il doit être complètement annulée dans le cadre de la restauration de la sauvegarde du journal. Il en résulte un fichier d'annulation grand, qui, en soi, peut faire la restauration d'un journal de sauvegarde prendre plus de temps. Si la sauvegarde de fichier journal en cours de restauration aussi a une transaction non validée, longues, puis c'est la tempête parfaite. Étape 3 aura également beaucoup de temps.
Cela peut se produire lorsque la base de données principale est en cours de maintenance des index et une sauvegarde du journal se termine vers la fin d'une opération de reconstruction d'index longues d'un index cluster grand. La restauration initiale de cette sauvegarde de journal sur la base de données secondaire est beaucoup plus longue que d'habitude pour terminer en raison de l'étape 3 du processus de restauration.
Aussi, la prochaine sauvegarde du journal sur la base de données principale se termine juste avant une reconstruction d'index. Lorsqu'il est restauré sur le secondaire, le fichier toute annulation doit être annulée à nouveau. Puis la restauration de journal se produit, et un autre fichier d'annulation grand est généré pour annuler la deuxième reconstruction d'index non validées.
Si la base de données secondaire requiert un accès 24 x 7 pour l'établissement de rapports, vous devez être au courant de cette possibilité. Dans ce cas, soigneusement augmenter les opérations de maintenance des index sur la base de données primaire avec les sauvegardes du journal. Cela garantira que seulement les reconstructions index complet et engagés sont présentes dans les sauvegardes du journal en cours de restauration sur la base de données secondaire.
Une alternative consiste à passer de la copie des journaux de la base de données mise en miroir. Ici, des enregistrements de journal sont continuellement envoyés du principal à la base de données miroir. Il n'ya pas des mesures supplémentaires impliquant l'annulation journal emploi plusieurs fois. Il y a un compromis complexité impliqués, comme l'inconvénient de cette approche est que rapports devront utiliser des captures instantanées de base de données.
Répliquer à réparer
Q. De temps en temps, nous nous retrouvons avec des fichiers corrompus. Nos sauvegardes finissent aussi par endommagé, alors il faut exécuter une opération de réparation. La semaine dernière, une des bases de données que je devais réparer était une base de données de publication de réplication. En ligne de livres de Microsoft, il est dit que tous les abonnés doivent être réinitialisés après la réparation d'une base de données de publication. Pouvez-vous nous expliquer pourquoi ?
**R :**Si vous envisagez l'utilisation de l'option REPAIR_ALLOW_DATA_LOSS de contrôle (DBCC) CHECKDB (je vais juste dire « réparer » d'ici) de cohérence de base de données, vous devez réfléchir à deux fois si la base de données que vous allez réparer est une base de données de publication de réplication. Si possible, utilisez vos sauvegardes au lieu d'exécuter la réparation.
Si vous utilisez la réplication de fusion, langage de manipulation de données (DML, Data Manipulation Language) déclenche la capture des modifications à la base de données de publication et les convertit en opérations logiques. Si vous utilisez la réplication transactionnelle, l'analyse de journal de transaction de base de données capture les modifications de base de données de publication. Ensuite les opérations journalisées, physiques sont converties en opérations logiques. Dans les deux cas, les opérations logiques sont ensuite appliquées aux bases de données réplication abonnement.
Aucun mécanisme permet la capture des opérations de réparation. Les opérations de réparation sont toujours des changements physiques directs pour les structures de base de données. Ceux-ci sont nécessaires pour corriger les incohérences dans les structures, telle qu'une page de base de données, un enregistrement de la table ou d'un lien entre deux pages. Ils sont différents de modifications de la base de données physique à cause des requêtes effectuant des insertions, des mises à jour ou des suppressions sur les tables.
Ces opérations ne peut pas traduire en opérations logiques que vous pouvez avez appliqué aux abonnés de réplication de la réparation. Il n'y a aucune opérations logiques que peut être exprimée à l'aide de Transact-SQL pour l'équivalent des changements structurels directs effectue la réparation. Imaginez qu'une opération de réparation est obligée de supprimer une page de données d'un index cluster unique (essentiellement en supprimant certains enregistrements de la table). L'abonnement n'est pas réinitialisé. Ces dossiers existeraient encore sur la copie répliquée du tableau.
Si une opération d'insertion ultérieure inséré des enregistrements avec des valeurs de clés cluster correspondant aux enregistrements supprimés par l'opération de réparation, l'Agent de Distribution échoue lorsque vous essayez d'appliquer les insertions à la copie répliquée. L'opération de réparation n'aurait pas été appliquée à la base de données d'abonnement, donc une erreur de violation de clé dupliquée se produit lorsque vous essayez d'appliquer l'insert à la table répliquée. Cela signifie que si une opération de réparation modifie une table qui fait partie d'une publication de réplication, l'abonnement de réplication n'est plus valide. Il devra être réinitialisé.
Si l'opération de réparation affecte toutes les tables de métadonnées de réplication, la publication de l'ensemble de la réplication est dans un état incohérent. La réplication doit être complètement enlevée et reconfigurée. Il s'agit de toute évidence d'un processus beaucoup plus envahissant que la réinitialisation d'un abonnement. L'essentiel est que, si possible, vous voulez éviter d'avoir à réparer une base de données de publication de réplication.
.jpg)
Paul S. Randal est le directeur général de SQLskills.com, directeur régional Microsoft et un SQL Server MVP. Il a travaillé sur l'équipe SQL Server du moteur de stockage chez Microsoft de 1999 à 2007. Il a écrit DBCC CHECKDB/réparation pour SQL Server 2005 et a été chargé par le moteur de stockage de base au cours du développement de SQL Server 2008. Randal est un expert en sinistre, de haute disponibilité et de maintenance de base de données et est une présentatrice régulière à des conférences dans le monde entier. Il blogs à SQLskills.com/blogs/paul et vous pouvez le retrouver sur Twitter à Twitter.com/PaulRandal.