SQL q & A: défragmentations et des catastrophes

Article
08/24/2016

Les journaux d'erreur et les fichiers de base de données temporaires peuvent rapidement échapper à votre contrôle. Les tactiques de configuration suivantes peuvent vous aider.

Paul s. Randal

Par défaut pour que la défragmentation

Q. J'ai été adressage certains problèmes de maintenance de base de données que nous avons sur notre serveur, et j'envisage la fragmentation des index. Au lieu de passer du temps à essayer de comprendre un facteur de remplissage pour chaque index, il serait plus facile de simplement définir le facteur de remplissage par défaut pour cette instance ? Existe-t-il des inconvénients pour y parvenir ?

**R :**Je ne recommande généralement définissant le facteur de remplissage à l'échelle de l'instance à tout sauf la valeur par défaut : 100 pour cent. La modification de ce paramètre peut entraîner l'espace inutilisé dans la base de données.

Lorsque vous créez un index sur une table, généralement qu'un seul de ces correspondra le motif d'insertion de table. Tous les autres index est inévitablement fragmentés. Tant que le modèle d'insertion est ajout uniquement (aucune insertions aléatoires d'avoir une clé aléatoire ne comme un identificateur global unique [GUID]), il est judicieux pour éviter d'avoir le plus grand index fragmenté.

Le plus grand index est toujours l'index ordonné en clusters, comme l'index ordonné en clusters est la table. Ce fichier contient tous les enregistrements de données avec toutes les colonnes de table. Il est judicieux de l'index ordonné en clusters agir n'obtenir fragmenté. Suppression de la fragmentation de l'index ordonné en clusters sera plus coûteuse (en termes de journal de transaction, l'heure et espace disque) que pour tout autre index.

Comme l'index ordonné en clusters ne généralement obtenir fragmenté, il est donc logique que le facteur de remplissage est définie sur 100. Il nécessitera un espace supplémentaire. Il peut y avoir des raisons autres que l'index ordonné en clusters obtient fragmenté (telles que les mises à jour des colonnes de longueur variable qui rendent plus longues lignes de la table), mais qui est généralement vrai.

Si vous attribuez à fillfactor de l'échelle de l'instance à l'exception de 100 pour cent, vous devez définir spécifiquement il de tous les index ordonnés en clusters n'obtenir fragmentés. Si vous ne le faites pas, ils utilisent le nouveau fillfactor à l'échelle de l'instance et laisser d'espace lorsqu'ils sont reconstruits. Ces déchets essentiellement l'espace dans la base de données. Vous pouvez choisir pour ce faire, mais il n'a généralement pas considéré comme une meilleure pratique.

Qu'il existe une variété d'index dans une base de données, il est rare de trouver une valeur fillfactor unique qui est optimale pour tous les index. Il est généralement préférable du point de vue la facilité de gestion à ignorer le facteur de remplissage à l'échelle de l'instance. Vous pouvez également définir l'il inférieur spécifiquement sur ces index uniquement qui en ont besoin.

Redémarrez les réponses

Q. Certains serveurs dans notre environnement sont rarement redémarrés. Alors que c'est une bonne chose bien des égards, cela ne signifie que le journal des erreurs de SQL Server peut croître démesurément grand. Il semble à remplir avec des dizaines de milliers de messages d'achèvement de sauvegarde pour lequel je n'ont aucune utilité. Y a-t-il quelque chose que je peux faire pour rendre les journaux d'erreurs plus petits et plus faciles à gérer ?

**R :**Il existe deux possibilités : Couper vers le bas sur les messages de sauvegarde et de configurer la gestion du journal des erreurs. Chaque fois qu'une sauvegarde est terminée, il écrit une entrée dans le journal des erreurs. Il s'agit de peu d'utilité. Réussite de sauvegarde est la norme, pas une erreur.

Il existe un indicateur de trace documentés : indicateur de trace 3226 — qui empêchera les messages de réussite de la sauvegarde. Vous devez ajouter que dans la liste des indicateurs de trace de démarrage (en utilisant le Gestionnaire de Configuration de SQL Server). Pour activer cette option sans avoir à arrêter et redémarrer le SQL Server, vous pouvez également activer cette trace marquer à l'aide de la commande « DBCC TRACEON (3226, -1). » -1 Signifie appliquer globalement l'indicateur de trace. L'équipe SQL Server vécu de l'indicateur de trace en 2007.

Vous pouvez également configurer votre gestion du journal des erreurs au sein de Management Studio de SQL Server (SSMS). Ouvrez l'Explorateur d'objets dans SSMS et connectez-vous à SQL Server. Développez la zone de gestion, avec le bouton droit sur journaux de SQL Server et sélectionnez Configurer. Lorsque la boîte de dialogue Configurer les journaux d'erreurs de SQL Server s'affiche, activez l'option « Limiter le nombre de fichiers journaux des erreurs avant d'être recyclés ». Journaux d'erreurs Select 99. Ce paramètre remplace le nombre par défaut de six journaux d'erreurs maintenue en permanence.

La dernière étape de configuration est ce qui limite la taille de chaque journal des erreurs. Demandez à SQL Server pour créer un nouveau journal des erreurs chaque jour (appelé « cyclique » le journal des erreurs). Cela en créant un travail d'Agent de SQL Server quotidien qui effectue simplement « EXEC sp_cycle_errorlog. » Après cela, vos journaux d'erreur doit être facile à gérer.

Maîtriser la base de données Tempdb

Q. Le volume de données a augmenté considérablement sur les deux dernières années. Notre tempdb semble toujours pour remplir selon le lecteur est activé. Nous exécutons certaines requêtes complexes, nous avons donc beaucoup de l'utilisation de tables temporaires. Vous donner des conseils sur la façon de réduire l'utilisation de tempdb ?

R: l'utilisation de Tempdb est un problème pour les utilisateurs SQL Server pérenne. Il est uniquement un seul tempdb pour chaque instance de SQL Server, afin que vous devez être prudent comment il est utilisé.

Une des utilisations plus courantes de tempdb est à partir de tables temporaires. Ces tables permettent de résultats intermédiaires des agrégations complexes ou de jointures pour faire persister et deviennent partie intégrante d'une requête plus grande. Ceci est souvent un moyen efficace de décomposer une requête longue et complexe, mais il n'est pas toujours le cas. Parfois les développeurs habituer à l'utilisation des tables temporaires par défaut, au lieu d'effectuer des tests de performances pour afficher ou non à l'aide d'une table temporaire est plus efficaces.

Le problème de création d'un résultat intermédiaire défini dans une table temporaire est qu'elle peut interrompre le pipeline efficace des données par l'intermédiaire d'une requête complexe. Il force l'optimiseur de requêtes à traiter la création de la table temporaire ou de la population séparément à partir des opérations suivantes, utilisez la table temp. L'optimiseur de requêtes affiche parfois un plan de requête plus efficace si elle n'utilise pas une table temporaire. Il peut également exécuter plus efficacement avec une construction de requête différent comme une table dérivée ou une Expression de Table commune.

Si une table temporaire est le moyen le plus efficace pour décomposer votre requête, il existe deux possibilités pour réduire l'utilisation de tempdb :

Créer des index appropriés : Assurez-vous que seuls index créés sur la table temporaire sont réellement utiles pour la poursuite du traitement (ceci en analysant et en vérifiant qui indexe le plan de requête est à l'aide de la vérification). Index non ordonnés en clusters créés sur chaque colonne de table temporaire sont peu susceptibles d'être utiles. Assurez-vous également que les index sont créés après le remplissage de la table temporaire, afin qu'ils puissent statistiques pour aider l'optimiseur de requêtes à utiliser l'index.
Réduire la taille de la table temporaire : Assurez-vous que les seules colonnes rendus persistants dans la table temporaire sont ceux utilisés pour un traitement supplémentaire, sinon elles sont une perte complète d'espace. Les tables temporaires sont souvent créées avec une instruction SELECT * construction non pensé que pour les colonnes qui sont réellement nécessaires. Si vous traitez un jeu de résultats volumineux, cet espace peut additionner vraiment.

Mauvaises sauvegardes

Q. Semaine dernière notre SAN s'est bloqué. Nous a subi des pertes de données dans notre base de données de production. Les sauvegardes les plus récentes ont été stockés sur SAN avec les fichiers de base de données, et ceux ont été endommagés. Nous avons également découvert notre légèrement anciennes sauvegardes ont été endommagés ainsi — parfois la corruption de la même. Comment nous pouvons pour éviter cette situation à l'avenir ?

**R :**Il s'agit d'une situation courante — aucune bonne sauvegarde et un point unique de défaillance, ce qui finalement entraîne des pertes de données.

Le premier problème est que vos sauvegardes sont endommagés. Vous devez implémenter une stratégie de test afin de déterminer si la sauvegarde est endommagée ou contient une base de données endommagée de sauvegarde. Il existe plusieurs parties :

Mettre en œuvre une vérification de cohérence réguliers pour la base de données de production. Cela signifie que la commande DBCC CHECKDB sur la base de données de production lui-même ou une copie de la base de données en cours d'exécution.
Activez les checksum de page sur la base de données de production (si pas déjà activée). Utilisez l'option avec total de contrôle sur toutes les sauvegardes. Cela va tester checksum de page comme pages de fichier de données sont lues pour inclusion dans la sauvegarde, qui permet d'éviter la création d'une sauvegarde avec une base de données endommagée.
Implémenter un contrôle de validité des sauvegardes une fois qu'ils sont prises. Cela implique d'effectuer la sauvegarde vers une autre instance de SQL Server et de restauration (à l'aide de l'option avec total de contrôle à nouveau), ou au moins exécuter RESTORE VERIFYONLY de la sauvegarde à l'aide avec les CHECKSUM. L'objectif est de restaurer la base de données et exécuter DBCC CHECKDB. Il s'agit également d'un bon moyen de déchargement de la charge de travail vérification de la cohérence du serveur de production.
Implémentez un planning de test régulier où vous exercer à la restauration de la base de données de production à partir de sauvegardes disponibles.

Une autre façon de tester facilement la validité des sauvegardes du journal des transactions consiste à créer un envoi de journaux secondaires. Ceci restaurera constamment les sauvegardes du journal des transactions à partir de la production. Il fournit également une copie redondante de la base de données.

Le deuxième problème est que vos sauvegardes sont stockées sur le sous-système d'e/S même en tant que la base de données elle-même. Cela signifie que vous ne disposez d'aucune protection contre une défaillance du sous-système d'e/S.

Vous avez besoin de conserver des copies de toutes les sauvegardes sur un sous-système d'e/S distinct à partir de la base de données de production, dans l'idéal, ce serait dans un emplacement totalement distinct. Aident de copies locales des sauvegardes avec reprise après sinistre rapide et copies distantes assurer la reprise après sinistre est toujours possible si le stockage local est endommagé ou détruit.

Une autre chose à prendre en considération est encourageant des exercices de récupération après sinistre régulières. Supposons qu'un sinistre a heurté et fonctionne par le biais de votre plan de récupération pour déterminer son efficacité. Mon article d'avril 2011, «SQL Server : protéger les données à tout prix, » explique ceci en matière de gestion.

Préparation de récupération après sinistre est comme la sécurité. L'une des lignes directrices souvent répétées est « défense approfondie. » Plus d'options qu'ont pour la récupération et les problèmes potentiels de plus pour anticiper et am? ioration, plus vous pourrez récupérer en cas de sinistre dans vos accords de niveau de service interruptions de service et de perte de données.

Paul S. Randal

**Paul s. Randal**est le directeur général de SQLskills.com, directeur régional Microsoft et MVP SQL Server. Il a travaillé sur le moteur de stockage SQL Server 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 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 blogue SQLskills.com/blogs/paul et vous pouvez trouver lui sur Twitter à Twitter.com/PaulRandal.