QUESTIONS ET RÉPONSES SQL Dépassement de capacité des lignes, des sauvegardes différentielle et plus encore

Paul S. Randal

mise à q: J'AI récemment niveau une application de s'exécuter sur SQL Server 2005. Une des choses que j'ai tiré parti d'est la possibilité lignes supérieur à octets 8,060 afin que je peut permettre aux utilisateurs de créer plus de champs de données sans recevoir une erreur de SQL Server. Maintenant que cette application est en production, nous rencontrez des problèmes de performances pour certaines requêtes Analyse qui utilisé pour exécuter correctement avant le changement de schéma. J'AI avez vérifié la fragmentation de l'index différents et tout est OK. Pourquoi sont les requêtes exécutées lentement sur SQL Server 2005 ?

A La fonctionnalité que vous utilisez, ligne-dépassement de capacité, est idéal pour autoriser la ligne occasionnelle à plus de 8,060 octets, mais il n'est pas parfaitement pour la majorité des lignes en cours oversized et peut entraîner un déroulement des performances de la requête, que vous rencontrez.

La raison en est que lorsqu'une ligne va devenir volumineux, une des colonnes longueur variable dans la ligne est placée « hors ligne. » Cela signifie que la colonne est extraite de la ligne de la page de données ou d'index et déplacée vers une page de texte. Dans la place de la valeur colonne ancienne, un pointeur est remplacé qui pointe vers le nouvel emplacement de la valeur de colonne dans le fichier de données.

Voici exactement le même mécanisme utilisé pour stocker normales colonnes de MÉTIER (objet volumineux), telles que XML, texte, image ou varchar. Notez que si le schéma de la table contient plusieurs colonnes de longueur variable, il n'y a aucune garantie que la même colonne va être déplacée hors ligne lorsque plusieurs lignes deviennent volumineux.

Ce mécanisme peut créer un problème de performances. Soudain une requête pour extraire une colonne de longueur variable d'une seule ligne dans une table peut être nécessaire une e / S supplémentaires si la colonne a été envoyée hors ligne (à lire dans la page de texte contenant l'emplacement hors ligne de la valeur). Si plusieurs lignes sont oversized, une requête pour extraire la même colonne de longueur variable plusieurs lignes peut avoir imprévisible des performances, selon le nombre de valeurs ont été envoyée hors ligne.

Dans votre cas, une requête d'une analyse de plage ou une analyse de table pour une liste de sélection qui inclut une colonne de longueur variable est subi de faibles performances en raison de dépassement de capacité de ligne et ses effets. Peu importe si les index sont parfaitement fragmentés, lorsqu'une colonne de longueur variable a été envoyée hors ligne, l'analyse précédemment efficace est essentiellement interrompue car une e / S aléatoire est nécessaire pour lire la page de texte contenant la valeur hors ligne.

Dépassement de capacité des lignes est toujours très utile pour les lignes volumineux occasionnelle. Toutefois, si la requête performance est critique, il ne doit pas être un composant largement exploitée dans votre conception.

q: nous avez simplement introduit la mise en miroir de base de données entre deux clusters de basculement comme un moyen d'obtenir géo-redondance pour inférieur le coût de réplication de réseau SAN (Network) de zone de stockage. Les centres de données sont dans la même ville, donc nous vous pouvez utiliser la mise en miroir synchrone. Le problème est que lorsqu'un basculement se produit sur le cluster local, la base de données miroir bascule sur du cluster à distance, qui n'est pas le comportement que nous souhaitons. Comment peut nous empêcher cela ne se produise ? Nous souhaitons le basculement se produise uniquement si le cluster local n'est pas disponible.

A pour une disponibilité accrue, la mise en miroir est configurée avec un serveur témoin afin de basculements se produise automatiquement si l'entité n'est pas disponible. L'idée est que si le cluster local entier tombe en panne, la mise en miroir de base de données est basculement vers le second cluster et l'application peut continuer.

Ce problème se produit si un basculement de cluster se produit. Le basculement est plus long à que la valeur de délai d'expiration par défaut de la mise en miroir de base de données. Le serveur témoin et le serveur miroir (c'est-à-dire l'instance active de SQL Server sur le second cluster) conviennent ils ne peuvent pas voir l'entité, et ensuite le serveur miroir établit un basculement en miroir au second cluster.

Pour empêcher cela, la plus simple consiste à supprimer le serveur témoin de sorte que la mise en miroir de base de données ne pas automatiquement basculement si le cluster local tombe en panne. Bien entendu, cela réduit disponibilité, une humaines puis nécessaires afin d'initier un basculement.

La deuxième option est pour modifier le paramètre de délai d'expiration par défaut de la mise en miroir de base de données. Ceci est le nombre de fois par seconde "pings » qui l'entité doit échouent pour répondre aux avant qu'il est déclaré non disponible. Ce paramètre est appelé le partenaire de délai d'expiration et a une valeur par défaut de 10. La valeur de délai d'attente actuelle de la base de données se trouve à l'aide du code suivant :

SELECT [mirroring_connection_timeout]
  FROM master.sys.database_mirroring 
  WHERE [database_id] = DB_ID ('mydbname');
GO

La valeur du délai d'attente peut être changée en utilisant le code suivant :

ALTER DATABASE mydbname 
  SET PARTNER TIMEOUT <timeoutvalue>;
GO

Pour ce scénario, le délai d'attente partenaire doit être défini que plus que le délai habituel prend un basculement de cluster sur le cluster local. Cela peut un peu plus difficile à déterminer donné la variabilité dans le temps que nécessaire pour exécuter récupération de la base de données en miroir lorsque le basculement de cluster se produit, mais vous devez en mesure de déterminer une limite supérieure. Le problème avec cette méthode est que la valeur du délai d'attente pouvez avoir à être minutes, qui peuvent être pas acceptable pour lorsque se produit un incident réel.

Questions-réponses sur ma stratégie de sauvegarde implique complète et de sauvegardes du journal, mais j'ai entendu que je doit ajouter restaurer les sauvegardes différentielles pour réduire temps. JE prends une sauvegarde complète une fois la sauvegarde le journal d'une semaine et horaire. J'AI essayé Ajout de sauvegardes différentielles quotidiennes, mais une chose étrange, que j'ai remarqué est que les sauvegardes différentielles à la fin de la semaine sont proche à la même taille que la sauvegarde hebdomadaire. J'ai sous l'impression qu'ils sont incrémentielles, comme les sauvegardes du journal. Est-I manquant quelque chose ?

A le misunderstanding ici est autour de la nature des sauvegardes différentielles. Contrairement aux sauvegardes de journaux, les sauvegardes différentielles ne sont pas incrémentielles. Une sauvegarde différentielle contient toutes les extensions de fichier données modifiées depuis la sauvegarde complète précédente (et cela s'applique à base de données, groupe de fichiers et des sauvegardes au niveau de fichiers).

Lorsqu'une étendue (un groupe logique de pages de fichier de données contigu huit) est modifié en aucun cas, il est marqué dans une page de bitmap spécial appelé le mappage différentiel (ou plus communément appelée le mappage diff). Il est une carte diff pour chaque bloc de 4 Go de chaque fichier de données. Lorsqu'une sauvegarde différentielle provient, le sous-système de sauvegarde analyse tous les mappages diff et copie toutes les étendues modifiés, mais les mappages diff ne sont pas rétablis. Cela signifie que si plusieurs étendues sont modifiées entre les sauvegardes différentielles, les sauvegardes ultérieures sont agrandir. Les mappages diff sont réinitialisées uniquement lorsqu'une sauvegarde complète est effectuée.

Si la charge d'application est de sorte que le contenu de la base de données est largement modifié pendant une courte période de temps (par exemple, dans une semaine), une sauvegarde hebdomadaire sera presque la même taille qu'une sauvegarde différentielle a été prise juste avant complet prochaine sauvegarde. Ceci explique le comportement que vous êtes observation.

Vous avez raison de penser que sauvegardes différentielles permettent réduire le temps de restauration dans une situation de récupération après incident. Si la stratégie de sauvegarde hebdomadaires des sauvegardes complètes et les sauvegardes du journal, une restauration instantanée nécessiterait suivantes :

• Prendre une sauvegarde Queue de journal (tous les journaux générés depuis la dernière sauvegarde de journal).
• Restaurer la dernière sauvegarde complète de la base de données.
• Restaurer toutes les sauvegardes du journal, en séquence, depuis la dernière sauvegarde complète de la base de données.
• Restaurez la sauvegarde Queue de journal.

Cela pourrait nécessite beaucoup de sauvegardes de journaux à restaurer, en particulier si l'incident se produit juste avant la prochaine sauvegarde complète est due. (Un scénario Pire signifierait 24 + 24 + 24 + 24 + 24 + 24 + à restaurer de sauvegardes du journal 23!) En ajoutant des sauvegardes différentielles quotidiennes à cette stratégie, la séquence de restauration modifie à ceci :

• Prendre une sauvegarde Queue de journal (tous les journaux générés depuis la dernière sauvegarde de journal).
• Restaurer la dernière sauvegarde complète de la base de données.
• Restaurez la sauvegarde différentielle la plus récente.
• Restaurer toutes les sauvegardes du journal, en séquence, depuis la dernière sauvegarde différentielle.
• Restaurez la sauvegarde Queue de journal.

Cela peut supprimer la nécessité d'un lot de restauration de sauvegardes du journal, en tant que restaurer une sauvegarde différentielle est essentiellement identique restauration toutes les sauvegardes du journal de la période couverte par la sauvegarde différentielle.

Le très pire des cas dans un scénario où une sauvegarde différentielle quotidienne est effectuée serait 23 de sauvegardes du journal, même sur le dernier jour de la semaine. L'un inconvénient de sauvegardes différentielles n'est pas incrémentielle est que pouvoir prendre plus d'espace, mais c'est presque toujours un compromis souhaitable pour réduire le temps de restauration.

q: J'ai un cluster avec basculement à deux noeuds. Chaque nœud exécute une seule instance de SQL Server 2005. Je suis suivant l'option courants du paramétrage de chaque instance pour utiliser uniquement 50 % de la mémoire disponible. Maintenant je rencontre des problèmes de la charge sur les deux instances nécessite davantage de mémoire pour conserver les mêmes niveaux de performances. Si je supprimer la limitation de mémoire ou rendre plus élevé, je pense que je vous rencontrez des problèmes lorsque parmi les instances de bascule et ils sont tous deux exécuté sur qu'un seul nœud. Ce que vous recommandez ?

A Je vous répondre à cette question le cas de deux noeuds, deux instance, mais tous les éléments ci-dessous s'applique également à autres paramétrages multi-instance (clusters de basculement N-1, où se trouvent N nœuds et les instances de SQL Server N-1).

Beaucoup de gens rencontrer une charge élevée (consomme plus de 50 % de la mémoire du serveur) sur les deux instances et ne pas prendre en compte l'effet sur les charges de travail lorsque les deux instances finissent s'exécutant sur un seul nœud après un basculement. Sans toute configuration spéciale, il est possible pour la distribution mémoire entre les instances pour devenir disproportionate, une charge de travail s'exécute correctement et l'autre ralentit à une analyse.

Avec SQL Server 2000, la recommandation consiste à limiter chaque instance à un maximum de 50 % de mémoire de nœud de cluster. C'est parce que le gestionnaire de mémoire dans SQL Server 2000 ne répond pas à la pression sur la mémoire — si SQL Server prend, par exemple, 80 % de mémoire du nœud, il est ne lui en pas. Dans une situation de basculement, cela signifie une autre instance simplement démarrage aurait 20 % de la mémoire disponible. En limitant les deux instances à un maximum de 50 % de mémoire d'un nœud, une instance de basculement est garanti que 50 % de la mémoire. Le problème avec cette est que la charge de travail dans chaque instance est également limité à 50 % de la mémoire.

Avec SQL Server 2005 (et SQL Server 2008), le Gestionnaire de mémoire peut répondre à pression de mémoire le nombre maximal de 50 % est plus appropriée. Mais sans un type de restriction, si deux instances exécutez sur un nœud de cluster, ils peuvent pressure eux jusqu'à ce qu'une distribution disproportionate mémoire est atteinte.

La solution consiste à définir chaque instance pour qu'une quantité minimale de mémoire afin qu'ils ne peuvent pas être pressured pour libérer trop de mémoire. Un paramètre courant d'une configuration à deux noeuds, deux instance est pour que chaque instance configuré pour un minimum de 40 % de la mémoire. Cela signifie que lorsque chaque instance est exécuté sur un noeud distinct, ils peuvent consommer autant de mémoire qu'ils le souhaitent. Lorsqu'un basculement se produit, chaque instance est garanti qu'une certaine quantité de mémoire pour conserver un niveau d'ensemble de performances de charge de travail, avec un peu gauche sur à être partagé entre eux. Bien que cela signifie que les performances des charges de travail peuvent ignorer dans une situation de basculement (comme prévu), ils ne sont pas être limitées tout pour la vaste majorité du temps lorsque chaque instance exécute sur un nœud de cluster distinct.

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.