SQL Q & A: L'importance de la vitesse et de la taille
La vitesse relative des requêtes SQL Server, la taille des fichiers de données et les journaux de transaction semblent être des facteurs de performances principaux.
Paul S. Randal
Planifier votre requête
Q. Nous avons eu récemment un problème avec une requête qui prend un certain temps à courir. La requête a couru plus vite après que nous avons reconstruit l'index cluster sur la table, qu'il s'agissait. Je ne pouvais pas voir les analyses dans le plan de requête, alors pourquoi influerait fragmentation sur l'exécution de la requête ?
**R :**Dans ce cas, il semble que la fragmentation d'index n'avait rien à voir avec l'exécution de la requête. Un plan de requête non optimaux était probablement la source du problème.
Lorsque vous effectuez une reconstruction d'index, toutes les statistiques de colonne pour cet index sont automatiquement reconstruits. C'est l'équivalent d'une analyse complète. Toutes les valeurs de colonne sont considérés lors de la création les statistiques, de sorte qu'il crée une représentation complète de la distribution des valeurs communes.
Tous les plans de requête créées à l'aide de ces statistiques sont essentiellement invalidées et sont recompilés. Recompiler un plan signifie que la prochaine fois que vous exécutez la requête, l'optimiseur de requêtes passent par le processus de choix d'une nouvelle manière plus optimale de produire des résultats de la requête. Il s'agit du plan de requête.
Dans ce cas, votre reconstruction d'index a déclenché une recompilation de plan de requête. Le nouveau plan a été plus efficace que le précédent. Il y a un certain nombre de raisons possibles pour cela. Le plan de requête peu performants aurait pu être très optimal et autorisées de requêtes rapides quand il a été compilé. Comme la distribution des valeurs de données dans le tableau a changé au fil du temps, le plan de requête aurait pu devenir moins optimal.
L'ancien plan de requête pourrait utilisent un index non-cluster repose sur le fait qu'une colonne particulière (partie de l'index non-cluster) était très sélective. Par conséquent, il était logique d'utiliser l'index non ordonné en clusters pour trouver les valeurs de données et puis plus de colonnes dans la table elle-même. Cela s'appelle une opération de recherche de clé.
Si la distribution des données change radicalement telle que la colonne n'était plus très sélective, cela aurait pu un grand nombre de recherches clés coûteuses. Compte tenu de la nouvelle distribution de données, un meilleur plan aurait été d'utiliser un index non cluster différent.
Lorsque l'index ordonné en clusters a été reconstruite, les statistiques ont été mises à jour. Cela a provoqué une recompilation du plan, qui a choisi l'index non-cluster plus sélectif. Ceci à son tour produit un plan plus efficace.
Alors que je suis faire des hypothèses sur la cause de l'accélération de la requête, vous pouvez voir ce que je veux dire au sujet de la reconstruction d'index étant simplement l'élément déclencheur de la recompilation du plan. Il ne peut pas avoir directement fixe la cause racine du problème de performances en premier lieu.
Plusieurs fichiers, plus d'espace
Q. J'ai un groupe de fichiers avec deux fichiers, et les deux sont très complet. Je tiens à ajouter plus d'espace pour le groupe de fichiers, donc je vais ajouter deux autres fichiers et ensuite avoir SQL Server rééquilibrer les données parmi tous les quatre fichiers. Est-ce possible ?
**R :**Malheureusement, il n'est pas une bonne façon de rééquilibrer les données entre les fichiers dans un groupe de fichiers une fois que vous ajoutez de nouveaux fichiers d'espace supplémentaire. J'ai blogué dans le passé au sujet de comment avoir plusieurs fichiers par groupe de fichiers peut conduire à des gains de performances pour certaines charges de travail. Il est notoire que c'est le cas.
C'est une grande généralisation, cependant. Combien gain vous obtiendrez dépend du sous-système d'e/s, la mise en page du fichier de données et la charge de travail. Il y aura un point devient trop le nombre de fichiers de données et est réellement un détriment des performances. Découvrez ces billets de blog sur analyse comparative de plusieurs fichiers de données et plusieurs fichiers de données (SSD).
SQL Server n'a tout simplement un mécanisme de rééquilibrage des tarifs pour les données dans un groupe de fichiers. Le fichier de données d'où proviendra la prochaine allocation est déterminé par la répartition alternée et remplissage proportionnel. Répétition alternée est où les allocations arrivent de chaque fichier de données à son tour. Aura une allocation de fichier, puis une allocation de fichier deux, puis revenir au fichier un nouveau. Cependant, les allocations sont effectuées de manière proportionnelle. Plusieurs affectations sont font sur des fichiers de données qui ont proportionnellement plus d'espace libre que les autres fichiers de données du groupe de fichiers.
La prémisse fondamentale de remplissage proportionnel est que chaque fichier a une pondération, où les dossiers avec moins d'espace libre aura une pondération plus élevée. Fichiers avec beaucoup d'espace libre aura une pondération inférieure. Les fichiers avec pondération inférieure seront alloués de plus fréquemment, ce qui signifie que les fichiers avec plus d'espace libre est affecté de la plus grande partie.
Ce que tout cela signifie que lorsque vous ajoutez de nouveaux fichiers à un groupe de fichiers complet, affectations subséquentes viendra principalement de nouveaux fichiers. Ils auront beaucoup plus faibles remplissage proportionnel des pondérations que les anciens fichiers contenant des données intrinsèquement plus. Les nouveaux fichiers deviennent zones réactives allocation menant à potentiellement plus faibles performances globales avec des charges de travail.
Vous ne pouvez pas contourner l'algorithme de remplissage proportionnel. Ni vous pouvez modifier les coefficients de pondération. Même d'essayer quelque chose comme la reconstruction de l'index dans le groupe de fichiers ne fonctionnera pas, comme les allocations pour les nouveaux index proviendra des nouveaux fichiers de données.
Si vous souhaitez ajouter d'autres fichiers à un groupe de fichiers, la meilleure façon est de créer un nouveau groupe de fichiers avec plusieurs fichiers. Puis, déplacez les données de tables et d'index pour le nouveau groupe de fichiers à l'aide de CREATE INDEX... AVEC (DROP_EXISTING = ON) commande, en spécifiant le nouveau groupe de fichiers comme emplacement cible. Après que vous avez déplacé toutes les données, vous pouvez laisser tomber le groupe de fichiers ancien, vide. Vous pouvez même déplacer métier des données au nouveau groupe de fichiers, en utilisant une astuce de Kimberly Tripp.
Effacer le journal
Q. J'ai récemment eu un problème avec un fichier journal des transactions qui poussaient très grand. J'ai été incapable de réduire. Pouvez-vous suggérer des choses pour moi de vérifier ?
**R :**Il existe deux causes courantes d'une cohérence de base de données (DBCC) SHRINKFILE ne fonctionne ne pas correctement sur le journal des transactions de vérification. Comme note latérale, réduire un fichier journal n'introduit pas la fragmentation d'index affectant la performance de la même manière qu'un psy de fichier de données. Toutefois, il devrait être encore une opération rare.
Un fichier journal rétractable enlève simplement ouvrir une session des parties inactives ou actuellement inutilisés de la transaction à la fin du fichier du journal des transactions. Ces « parties » du journal des transactions sont appelés fichiers journaux virtuels (virtuels). Il y a deux problèmes qui peuvent vous empêcher d'être en mesure de réduire VLFs : ne pas effectuer l'opération réelle qui permet VLFs deviennent inactifs et ne pas avoir VLFs inactifs à la fin du journal des transactions.
VLFs deviennent inactifs à travers un processus connu sous le nom « effacer le journal. » Vous pouvez faire cela avec un point de contrôle si vous utilisez le modèle de récupération SIMPLE. Vous pouvez aussi faire cela avec une sauvegarde du journal des transactions si vous utilisez les modèles de récupération FULL ou BULK_LOGGED. Tant que les enregistrements de journal de transaction dans les VLFs ne sont pas requis par SQL Server en quelque sorte, vous pouvez faire les VLFs inactifs.
SQL Server peut toujours exiger le journal enregistre pour certaines situations, telles que s'ils font partie d'une transaction de longue durée, s'ils n'ont pas été analysés par le travail d'Agent de lecture du journal de réplication, ou s'ils sont en train d'être envoyé à un miroir de base de données ou un réplica de groupe de disponibilité de. Vous pouvez demander à SQL Server, pourquoi un journal de transactions particulières ne « efface » en utilisant la commande suivante :
SELECT [log_reuse_wait_desc] FROM sys.databases WHERE [name] = N'MyDBName';
Utilisez la sortie de cette commande comme un indicateur de ce qu'il faut faire ensuite. Une fois que le journal des transactions est capable d'effacer, si DBCC SHRINKFILE n'est toujours pas en mesure de réduire le journal, cela signifie qu'il n'a pu se rétrécir vers le bas pour la VLF actuellement actif (ou VLFs). Ceux-ci pourraient se trouvent au milieu du fichier journal des transactions. Dans ce cas, effectuez à nouveau l'opération de nettoyage des journaux et puis un autre rétrécissement.
Vous devrez peut-être faire ceci plusieurs fois et, en fin de compte, il pourrait être difficile, voire impossible, à réduire le journal des transactions à sa taille minimale sur une base de données de production occupés. Toutefois, ces problèmes communs devrait aide vous réduisez la transaction fichier journal assez soient remplies. Vous pouvez en savoir plus sur ces sujets dans mon février 2009 TechNet Magazine article, "journalisation de compréhension et la récupération dans SQL Server."
Intégrité d'e/s
Q. Je continue à voir des messages dans le journal des erreurs de l'un de mes instances de SQL Server disposant d'entrées/sorties pour être essayé à plusieurs reprises avant qu'ils réussissent. Cela semble inquiétant pour moi. Pouvez-vous expliquer ce que signifient les messages ?
**R :**Ces messages sont des instances de message 825. Ce message a été introduit dans SQL Server 2005. C'est un signe avant-coureur que votre sous-système d'e/s a des problèmes d'intégrité.
Si SQL Server émet une lecture I/O et l'I/O est défectueuse (soit le système d'exploitation indique à SQL Server les e/s a échoué, ou les données retournées par le système d'exploitation sont jugées par SQL Server comme corrompu), SQL Server retente l'opération de lecture quatre fois plus pour voir si l'un d'eux réussira. La prémisse de ceci est que les sous-systèmes I/O ont parfois des anomalies transitoires, alors une nouvelle tentative d'une e/s ayant échoué peut fonctionner sur une tentative ultérieure. Cela évite la possibilité immédiate d'indisponibilité.
Si aucun d'entre les tentatives réussissent, SQL Server déclenche une erreur 823 ou 824, et la connexion est interrompue (que ces erreurs sont de gravité 24). Si un de la tentative réussit, la charge de travail continue comme d'habitude, et SQL Server écrit le message 825 dans le journal des erreurs.
Le message de 825 possède le format suivant :
Msg 825, Level 10, State 2, Line 1.
Il s'agit d'une lecture du fichier "J:\SQLskills\MyDatabase_DF1. NDF"au décalage 0 × 000004AA188000 a réussi après avoir échoué une fois avec l'erreur : somme de contrôle incorrecte (prévu : 0 × 33d1d136 ; réelles : 0 × 0a844ffd). Des messages supplémentaires dans le journal des événements SQL Server erreur système et journal pourraient fournir davantage de détails.
Cette condition d'erreur menace l'intégrité de la base de données et que vous devrez corriger la situation. Compléter un DBCC CHECKDB complet. Cette erreur peut être provoquée par plusieurs facteurs. Pour plus d'informations, voir SQL Server Books Online. Ce que c'est vraiment c'est que le sous-système d'e/s est en train d'échouer. Une installation similaire existe dans Exchange Server, à l'origine de l'idée de ce mécanisme.
Bien que cette fonctionnalité est utile, le message 825 est seulement la gravité 10 (ce qui signifie d'information). À moins que vous soyez à la recherche à travers les journaux d'erreurs ou avez un Agent alerte mis en place pour le message 825, ces messages critiques peuvent passer inaperçues. Néanmoins, vous devriez avoir une alerte mis en place pour 825 messages et prendre des mesures dès qu'un message de renouvellement de lecture se produit. Vous pouvez lire plus au sujet de ce message et sur le paramétrage d'une alerte à l'attraper dans ce blog post.
.jpg)
Paul S. Randal est le directeur général de SQLskills.com, directeur régional Microsoft et MVP SQL Server. Il a travaillé sur l'équipe SQL Server Storage Engine chez Microsoft de 1999 à 2007. Il a écrit DBCC CHECKDB/repair pour SQL Server 2005 et était responsable de Core Storage Engine pendant le développement de SQL Server 2008. Randal est un expert de récupération après incident, haute disponibilité et de maintenance de base de données et présente fréquemment à des conférences dans le monde entier. Il blogs à SQLskills.com/blogs/paul, et vous pouvez trouver lui sur Twitter à twitter.com/PaulRandal.