SQL Q & A: La taille a finalement son importance
La taille des bases de données, la fragmentation des index et la disponibilité post-basculement comptent parmi les problèmes que suivent de près les administrateurs SQL ce mois-ci.
Paul S. Randal
La Fragmentation de la peur
Q. J'ai lu quelques articles du blog qui semblent impliquer nous est inutile de s'inquiéter de la fragmentation des index si nos bases de données sont hébergées sur la mémorisation à semi-conducteurs — la théorie étant que Solid-State drives (SSD) sont donc beaucoup plus rapides que la rotation des disques. Je comprends la dégradation des performances serait réduite, mais vraiment juste complètement ignorer la fragmentation index ?
**R :**Si vous utilisez des disques de filature ou de .ssds., vous devez faire attention à la fragmentation de l'indice. Fragmentation de l'index comprend deux phénomènes — pages d'index hors de l'ordre et les questions de la page-densité. L'ancien empêche lecture efficace à venir au cours de l'indice-gamme balayages et ce dernier réduit la densité des données.
Il est vrai que les latences de lecture/écriture avec SSDs sont très petits. En conséquence, la nécessité d'effectuer les plus fréquentes, plus petites read-ahead e/S lors de la gamme balayage un index fragmenté n'aura pas autant d'un effet de performance comme dans la même situation proche lors de l'utilisation de disques de la filature.
Cependant, la réduction de la densité de données de la fragmentation de l'indice peut être encore un gros problème. La plupart indice fragmentation se produit par une opération appelée un « split page. » Cela se produit lorsque l'espace libre est créé sur une page dans un index en déplaçant la moitié les lignes de l'indice à une nouvelle page. Cela laisse les anciennes et les nouvelles pages avec un espace vide à environ 50 p. 100. Avec un index fortement fragmenté, il n'est pas rare de voir des index avec une densité moyenne de page de 70 % ou moins (avec 30 % d'espace libre).
Dans cet esprit, si un grand nombre d'indices dans les bases de données stockées sur votre SSDs ont page faible densité, cela signifie que votre chers SSDs pourraient être stocker une grande quantité d'espace vide. Ce n'est clairement pas une situation optimale. Aussi, même si les e/S supplémentaire nécessaire pour lire les pages de faible densité aura latences faibles sur le SSD, ils vous prendre plus d'espace dans le pool de mémoire tampon de SQL Server (le cache de la page en mémoire-fichier de données). Cela signifie aussi que la mémoire de votre précieux serveur n'est pas utilisée optimale.
L'autre chose à considérer en dehors de la fragmentation de l'indice elle-même est la cause de la fragmentation : page se divise. Il s'agit d'opérations dispendieuses qui génèrent beaucoup d'enregistrements du journal des transactions (regardez ma blog post de voir à quel point il peut être). Ces enregistrements de journal supplémentaire signifient un traitement supplémentaire par tout ce qui se lit le journal des transactions (telles que la réplication transactionnelle, sauvegardes, base de données mise en miroir, ouvrir une expédition). Cela peut entraîner une dégradation des performances pour ces mêmes processus. Donc n'ignorez pas la fragmentation indice juste parce que vous utilisez SSDs.
Ne comptez pas sur le miroir
Q. Nous sommes refonte de notre stratégie de disponibilité, mais ont devenir coincés sur la façon de rendre les abonnement bases de données d'un couple de réplication transactionnelle plus hautement disponible. Nous ne pouvions pas utiliser de base de données en miroir sur SQL Server 2005 parce que nous aurions à réinitialiser l'abonnement après un basculement. Y a-t-il une meilleure solution maintenant que nous sommes sur SQL Server 2008 R2 ?
**R :**Vous avez raison en disant que mise en miroir d'une base de données d'abonnement sur SQL Server 2005 ne fournit qu'une copie de redondance des données qui ont déjà été mis en miroir à la copie miroir de la base de données d'abonnement. Il n'y a aucun moyen de recréer l'abonnement de réplication sans une réinitialisation complète. Évidemment, cela fait un mauvais choix sur SQL Server 2005 de mise en miroir de base de données.
Avec SQL Server 2008, il y avait un nouveau mécanisme introduit dans la réplication transactionnelle permettant une réinitialisation partielle d'un abonnement. L'option est appelée « initialiser de lsn. » Il est précisé que le paramètre @ sync_type lors de l'appel de sp_addsubscription.
La réplication transactionnelle de peer-to-peer dans SQL Server 2008 a été améliorée pour vous permettre d'ajouter et de supprimer des nœuds dans une topologie de peer-to-peer sans avoir à faire toute l'activité de la topologie complètement paisibles tout d'abord. Il s'agit d'un important coup de pouce à la topologie de peer-to-peer de disponibilité données fournit. L'option « initialize de lsn » a été ajoutée que ces améliorations étaient en place.
Pour la mise en miroir de base de données, il n'y a aucune charge supplémentaire pour la mise en miroir de bases de données de souscription (comme il est dans l'Agent de lecture du journal pour la mise en miroir de bases de données de publication). Cependant, vous pouvez utiliser la méthode « initialize de lsn » pour fournir une réinitialisation rapide d'un abonnement après un basculement de mise en miroir.
Cette méthodologie s'appuie sur la détermination du numéro de séquence de journal (LSN — un numéro unique identifiant un enregistrement du journal transaction) de la plus récente répliquées opération appliquée à la base de données d'abonnement avant le basculement de la mise en miroir. Nous appellerons ce LSN2.
Certaines de ces opérations seront ont également été reflétés à la copie miroir de la base de données avant le basculement sur incident s'est produit. Cela pourrait monter à LSN3, par exemple, un peu plus loin dos en temps que LSN2. Il y aura aussi certaines opérations qui n'ont pas été appliquées à la base de données d'abonnement du tout. Ceux-ci sont plus récents en temps que LSN2 ou LSN3. Nous appellerons ces LSN1.
Toutes les opérations jusqu'à LSN2 ont été appliquées à la base de données d'abonnement principal. Toutes les opérations jusqu'à LSN3 ont été appliquées à la base de données d'abonnement principal et miroir de la base de données miroir abonnement. Pour effectuer une initialisation « initialize de lsn » d'un nouvel abonnement après un basculement de mise en miroir, l'appel à sp_addsubscription doit utiliser LSN3 comme point de départ.
La rétention de distribution période doit également être définie pour les opérations sont conservées dans la base de données de distribution pour quelque temps après que qu'ils ont été appliquées à la base de données d'abonnement. En bref, vous pouvez maintenant utiliser la mise en miroir de base de données afin d'augmenter la disponibilité d'une base de données d'abonnement avec seulement une réinitialisation minimale requise après un basculement de mise en miroir. Pour obtenir une explication approfondie de plus en plus à ce sujet, téléchargez le livre blanc, "réplication SQL Server : Fournissant la mise en miroir de base de données haute disponibilité à l'aide de. "
Trop gros pour poignée
Q. Notre base de données principale a atteint presque 9TB. Nous constatons que nous n'avons tout simplement pas la capacité d'effectuer des tâches de maintenance régulière sans affecter sérieusement notre charge de travail régulier. Nous sommes plus inquiète à l'idée de pouvoir sauvegarder la base de données pour permettre la reprise après sinistre. Avez-vous des conseils ?
**R :**Il s'agit d'un cas où scission la base de données en morceaux plus gérable serait les bénéfique. Vous pouvez le faire dans un certain nombre de façons, la plus courante qui utilise la table/index SQL Server partitionnement fonctionnalité (Enterprise Edition) ou split manuellement les choses dans des tables distinctes.
Dans les deux cas, le point crucial est de créer plusieurs groupes de fichiers dans la base de données. Avec le partitionnement, chaque partition des tables et index plus importants réside dans un groupe de fichiers distinct. Avec fractionnement manuelle, chaque grande table réside dans un groupe de fichiers distinct (éventuellement avec tous ses index ainsi).
À l'aide de groupes de fichiers distincts, vous avez des unités plus granulaires de la base de données que vous pouvez sauvegarder et restaurer. Vous n'aurez pas à exploiter sur le 9TB ensemble chaque fois. Si vous aviez une base de données sales, par exemple, avec les données de 2012 à 2008, vous pourrait partitionner les différents tableaux par plage de données dans une partition année civile. Chaque partition année serait dans un groupe de fichiers distinct.
Avec uniquement les 2012 un groupe de fichiers qui subit une modifications, vous peut sauvegarder il fréquemment. Vous peut sauvegarder les autres groupes de fichiers immuable beaucoup moins souvent. Cela permet d'économiser sur l'espace de stockage de sauvegarde et de la quantité de temps que l'e/extra s frais généraux d'effectuer la sauvegarde est engagée sur le système de production.
Avec un tel arrangement, de reprise après sinistre devient également plus rapide (en utilisant l'Enterprise Edition). Vous devez seulement restaurer rapidement les groupes de fichiers nécessaires pour rendre la portion de traitement de transactions en ligne (OLTP) de la charge de travail en ligne. Vous pouvez le faire avec une restauration partielle, puis utiliser la disponibilité de la base de données partielle d'apporter de la base de données en ligne. Vous pouvez restaurer les groupes de fichiers contenant les données plus anciennes ultérieurement à l'aide de restauration fragmentaire en ligne, tandis que l'activité OLTP survient dans les groupes de fichiers déjà en ligne.
Vous pouvez lire que davantage sur cette approche dans ces livres blancs :
- “Partitionné Tables et index dans SQL Server 2005"
- “Disponibilité partielle de base de données"
- “Partitionné Table et stratégies d'Index à l'aide de SQL Server 2008"
Sous la pression
Q. Une chose qui confond notre équipe DBA est comment dire si le pool de mémoire tampon est actuellement sous la pression. Il y a beaucoup d'informations contradictoires sur les compteurs PerfMon et ce que les seuils d'utiliser. La plupart de ce que j'ai lu dit d'utiliser la Page de l'espérance de vie (PLE) et 300 comme un seuil. Vous pouvez faire la lumière sur ce ?
**R :**Vous n'êtes pas seul dans votre confusion. Tout d'abord, le nombre de 300 a été référencé dans un livre blanc de Microsoft publié il y a cinq ans. C'est maintenant désuet mal.
PLE est le compteur de droite à utiliser, mais vous devez comprendre ce que cela signifie et au moment d'être concernés. Ce nombre fournit une mesure instantanée de comment agressivement le pool de mémoire tampon est faire de l'espace pour les pages de fichier de données requis être lues depuis le disque dans la mémoire. Il n'est pas une moyenne mobile. C'est le moment prévu en secondes, pendant lequel une page lire à partir du disque sera sursis en mémoire avant que vous avez besoin débusquer il donc une autre page peut prendre sa place.
Ainsi, en regardant une seule valeur PLE ne vous dire beaucoup. Vous devez vérifier les tendances de la valeur. Il est tout à fait possible pour les opérations de SQL Server valides provoquer la LEP chuter drastiquement. Il sera souvent puis recouvrer son ancienne valeur. Si la LEP gouttes et reste faible, c'est une source de préoccupation.
Quand s'inquiéter le seuil n'est pas une valeur fixe, comme beaucoup de gens décrire. Les moyens de la 300 valeur du pool de mémoire tampon entière sont remplacé toutes les 300 secondes. Par exemple, si vous avez un pool de mémoire tampon de 100 Go, cela signifie que 100 Go de données nouvelles est lu dans la mémoire toutes les cinq minutes. C'est manifestement un problème de performance. Cependant, il devient un moyen de question performance massive avant PLE hits 300. Vous pouvez calculer une valeur de plus raisonnable en utilisant (mémoire du pool de mémoire tampon en GB / 4) * 300, comme expliqué dans ce billet de blog.
Vous devez également être au courant de la configuration de non-uniform memory access (NUMA) de votre serveur. Le compteur de la LEP dans l'objet de performance du gestionnaire de mémoire tampon est en fait la moyenne des PLEs pour chaque nœud NUMA, si vous avez configuré de NUMA. Cela signifie que la LEP de gérer les tampons de surveillance n'est pas un vrai indicateur de la pression de mémoire sur le serveur. Dans ce cas, vous devez mesurer le compteur PLE dans chacun des objets de performance de Partition de la mémoire tampon. Vous pouvez lire plus sur la LEP et NUMA dans ce billet de blog.
PLE est le compteur de droite pour surveiller, mais vous devriez seulement être concerné si la valeur diminue significativement au-dessous de la normale et y reste pendant une longue période. C'est une ligne directrice générale, mais malheureusement, il n'y a aucun détail qui s'appliquent à toutes les situations.
.jpg)
**Paul S. Randal**est directeur général de le SQLskills.com, un directeur régional de Microsoft et MVP SQL Server. Il a travaillé sur l'équipe de moteur de stockage SQL Server de Microsoft de 1999 à 2007. Il a écrit de DBCC CHECKDB/réparation pour SQL Server 2005 et a été responsable pour le moteur de stockage de base au cours du développement de SQL Server 2008. Randal est un expert sur la reprise après sinistre, la haute disponibilité et la maintenance de base de données et une présentatrice régulière lors de conférences dans le monde entier. Il blogs à SQLskills.com/blogs/paul, et vous pouvez lui trouver sur Twitter à twitter.com/PaulRandal.