Partager via

  • Article

SQL Q & A Suppression de fragmentation d'index, synchronisation et de synchronisation et bien plus encore

Paul S. Randal

QJe suis sais pas comment supprimer la fragmentation d'index affecte des statistiques. J'ai entendu que parfois, je doit recréer Mes statistiques après la recréation d'index et parfois, je ne devrait pas, et que la reconstruction d'un index organisé en clusters affectera tous les autres index trop. Pouvez vous shed certains voyant cette please que je souhaitez vous assurer que je ne suis pas par inadvertance rendre pire performances ?

AC'est certainement un problème qui provoque beaucoup de confusion, mais vous sont droite qu'une stratégie de maintenance de base de données complète inclut suppression fragmentation d'index et la mise à jour des statistiques. Je vais pas dans les détails d'exactement quand et pourquoi supprimer la fragmentation d'index, pour cela, consultez l'article 2008 août » Pourboires supérieure pour la maintenance de base de données réelle." Au lieu de cela, je suppose que vous savez qui index pour manipuler et J'AI simplement pencherai sur comment les choses fonctionnent.

La première partie de la confusion est autour la suppression fragmentation opérations affectent les statistiques. Reconstruction d'un index (à l'aide de ALTER INDEX … RECONSTRUIRE, DBCC DBREINDEX et CREATE INDEX … WITH DROP_EXISTING) met à jour statistiques avec l'équivalent d'une analyse complète, mais à réorganiser un index (à l'aide ALTER INDEX … REORGANIZE ou DBCC INDEXDEFRAG) ne pas jour statistiques, même si les deux supprimer fragmentation d'index.

La raison que recréation mettre à jour les statistiques et réorganiser n'a aux algorithmes utilisés pour ces opérations. Une opération de reconstruction d'index dispose d'une vue complète de l'index et par conséquent peut mettre à jour les statistiques correctement. Une opération réorganiser, cependant, seulement fonctionne sur quelques pages de l'index à la fois et ne peut pas mettre correctement à jour les statistiques de l'index entier.

La deuxième partie de la confusion est sur les statistiques sont mises à jour lorsqu'un index est reconstruit. Il existe deux types généraux de statistiques pour une table — celles des colonnes indexées de la table et celles pour les colonnes non indexés. Une opération de reconstruction index met à jour uniquement les statistiques de l'index est recréé. Les statistiques pour les colonnes non indexés doivent être mises à jour manuellement dans le plan de maintenance. En outre, comme mentionné dans l'article « haut conseils pour effet de base de données Gestion », vous devez être veillant à ne pas mettre à jour manuellement statistiques relatives à l'index après la recréation de l'index, la mise à jour manuelle peut utiliser un taux d'exemple d'inférieur à 100 %, tandis que la reconstruction d'index utilisera l'équivalent d'un échantillon (100 %) analyse complète. En d'autres termes, cela pourrait entraîner écraser une statistique analyse complète avec une statistique échantillonnée.

La dernière partie de la confusion est autour d'effet de reconstruction d'index dispose sur autre index. La réponse est toujours que reconstruction d'index affecte uniquement cet index particulier et sa statistiques. L'exception est d'un index cluster non unique dans SQL Server 2000, où reconstruction d'un tel index provoque également tous les index non clusterisé sur la table afin d'être reconstruit. Cela a été résolu à partir de SQL Server 2005. Vous trouverez plus d'informations sur ce point dans mon billet de blog à" Index de chaque angle."

Pour résumer, votre gestion des index et les statistiques doivent effectuer les opérations suivantes :

  • Reconstruire ou réorganiser vos index à supprimer de fragmentation
  • Mettre à jour statistiques d'index pour ces index ne était pas recréé
  • Mettre à jour les index non statistiques

QJ'ai tester avec SQL Server 2008 et j'ai trouvé un comportement étrange. Il semble que si j'activer la compression des données dans la base de données de production et puis sauvegarder la base de données et essayez de restaurer sur une instance de Standard Edition, la restauration échoue ! Est-ce comportement attendu ? Si tel est le cas, est ce limité pour la compression de données ou sont affectées d'autres fonctionnalités ? Et pourquoi ne pas la restauration échouent immédiatement au lieu de seeming pour effectuer la restauration entier avant d'échec ?

ALe comportement que vous vous voyez est voulu par la conception même du produit. Plus « Enterprise-uniquement » fonctionnalités simplement limite les éditions serveur SQL à laquelle vous pouvez apporter utiliser la fonctionnalité pour Enterprise Edition, Enterprise Evaluation Edition et Édition Développeur. Il existe certaines fonctionnalités, cependant, que également limite les versions de serveur SQL peut restaurer une sauvegarde d'une base de données contenant la fonction. Vous avez trouvé une des fonctionnalités SQL Server 2008 ayant cette restriction, compression de données.

Le comportement est en fait pas nouvelle pour SQL Server 2008. Dans SQL Server 2005, si une base de données contient les tables partitionnées ou un index (explicitement en utilisant la fonction de partitionnement), ensuite une copie de sauvegarde de cette base de données peut uniquement être récupéré dans la liste à l'aide d'une Édition simplement mentionné. Il existe deux problèmes, cependant, dans SQL Server 2005. Tout d'abord, il peut être difficile de savoir si il est de partitionnement d'une base de données et ensuite, la restauration n'est pas échouer jusqu'à ce que juste avant qu'elle serait ont terminée.

Cela signifie que vous ne pas savoir que votre restauration va échouer jusqu'à ce que vous avez attendu par l'opération de restauration (potentiellement longue) entière. C'est parce que la base de données n'est pas cohérente de façon transactionnelle que la partie restauration récupération soit terminée, et pour une opération au cours de la récupération peut ajouter ou supprimer le partitionnement. Rencontré cette situation peut être très ennuyeux dans une situation de récupération après incident s'où le seul serveur accéder rapidement la base de données restaurée trouve être Standard Edition.

Dans SQL Server 2008, la liste des "Enterprise-uniquement" fonctionnalités avec ce comportement a atteint 4: compression de données, modifier automatisée chiffrement des données transparent et partitionnement. Cela signifie que plusieurs personnes allez rencontrer ce problème. Pour cette raison, un nouveau DMV a été ajouté, sys.dm_db_persisted_sku_features, qui permet à un administrateur de base pour facilement vérifier si une de ces fonctionnalités sont activée dans une base de données.

Par exemple, dans une base de données dans laquelle une table a été compression des données activée, exécute la DMV donnera les résultats suivants :

SELECT * FROM sys.dm_db_persisted_sku_features;
GO
feature_name    feature_id
--------------  -----------
Compression     100

Toutefois, SQL Server 2008 ne résout pas le problème avec la restauration opération devoir presque terminée avant que l'administrateur de base de données est informé que la base de données ne peut pas réellement être restaurée. En fait, ce problème est probablement être résolus donné la nature du fonctionne de restauration, consultez la Colonne de questions et réponses SQL du problème 2008 octobre Pour plus d'informations.

Si il est possible qu'une base de données doit être restauré dans Standard Edition (ou une version inférieure), il est impératif que l'administrateur de base de données empêche ces fonctionnalités ou utilise le DMV régulièrement pour éviter une surprise nocif lorsque vous tentez de restaurer dans une situation critique.

QJ'ai implémenté la mise en miroir de base de données synchrone et était sous l'impression cela supposait que la base de données miroir est toujours synchronisée avec la base de données principale. Cependant, j'ai parfois vu l'état du miroir devient SYNCHRONIZING au lieu de SYNCHRONIZED, ainsi que lorsque nous avons tout d'abord configuré un miroir. Pourquoi cela se produit-elle ?

AAvant de je répondre à la question, je vais définir la base de données SYNCHRONIZED et SYNCHRONIZING que la mise en miroir des états. En bref, la mise en miroir de base de données fonctionne en envoyant en permanence des enregistrements du journal transaction physique entre l'instance SQL Server hébergeant la base de données principale et l'instance qui héberge la base de données miroir. S'il n'y a aucun enregistrement de journal de transaction en attente de l'entité à envoyer au miroir, l'état en miroir est SYNCHRONISÉE (dans d'autres termes, les deux bases de données sont synchronisés). S'il existe transaction enregistrements du journal qui n'ont pas encore été envoyées sur le miroir (la base de données miroir est « derrière » le principal), l'état de mise en miroir est SYNCHRONIZING.

La méthode pour initialiser un miroir de base de données doit prendre une sauvegarde complète de la base de données, ainsi qu'au moins un journal des transactions sauvegarde et restaurer (à l'aide avec NO_RECOVERY) sur le miroir. Ensuite lorsque la mise en miroir est activé, l'état en miroir est synchronisation initiale. Cela est dû au fait que comporte peut-être des activités de transactions sur le principal depuis la dernière sauvegarde de journal qui a été restaurée sur le miroir. Ceci est très probablement d'une base de données avec une charge constante.

L'astuce pour réduire le temps dans laquelle la mise en miroir reste état la synchronisation essayez de rendre la base de données miroir aussi à jour que possible connecter les sauvegardes. Toutefois, pour une base de données avec une charge constante, cela peut être très difficile et l'état en miroir est initialement donc SYNCHRONIZING pendant un certain temps. Une fois que le miroir intercepte les, l'état en miroir devient SYNCHRONIZED.

Après ce point, il est possible de la base de données miroir à nouveau se trouvent derrière le principal dans lequel les cas l'état en miroir est déposez retour pour SYNCHRONIZING. Une raison de ce peut être si le miroir et l'entité ne peuvent pas communiquer pour une période donnée. Ou il peut se produire si la base de données principale est de générer le journal des transactions plus rapidement que le journal peut être envoyé vers le miroir. Dans les deux cas, et selon la mise en miroir de base de données est configuration, la base de données principale peut continuer à traiter une file d'attente de journal des transactions enregistrements va constituer (appelée la file d'attente SEND) et des transactions. Ce doit être envoyé au miroir pour qu'il intercepter des. Jusqu'à ce que la base de données en miroir a intercepté haut, l'état en miroir restera SYNCHRONIZING.

Si le lien réseau est peu fiable entre le principal et miroir, vous pouvez voir l'état de mise en miroir basculer back-et-suite entre SYNCHRONIZING et SYNCHRONIZED. Vous trouverez une description complète de ces états dans le livre blanc Base de données mise en miroir dans SQL Server 2005.

QNous avons synchrone base de données mise en miroir ensemble haut de notre base de données application principale et notre surveillance indique que la file de rétablissement sur le miroir est généralement près à zéro. Nous aimerions utiliser la base de données miroir pour la génération d'états et cohérence vérification afin de pouvoir effectuer une meilleure utiliser du matériel redondant. Nous savons cela est possible, mais y a-t-il des inconvénients à cela que nous doit de ?

A Cela est relativement courant, mais il existe quelques inconvénients que vous deviez regarder pour.

Le premier problème licence. La licence pour l'instance SQL Server hébergeant la base de données miroir est disponible si l'instance est uniquement utilisée pour cet objectif. Dès que vous créez un instantané de base de données sur la base de données miroir ou continuer avec cette instance de SQL Server, vous devez acheter une licence.

Agissant la vérification de cohérence est, leur exécution sur la base de données miroir (via un instantané de base de données) ne vous fournit les garantie quant à la cohérence de la base de données principale pas. Uniquement les enregistrements de journal de transactions sont inversés entre les bases de données, donc si le sous-système d'E / S endommage une page dans la base de données principale, cette corruption n'est pas reflétée sur la base de données miroir. Cela signifie une vérification de cohérence de la base de données miroir ne serait pas afficher cette page endommagée dans la base de données principale.

Génération d'états est une utilisation bonne d'un miroir de base de données. Le premier problème personnes exécutée dans est comment actualiser l'instantané de base de données qui exécutent les états par rapport à. Un instantané de base de données ne peut pas être actualisé pour un nouvel instantané doit être créé et l'application de génération d'états doit se connecter à l'instantané de nouveau. Cela nécessite logique supplémentaire pour être intégré à l'application de génération d'états. Le deuxième problème consiste à déterminer comment l'application de génération d'états doit se comportent dans le cas d'un basculement en miroir, doit il rester connecté à l'entité de nouveau ou doit il déplacer vers le nouveau disque miroir ? Passer dans les détails sur cette n'entre pas le cadre de cet article.

Avec toute utilisation supplémentaire de la base de données miroir, l'objectif est le risque de provoque des problèmes de performances sur le miroir. Créant un instantané de base de données d'ajoute la charge d'E / S supplémentaire en que la première fois une page change dans la base de données miroir, elle doit être déplacée dans l'instantané de base de données. En outre, les charge vous puis placer sur l'instantané de base de données augmente la charge d'E / S sur la base de données miroir, lorsque la plupart des pages lues à partir de l'instantané de base de données sont vraiment être lirez à partir de la base de données miroir, comme elles ne pas ont été modifiées depuis que l'instantané a été créé.

Cette charge d'E / S supplémentaire sur la base de données miroir peut ralentir la relecture des enregistrements de journal des transactions et à son tour avance à une file d'attente. Cette file d'attente est appelée la file d'attente de RÉTABLISSEMENT et est la quantité de journal des transactions qui doit être relu avant que la base de données miroir peut provenir en ligne après un basculement. Plus la charge d'E / S sur la base de données miroir à partir l'instantané de base de données, la plus grande que la file d'attente de nouvelle TENTATIVE va croître potentiellement et plu la base de données n'est pas disponible après un basculement.

Vous n'avez indiquez que votre longueur de file d'attente RÉTABLIR est généralement près à zéro pour cela peut être pas un problème pour vous, mais il est sans aucun doute quelque chose à regarder pour donc vous n'obtenez compromettre la disponibilité des bases de données en cours de la possibilité accrue d'exécuter des rapports.

S Paul Randal est le directeur Gestion de SQLskills.comet un MVP de SQL Server. Il a travaillé dans l'équipe SQL Server Storage Engine chez Microsoft de 1999 à 2007. Paul écrit DBCC CHECKDB/réparation pour SQL Server 2005 et était responsable pour le moteur de stockage base pendant le développement de SQL Server 2008. Paul est un expert de la récupération après incident, haute disponibilité et la maintenance de base de données et un présentateur standard à des conférences dans le monde entier. Blogs il à SQLskills.com/blogs/paul, et vous pouvez lui trouver sur Twitter à Twitter.com/PaulRandal.