Q SQL &A: Goulots d'étranglement et journaux de transactions

  • Article

Il est parfois facile de déterminer la nature du goulot d'étranglement des performances…et d'autres fois non. Il en est de même pour la configuration des journaux de transactions.

Paul S. Randal

Goulot d'étranglement stockage

Q: Je tente de déterminer la nature de performance goulot d'étranglement de mon système. Je suis convaincu que c'est la couche de stockage comme je vois la file d'attente de disque longueurs aller supérieur à deux. J'ai lu que c'est un bon moyen de prouver que SQL Server est surcharge le stockage. Est-ce vrai ? Dans l'affirmative, que puis-je faire à ce sujet ?

A: Malheureusement, ce que vous avez lu est une légende urbaine. SQL Server est conçu pour utiliser les e/S asynchrones et repoussera joyeusement la longueur de file d'attente de disque au-dessus de deux. Chaque thread qui émet un IO puis continue de faire quelque chose d'autre (potentiellement) jusqu'à l'IO est terminée. SQL Server tente de maximiser le débit dans le sous-système de IO par l'émission d'IOs asynchrones simultanées. Il sera également effectuer les opérations comme read-ahead lors de l'analyse de gros volumes de données.

En fait, certaines opérations comme DBCC CHECKDB seront saturer le sous-système de IO. Il n'est pas rare de voir des longueurs de file d'attente du disque de plusieurs centaines. Vous pouvez en savoir plus sur cette légende urbaine d'une longueur de file d'attente du disque dans cette de blog post .

La question devient alors, "ce que faire vous regardez pour déterminer s'il y a un goulot d'étranglement du sous-système de IO?" Il y a deux compteurs de performance dans l'objet de performance de disque physique. Vous devez accorder une attention à ces :

  • Moy. Disque s/lecture.
  • Moy. Disque sec-écriture.

Ceux-ci donnent le temps, en millisecondes, qu'il faut pour remplir un IO. Si ces chiffres sont toujours plus élevés (ou ont des pointes plus régulières) à la norme (qui devrait être entre 5ms et 12ms), puis le disque physique est le goulet d'étranglement IO. Bien entendu, ce disque physique peut être un LUN de SAN, mais vous ne pouvez pas creuser un peu plus de Windows.

Si vous avez plusieurs SQL Server des fichiers de journaux et de données sur ce disque physique, vous devrez déterminer quels fichiers sont à l'origine la charge IO. Utilisez la gestion dynamique (DMV) de vue sys.dm_io_virtual_file_stats et effectuer des analyses chronologiques simples sur les résultats.

Si les résultats de la DMV n'indiquent une lourde charge sur ce disque physique, un administrateur de stockage peut ont placé les fichiers d'autres applications sur cette partie du sous-système IO. Cette charge de travail pourrait être ce qui monopolise la bande passante de IO. Dans ce cas, vous devrez demander cet administrateur particulier pour déplacer les fichiers de SQL Server vers une partie dédiée du sous-système IO.

Si c'est purement les fichiers SQL Server sur le sous-système et vous pouvez identifier ceux qui causent le IO excessive, de considérer ces stratégies :

  • Regardez la charge de travail de requête de base de données et de déterminer si elle effectue excessive de la table en raison d'une stratégie d'indexation incorrecte, ou des plans de requête bad causées par des statistiques périmées.
  • Déplacer certains fichiers à une partie différente du sous-système IO.
  • Ajouter plus de mémoire sur le serveur pour permettre un plus grand bassin de tampon de SQL Server (en mémoire cache des pages de données fichier) et d'éviter autant lire IO.

Si aucune de ces travaux et c'est vraiment juste un cas où la charge de travail a dépassé le sous-système de IO, passer à un sous-système de IO plus capable. Vous pourrait également envisager de stockage de mémoire flash de classe entreprise comme la Fusion-io.

Questions de taille

Q: Je suis établissant les exigences de stockage pour certains serveurs de nouveau et j'éprouve des difficultés pour déterminer comment grand pour rendre les journaux de transactions. Dans le passé, j'ai essayé à la base il sur la taille de la transaction. Parfois, cependant, il semble à double. Pouvez-vous expliquer comment je peux arriver à une estimation décente ?

A: Il n'y a pas facile de la formule pour calculer la taille de journal de transaction optimal. Malheureusement, il est également difficile d'empêcher la transaction log croissance sauf si vous avez désactivé auto-pousser pour le fichier journal. Cependant, éteignant auto-pousser n'est jamais conseillé.

Le premier d'entre eux est le niveau RAID sous-jacent. Les différents niveaux RAID ont compromis différentes que ce qui concerne les performances et la redondance. Par exemple, la configuration RAID moins cher encore offre une redondance RAID-5, mais cette configuration peut seulement face à une panne du lecteur unique (à moins d'utiliser RAID-6, ou configuré les disques de secours) et peut parfois altérer la performance pour l'écriture-lourdes charges de travail, selon le nombre de lecteurs sont dans le tableau.

Le journal des transactions devrait toujours être capable de se développer à l'auto. Cela est particulièrement vrai en cas d'urgence lorsque la taille de votre fichier journal surveillance échoue. Par exemple, dire vous avez une alerte de l'Agent SQL Server sur le compteur de performance pour cent Journal utilisé va supérieur à 90 %, mais le contact d'urgence notifié par/radiomessagerie est des malades. Si le journal ne peut croître, actuellement en cours d'exécution modifier la base de données des transactions arrête et faire reculer. Cela se traduit de temps d'arrêt pour votre charge de travail.

Cependant, disant il n'y a aucun formule facile n'est un peu trompeur. Il existe de nombreuses opérations qui prennent place un journal de transaction. Vous pouvez utiliser la taille de ces opérations pour estimer vos besoins de journal des transactions. Celles-ci peuvent être des opérations qui se produisent dans le cadre de votre charge de travail quotidienne, ou des actions plus rares telles que de la maintenance de la base de données. Vous devez tenir compte de tous. La liste d'opération comprend :

  • La plus grande transaction unique d'insert/update/delete votre charge de travail effectue (si une transaction implicite de single-déclaration qui touchent des millions de lignes de la table, ou une transaction explicite qui effectue de nombreuses opérations).
  • La plus grande opération de vrac votre charge de travail effectue, comme par exemple un BULK INSERT. Si vous utilisez le mode de restauration complète, vous pourrez peut réduire le montant du journal des transactions générée en utilisant le modèle de récupération BULK_LOGGED. Si vous utilisez le modèle de récupération BULK_LOGGED pour minimale de journalisation pour certaines opérations, il peut affecter votre capacité à se rétablir après une catastrophe. Consultez mon blog, " un SQL Server DBA mythe par jour : BULK_LOGGED récupération modèle . »
  • Une reconstruction d'index de votre plus grande index cluster. Vous pourrez également utiliser BULK_LOGGED ici.

Avec toutes ces opérations, il n'est pas seulement le montant de journaux de transactions, que vous devez prendre en considération, il faut aussi tenir compte de l'espace, le système de gestion des journaux de transaction sera « réserve » permettant de rollback adéquat de transactions. Si une transaction génère 100 MB d'enregistrements du journal des transactions, le système sera réserve environ 100 Mo d'espace vide dans le journal des transactions pour garantir qu'il peut interrompre la transaction et restaurer correctement. C'est un mécanisme de sécurité pour empêcher une base de données devient incompatible. C'est pourquoi vous peut avoir vu le journal des transactions croître, même si vous pensez que vous avez donné suffisamment d'espace pour la plus grande transaction.

Une autre chose à considérer, c'est s'il existe des raisons pourquoi les enregistrements du journal des transactions doivent rester dans le journal. Cela pourrait conduire à un journal de transactions croissante, avoir à pousser encore plus. Voici quelques-unes des raisons possibles :

  • La base de données est en utilisant les modèles de récupération complète ou BULK_LOGGED et pas effectuer des sauvegardes du journal des transactions (ou leur exécution rarement). Vous devez sauvegarder les enregistrements du journal avant de les jeter leur.
  • Il y a une transaction exceptionnellement longue. Cela empêchera jeter tous les enregistrements de journal de transaction générées depuis le début de l'opération de longue durée.
  • Mise en miroir de base de données est utilisé et certains enregistrements de journal de transactions n'ont pas été envoyées par le serveur principal vers le serveur miroir. Vous ne peut pas ignorer ces jusqu'à ce qu'ils ont été envoyés.
  • La réplication transactionnelle (ou réplication de peer-to-peer) est à utiliser et il y a certains enregistrements de journal de transaction que le travail de l'Agent de lecture du journal n'a pas traité.

Si vous êtes vu la croissance de journal de la transaction et vous ne savez pas pourquoi ce qui se passe, demandez à SQL Server. Publiez la requête :

SELECT [log_reuse_wait_desc] FROM sys.databases
WHERE [name] = 'dbmaint2008';
GO

Le résultat sera la raison pour laquelle vous ne peut pas ignorer certains enregistrements de journal et la réutilisation de l'espace de journal (appelé « compensation » ou « troncature » du journal).

Comme vous pouvez le voir, il y a très peu de choses qui peut affecter la taille du journal des transactions, encore plus si vous envisagez de la base de de données tempdb. Vous pouvez lire un peu plus sur ce sujet dans mon billet de blog, " de l'Importance de la gestion de taille pour le journal adéquat de transactions," et dans le technet Magazine l'article compréhension SQL Server Logging et récupération . »

Enregistrement obligatoire

Q: Pouvez-vous expliquer pourquoi je ne peux pas faire opérations SQL Server non-connecté ? J'ai lu cette troncature de table est non journalisée — pourquoi ne peut il être un paramètre permettant de faire toutes les opérations non-consignées pour SQL Server s'exécute plus vite ? Que se passe-t-il si je me fiche de pouvoir récupérer après une catastrophe ? Je voudrais surtout être en mesure d'ignorer le journal des transactions tempdb.

A: Ce que vous avez lu sur une opération de tronquer la TABLE n'est pas vrai. Toutes les opérations sur toutes les bases de données sont enregistrées dans une certaine mesure. Certains sont « minimalement connectés, » comme la troncature de la table. En termes simples, une opération minimalement connectée est un où l'allocation et la désallocation de pages du fichier de données est connecté. Toutes les opérations sur les dossiers d'index et de la table sur les pages ne sont pas enregistrées. Cela accélère les opérations et signifie moins journal des transactions est généré — mais il reste encore quelques forestière.

Une troncature de table est toujours minimale consignée dans tous les modèles de récupération. Les autres opérations minimalement connectées (tels que les index des charges build/reconstruction et en vrac) sont très peu consignées lorsque vous utilisez les modèles de récupération SIMPLE ou BULK_LOGGED.

Les opérations uniquement vraiment non-connecté dans SQL Server sont celles qui touchent le magasin de version dans tempdb, qui prend en charge les fonctionnalités comme l'isolation d'instantané et des opérations de l'index en ligne. Ceux-ci peuvent être non-connectés parce qu'il n'y a jamais un besoin pour faire reculer une opération de magasin de version ou l'exécution crash-récupération, sur la base de données tempdb.

C'est pour l'essentiel de votre question. Pourquoi les opérations dans SQL Server ne peut pas être non-connecté ? SQL Server doit toujours être en mesure de faire reculer une opération si quelque chose va mal. Si il n'y avait qu'aucun description (comme les enregistrements du journal des transactions) de ce que l'opération n'avait fait, comment SQL Server saurait quoi faire pendant la restauration ? Vous pouvez obtenir uniquement par le biais de journalisation.

Même si vous ne leur-recouvrement, SQL Server doit encore être en mesure de faire reculer opérations si la base de données s'exécute d'espace ou rencontre un secteur corrompu, ou si la requête est exécutée de mémoire. Si SQL Server ne peut pas faire reculer une opération, la base de données devient inutilisable et la charge de travail s'arrête.

Ceci s'applique également à la base de données tempdb. Bien que l'exploitation forestière est simplifié et réduit dans tempdb, vous ne pouvez jamais il supprimer entièrement pour les mêmes raisons. Aussi, SQL Server doit être capable de courir crash-récupération après chaque accident afin de chaque base de données est conforme. Par ailleurs, la base de données est inutilisable. Bottom line : Il n'y a aucun moyen de faire des opérations non journalisées dans SQL Server, et je ne m'attends que de changer.

Paul Randal

Paul S. Randal est le directeur général de SQLskills.com, d'un directeur régional de Microsoft et de 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 la 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.

Contenu associé