Questions / Réponses SQL : Histoires de transactions

Article
08/24/2016

Les journaux de transactions sont essentiels aux efforts de sauvegarde, mais vous devez vous assurer qu'ils sont correctement configurés et de compensation lorsqu'ils sont censés pour clair.

Paul S. Randal

Effacer ces journaux

Q. J'ai lu que dans le modèle de récupération complète, transaction journal compensation seulement survient lorsqu'il y a une sauvegarde du journal des transactions. Je vois un comportement étrange, cependant. Parfois, les sauvegardes de journaux ne vider le journal. Il semble que les sauvegardes de données qui le font. Quel est le problème ?

**R :**Vous avez raison : dans les modèles de récupération complète et journalisé en bloc, le journal des transactions peut seulement être effacé (extraits du journal sont marquées comme réutilisables) lorsqu'il y a une sauvegarde du journal des transactions. Dans le modèle de récupération simple, c'est un point de contrôle qui efface le journal des transactions. Pour plus d'informations sur l'exploitation forestière et de la récupération en général, voir mon article TechNet, «Logging de compréhension et de récupération dans SQL Server. "

Cependant, il y a une torsion. Bien que l'opération de défrichage journal de transaction aura lieu à la conclusion d'une sauvegarde du journal des transactions, il n'y a aucune garantie que les parties du journal des transactions de seront effectivement effacés. Le fait qu'une partie de du journal des transactions a été sauvegardée ne suffit pas de le laisser être effacée. SQL Server ne doit pas besoin du journal des transactions de la partie à d'autres fins à tout autre moment.

SQL Server peut exiger encore accès à une partie du journal des transactions de car une sauvegarde des données (comme une sauvegarde complète de base de données) est en cours d'exécution. Une sauvegarde de données doit inclure la partie du journal des transactions. Il faudra au moins les données de journal de transactions générées tandis que les données sont être copiées à partir de la base de données. Il peut même besoin de plus.

Cela signifie que pendant l'exécution d'une sauvegarde de données, Journal de compensation ne peut pas se produire. Cela est vrai même si une sauvegarde du journal des transactions simultanées se déroule (sauvegardes de journaux et de données simultanées sont possibles depuis SQL Server 2005).

Dans ce cas particulier, la partie sauvegarde SQL Server « mémorial » il y a eu une sauvegarde du journal des transactions et souvient quelles parties du journal des transactions ont été sauvegardés. Lorsque la sauvegarde de données simultanées se termine, SQL Server peut effacer les portions de journal des transactions qui ont été sauvegardées par la sauvegarde du journal des transactions (tant que quelque chose n'a pas besoin de ce journal de transactions, comme une transaction non validée, longue).

Ce comportement est appelé troncatures différée. C'est le comportement que tu vis. On dirait que la sauvegarde de données est effacer le journal des transactions, mais c'est vraiment un artefact de sauvegardes de journaux des transactions simultanées.

Miroir, miroir

Q. Nous sommes en train de mettre en œuvre la mise en miroir de base de données pour se protéger contre la perte de données pour l'une de nos bases de données. Pouvez-vous me dire ce que nous devrions suivre pour assurer que notre mise en miroir de base de données fonctionne correctement ?

**R :**Lors de la mise en œuvre de la mise en miroir de base de données, vous devez absolument surveiller la taille de la file d'attente de l'envoi et la file d'attente REDO. Ces directement liés à la perte de données et les temps d'arrêt, respectivement.

La file d'attente SEND pistes combien journal des transactions de la base de données principale n'a pas encore été envoyée au serveur miroir. Cette partie du journal des transactions de décrit les modifications de la base de données principale qui seraient perdus si une catastrophe rendu indisponible.

Beaucoup de personnes pensent que si vous configurez la base de données mise en miroir de haute sécurité ou haute disponibilité (également connu sous le nom de synchrone en miroir), la file d'attente SEND sera toujours zéro. Une transaction sur la base de données principale ne peut s'engager jusqu'à ce que tous les journaux de transactions pour la transaction ont été envoyés au serveur miroir. Cependant, ce n'est pas vrai. Il est possible, dans certaines circonstances, pour les serveurs principal et miroir de perdre le contact avec les autres et la base de données principale de rester en ligne. Dans ce cas, la file d'attente SEND commencera à se développer. Cela augmente le risque de perte de données. Aucune perte de données est garanti uniquement lorsque l'état de mise en miroir est synchronisé.

La file d'attente REDO pistes combien journal des transactions sur le serveur miroir n'a pas encore été relus sur la base de données miroir. C'est une idée fausse commune que, lorsque le serveur principal envoie le journal des transactions pour le serveur miroir, il est aussi immédiatement relus sur la base de données miroir. Cela n'est également pas vrai. Qui a besoin, c'est que le journal des transactions est écrit au stockage durable sur le serveur miroir.

Cela signifie que, selon le matériel de serveur miroir (y compris son sous-système d'e/S, une charge de travail simultanée et autres facteurs ayant une incidence sur le rendement), il peut être une file d'attente journal dimensionnable transaction qui n'a pas encore été relue sur la base de données miroir.

Lorsqu'il y a un basculement en miroir, la base de données miroir Won't come en ligne jusqu'à ce que le journal des transactions a été relue. C'est approximativement proportionnel à la quantité du journal des transactions un-replayed. Il représente également l'indisponibilité de l'application et ses utilisateurs connaîtront cas de basculement en miroir.

Vous devez être prudent sur la taille de la file d'attente REDO également si vous envisagez d'utiliser des instantanés de la base de données de la base de données miroir pour fins de déclaration. Lorsque vous créez un instantané de la base de données, récupération de base de données doit traiter le journal des transactions exceptionnelle donc l'instantané de base de données est une vue cohérente de la base de données sous-jacente. Plus la file d'attente REDO, plus il faudra créer l'instantané de base de données. Il affecte également les performances du serveur miroir, qui peut faire la file d'attente REDO grandir.

Il y a plusieurs autres paramètres que vous devez surveiller, tels que la latence du réseau entre les serveurs principal et miroir. Cela se traduit par la surcharge par transaction de mise en œuvre mise en miroir synchrone. Vous pouvez utiliser l'analyseur de performances (sys.dm_os_performance_counters vue de gestion dynamique [DMV]) pour suivre ces métriques. Vous pouvez aussi utiliser l'outil de moniteur de mise en miroir de base de données construite en Management Studio et décrit dans En ligne de SQL Server. L'outil vous permet de créer facilement des alertes basés sur les seuils de taille de file d'attente.

Gains en termes de performances

Q. J'ai été informé que je devrais avoir plusieurs fichiers de données pour aider à la performance, et je comprends pourquoi. Maintenant je suis dit je devrais aussi plusieurs fichiers de journaux de transactions pour SQL Server peuvent faire des opérations d'e/S plus efficaces pour le journal des transactions. Est-ce correct ?

**R :**Non, ce n'est pas correct. Malheureusement, j'entends cette recommandation de temps en temps.

Plusieurs fichiers de données peuvent aider avec la prétention de sous-système de I/O. Dans certains cas (couramment avec tempdb), ils peuvent aussi aider la prétention sur les structures d'allocation de base de données qui sont dans la mémoire. Il y a toutes sortes de lignes directrices sur les fichiers de données combien de créer.

La recommandation d'avoir plusieurs fichiers de journaux de transactions est extrapolée à partir de la même recommandation pour les fichiers de données, mais il est incorrect. Plusieurs fichiers de journaux de transactions ne fournissent pas de tout gain de performance, de disponibilité, d'évolutivité ou de toute autre métrique mesurable.

Le journal des transactions est et doit être séquentiel dans la nature afin que SQL Server n'effectuer des e/S parallèle dans le journal des transactions s'il y a plusieurs fichiers journaux de transactions actuels. Le premier fichier sera utilisé dans son intégralité, puis le deuxième fichier, puis le troisième et ainsi de suite. Cela se produira jusqu'à ce que le journal des transactions entoure et commence une fois de plus le premier fichier journal des transactions. Ces fichiers journaux supplémentaire à cet effet aucun gain.

Il y a une situation où un deuxième fichier journal des transactions peut-être être nécessaires. Si le premier fichier journal des transactions est plein, le journal des transactions ne peut effacer (voir la réponse à la première question pour explication supplémentaire). Le premier fichier journal ne peut pas croître pour accueillir plusieurs enregistrements de journal des transactions. Dans ce cas, ajout d'un deuxième fichier journal des transactions va permettre temporairement des modifications de base de données de continuer jusqu'à ce que le premier fichier journal des transactions peut effacer.

Réduire les e/S, augmentation des performances

Q. Nous avons été enquête sur l'utilisation de disques à semi-conducteurs (SSD) pour tenter de réduire certains de nos problèmes de performances de I/O, mais nous sommes confus sur les bases de données sur eux. Pouvez-vous nous donner quelques indications sur la meilleure façon d'utiliser SSDs ?

**R :**Il s'agit d'une question intéressante. Il existe une variété de réponses « définitifs », que vous verrez sur les forums d'aide Internet, tels que, « toujours mis tempdb sur votre disque dur SSD » ou « toujours mettre l'opération enregistre sur votre disque dur SSD. » Aucune de celles-ci est appropriée.

Il existe certains facteurs de garder à l'esprit lorsqu'on examine la meilleure façon d'utiliser SSDs avec SQL Server :

SSD est chers, vous souhaitez vous assurer que vous obtenez le meilleur retour sur investissement de leur part.
SSD fournit la plupart gain de performance pour aléatoires charges de travail I/O, charges de travail I/O non séquentielles.
Pour toute partie d'un sous-système d'e/S surchargé, un SSD donnera un coup de pouce de performance — indépendamment du motif I/O — en raison de sa nature de réduire considérablement la latence de lecture-écriture.
Connexion directe SSDs devraient fournir un regain de performance plus profond que celles consulté sur tout type de tissu de communications.
Les journaux de transactions sont en écriture séquentielle, avec principalement des lectures séquentielles (et quelques lectures aléatoires, s'il y a beaucoup de potentiel pour les transactions arrière).
Tempdb peut-être être très légèrement utilisé sur votre serveur SQL. Même s'ils sont utilisés modérément, ils éprouvent pas une grande quantité d'activité d'écriture de fichier de données.

Lorsque vous tenez compte de ces facteurs, vous vous rendez compte que le mettant les journaux tempdb ou transaction sur votre SSDs peut-être pas la meilleure façon de les utiliser. Identifier les portions du sous-système d'e/S qui sont le plus gros goulot d'étranglement de performance pour votre charge de travail. Ceux-ci peuvent être bien des fichiers de données pour une base de données de traitement (OLTP) volatils de transaction en ligne ou un journal de transactions pour une base de données avec une charge de travail lourde insert — ou qu'ils soient bien tempdb. Ce sont les candidats pour les placer sur votre SSDs, plutôt que de cueillette juste une partie du stockage SQL Server sans effectuer toute enquête.

Un autre morceau de mauvais conseils concernant SSDs, c'est que vous pouvez arrêter de se préoccuper de la fragmentation index lors de l'utilisation de SSDs pour stocker les fichiers de données. Ce n'est absolument pas le cas. Il est vrai que les effets de lecture moins efficace à venir de la fragmentation seront atténués quelque peu par la grandement réduite lire latence, mais il y a encore le coût d'e/S plus que nécessaire. Vous pouvez réduire qu'en s'attaquant à la fragmentation.

Ce que la plupart des gens ne considèrent, c'est que la fragmentation n'est pas seulement environ lu à venir. L'un des effets secondaires de ce qui provoque la fragmentation (opérations appelées "page fractionnements") est que le pourcentage d'espace inutilisé sur les pages de données fichier peut augmenter considérablement — de 40 à 50 pour cent (Ceci est appelé la « densité de page »).

Si une grande partie des index de la base de données est fragmentée, puis sans maintenance des index régulièrement les pages de fichier de données vont contiennent beaucoup d'espace vide. En dehors de réduire l'efficacité des e/S et utilisation de la mémoire, qui signifie vous utilisez SSDs chers pour stocker l'espace vide. Ce n'est pas un bon ROI dans le livre de quiconque.

Paul Randal

**Paul S. Randal**est directeur général de SQLskills.com, un directeur régional Microsoft et MVP SQL Server. Il a travaillé sur l'équipe de moteur de stockage SQL Server de Microsoft de 1999 à 2007. Il écrivit 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. 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.