Forum aux questions SQL : Réglage pour des performances optimales
Les index en double, les opérations de restauration annulées et les pics d'E/S peuvent provoquer des problèmes de performance, vous pouvez toutefois les contourner.
Paul S. Randal
Index en double
Q. Il semble que SQL Server permettez-moi de créer des index qui sont exactement les mêmes sur la même table. Comment aider ce ma performance de la charge de travail ? Différentes requêtes utilisera différentes copies de la même index ?
**R :**Il est regrettable que les index en double, permet à SQL Server comme ils ne fournissent aucun avantage que ce soit. En fait, les index en double peuvent nuire de bien des façons.
Un index en double se produit lorsque les clés d'index sont exactement identique à un autre indice, spécifié dans le même ordre et avec les mêmes spécifications de l'ASC ou DESC. Les colonnes incluses (le cas échéant) sont également les mêmes (même si les colonnes incluses peuvent être spécifiées dans n'importe quel ordre).
SQL Server n'utiliserons un des index en double pour aider avec les requêtes, mais il doit conserver tous les index sur une table pendant insérer, mettre à jour et supprimer des opérations. Cela signifie que chaque fois qu'il y a un insert ou delete sur la table, il doit se refléter dans tous les index. Le même est vrai pour les mises à jour, si les colonnes mises à jour font partie de l'indice.
Cette maintenance des index utilise des ressources supplémentaires et génère des enregistrements du journal des transactions supplémentaires — tous pour les index qui sont essentiellement inutiles. Ces index en double prendre plus d'espace sur le disque et de l'espace supplémentaire dans les sauvegardes — et les pages nécessaires à la maintenance des index prennent trop de plus d'espace dans la mémoire.
Index en double sont susceptibles de devenir fragmenté. Ils exigent également des ressources supplémentaires au cours de la fragmentation indice régulière. Le journal des transactions supplémentaires dossiers de maintenance des index et suppression de la fragmentation peut aussi conduire à réduire les performances des fonctionnalités de haute disponibilité (HA) comme la réplication transactionnelle et de mise en miroir de base de données.
SQL Server vous ne donne aucun avertissement que vous avez créé juste un index en double, donc c'est à vous d'éviter de le faire. Vérifier si vous avez déjà des indices en double, c'est pas une petite affaire. Il s'agit de script toutes les définitions d'index et comparer manuellement leur, ou une vaste programmation analyse les catalogues système. L'année dernière, Kimberly Tripp posté un solution complète à ce problème.
Méfiez-vous de la restauration
Q. J'ai eu récemment à annuler une mise à jour de longue durée. Après que l'opération restaurée, la prochaine sauvegarde du journal quotidien transaction était énorme. Je m'attendais à être très petit, car rien n'a changé dans la base de données. Pouvez-vous expliquer cette anomalie ?
**R :**C'est une idée fausse assez commune. Si vous revenir une importante opération, la sauvegarde différentielle suivant doit être petite, à droite ? Non.
N'importe quel moment, que SQL Server apporte une modification à la base de données, deux choses se produisent. Tout d'abord, il génère des enregistrements du journal des transactions qui décrivent le changement. Deuxièmement, pour toute données fichier pages modifiées par le changement, le bit correspondant est défini dans la bitmap différentielle. Cela signifie que ces pages doivent être sauvegardés par la sauvegarde différentielle suivante.
Lorsque vous revenir une opération, SQL Server doit annuler les modifications, l'opération faite. Cela signifie qu'il examine tous les enregistrements du journal des transactions générées par la partie avant de l'opération. Il doit annuler ces modifications dans l'ordre inverse. Chaque enregistrement de journal de transaction décrit un seul changement de la base de données dans le cadre de l'opération. Pour faire reculer ce changement, vous devrez faire un autre changement à la base de données, ce qui annule l'effet de la variation de l'originale. Par exemple, vous pourrait faire reculer une insertion record en supprimant le dossier. L'effet net est que le dossier n'existe pas.
Voici la partie plus confuse : chaque changement effectué au cours de la restauration est vraiment juste un autre changement de base de données (quoiqu'un spécial). Pour chaque modification de la base de données, il doit y avoir un enregistrement de journal de transaction. Ainsi même apportées pendant une restauration doivent être correctement consignées. Cela signifie qu'une restauration importante opération génère non seulement transaction journal des dossiers pour la partie avant de l'opération, mais aussi pour la restauration. Sauvegardes du journal des transactions sauvegardera tous ces enregistrements du journal des transactions, comptant pour la sauvegarde du journal des transactions importantes.
Lorsque la partie avant de l'opération provoque la bitmap différentielle de bits car certaines parties de la base de données ont changé, vous ne peut pas effacer les bits de la bitmap différentielle encore parce que la base de données a changé. Peu importe que le changement a été restauré par la suite. Les pages de fichier de données ont toujours été changés (deux fois, en fait) et donc doivent être soutenus par la sauvegarde différentielle.
Le noeud de la question est que, même lorsqu'une opération est annulée, la base de données est toujours changé. Toutes les sauvegardes doivent refléter ces changements.
À la recherche de pointes
Q. Je suis dépannage d'un problème où nous voyons des pointes de I/O périodiques de l'un de nos serveurs SQL. J'ai le rétrécie jusqu'aux points de contrôle à l'aide de PerfMon, mais je ne peux pas la base de données est le coupable majeur. Comment peux je creuser davantage ?
**R :**Il existe des points de contrôle pour deux raisons. Tout d'abord, ils à jour les pages de données fichier avec ce qui a été écrit pour le journal des transactions. SQL Server utilise un mécanisme appelé write-ahead logging, où les modifications de base de données sont décrits dans le journal des transactions avant d'être reflétée dans les fichiers de données. Cela garantit la durabilité des changements dans l'éventualité d'un accident. Deuxièmement, ils réduisent la quantité de charge constante de I/O écrivant uniquement les données modifiées pages fichier périodiquement, plutôt qu'après chaque changement à chaque page de fichier de données.
Points de contrôle se produisent séparément pour chaque base de données. Ils sont déclenchés basée sur un certain nombre de facteurs, y compris l'intervalle de récupération — c'est le serveur SQL estimer qu'assez journal des transactions a été généré depuis le dernier point de contrôle afin que leur recouvrement prendra environ une minute (par défaut).
Ce nombre correspond à la génération de plusieurs dizaines de milliers d'enregistrements du journal des transactions individuelles. Les pages de fichier de données plus modifiées par ces enregistrements du journal des transactions, plus la quantité d'e/S qui doit être effectué par des points de contrôle de base de données.
Vous pouvez suivre les points de contrôle en utilisant le compteur « Checkpoint pages/sec » dans SQL Server : Objet de performances Buffer Manager. Qui ne donne un nombre total sur toutes les bases de données sur l'instance de SQL Server. Pour déterminer la base de données est actuellement « interfaces » à tout moment, vous aurez besoin d'utiliser des indicateurs de trace.
Si vous activez les indicateurs de trace 3502 (trace écrite lorsqu'un point de contrôle se produit), 3504 (détails impression de trace sur le point de contrôle) et 3605 (laisser trace imprime pour aller dans le journal des erreurs), vous serez capable de déterminer la base de données est comptable pour les pointes de I/O en raison de points de contrôle.
Vous pouvez activer ces indicateurs de trace à l'aide de la commande :
DBCC TRACEON (3502, 3504, 3605, -1)
Les désactiver à l'aide de la commande :
DBCC TRACEOFF (3502, 3504, 3605, -1)
Points de contrôle ultérieurs produira sortie semblable au suivant dans le journal des erreurs :
2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 started (8) 2011-12-30 05:07:14.390 spid17s About to log Checkpoint begin. 2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 phase 1 ended (8) 2011-12-30 05:07:14.830 spid17s FlushCache: cleaned up 4307 bufs with 201 writes in 441 ms (avoided 23 new dirty bufs) 2011-12-30 05:07:14.830 spid17s average throughput: 76.30 MB/sec, I/O saturation: 198, context switches 392 2011-12-30 05:07:14.830 spid17s last target outstanding: 15, avgWriteLatency 2 2011-12-30 05:07:14.830 spid17s About to log Checkpoint end. 2011-12-30 05:07:14.830 spid17s Ckpt dbid 21 complete
Cela vous permet de voir la base de données est en cours interfaces et qui correspondent à l'information de PerfMon. Vous pouvez ensuite étudier pourquoi il est tellement données étant changées entre les points de contrôle, effectuer des plus fréquentes aux points de contrôle pour réduire le pic de I/O, ou augmenter la capacité du sous-système d'e/S.
Préoccupations de consolidation
Q. Mon entreprise a mis en place une nouvelle politique exigeant que nous renforcer autant que possible pour réduire les coûts de matériel. Je suis aussi poussé afin de réduire le nombre d'instances de SQL Server pour enregistrer les coûts de licence. Y a-t-il des lignes directrices sur la logique de combien de bases de données par instance de SQL Server ?
**R :**La réponse à cette question est un gros « cela dépend ». La liste des facteurs comprend la taille des bases de données, les types de charges de travail qu'ils sont en cours d'exécution, la volatilité des données, le type d'entretien régulier nécessaire et la reprise après sinistre et exigences HA.
Chaque instance de SQL Server possède une quantité finie de l'espace dans la mémoire pour stocker les pages de fichier de données à tout moment en cours de traitement (Ceci est connu comme le pool de mémoire tampon). Les bases de données plus vous avez sur une instance avec des charges de travail disparates tous nécessitant une transformation, la compétition plus il y aura parmi les charges de travail pour l'espace de pool de mémoire tampon.
Cela peut mener à la dissipation de la mémoire tampon de la piscine. Il y aura de roulement constant de faire de la place pour les nouvelles pages de fichier de données lues à partir du disque. Il y aura aussi des quantités importantes de lecture I/O avec les latences de lecture plus qu'acceptables. Tous ces facteurs dégradera les performances de la charge de travail.
Si les charges de travail différentes entraînent des modifications de la base de données, il y aura aussi écrire I/O de points de contrôle périodiques. Avec les nombreuses bases de données consolidées sur une instance unique, il pourrait y avoir plusieurs points de contrôle qui se produisent simultanément. Cela pourrait entraîner des latences d'écriture I/O — ralentir les opérations de contrôle et de contribuer davantage à la dégradation des performances de charge de travail.
Entretien régulier de la base de données devient aussi un problème avec un grand nombre de bases de données. Si chaque base de données requiert l'indice et maintenance statistiques, vérification de la cohérence et les sauvegardes, il peut être difficile de planifier toutes ces opérations pour toutes les bases de données afin qu'elles n'entrent en conflit avec l'autre et mettre la charge encore plus de I/O sur le serveur.
Les bases de données plus il sont sur une instance, il devient plus difficile d'utiliser les technologies HA natifs de SQL Server afin des pour protéger tous. Il est plus probable que vous aurez besoin d'une sorte de technologie de réplication niveau du sous-système I/O — même juste du point de vue de la facilité de gestion. Cela signifie que les dépense de capital supplémentaire qui pourraient compenser les économies de coûts de la consolidation de serveur.
La consolidation est un vaste sujet. Il rendre pleinement justice est au-delà de la portée d'une seule colonne. C'est assez de réflexion pour vous rendre prudent d'over-consolidating. Sur le revers, vous pouvez avoir plusieurs bases de données petites avec des charges de travail minimales que vous héberger sur une instance unique sans problèmes. Comme je l'ai dit avant, cela dépend.
.jpg)
Paul S. Randal est le directeur général de SQLskills.com, un directeur régional Microsoft et MVP SQL Server. Il a travaillé sur l'équipe du moteur de stockage SQL Server de Microsoft de 1999 à 2007. Il a écrit de réparation DBCC CHECKDB pour SQL Server 2005 et a été responsable pour le moteur de stockage de noyau au cours du 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 vous pouvez lui trouver sur Twitter à Twitter.com/PaulRandal.