Forum aux questions sur SQL : Les données dynamiques et reprise après sinistre

Les solutions de ce mois pour réussir avec SQL couvrent l’ensemble des techniques, de l’extension d’un tempdb au clustering d’une énigme, jusqu’aux plans de récupération d’urgence incomplets.

Paul s. Randal

Remplissage de l'espace

Q. Un des serveurs de production pour laquelle je suis responsable a un problème. Base de données tempdb croît très larges tous les jours. Il s'agit d'un problème relativement nouveau. Je ne vois pas toute différence entre le nombre de connexions au serveur ou l'utilisation de la mémoire. Comment puis-je contrôler la situation afin de déterminer à l'aide de l'espace de tempdb ?

R : Il existe quelques raisons l'utilisation de tempdb de croître :

• Utilisation du système de contrôle de version (pour l'isolation d'instantané ou les opérations d'index en ligne, par exemple) peut provoquer la banque des versions dans tempdb de croître.
• Un plan de requête peut changer en raison de statistiques périmées, qui à son tour entraînerait un opérateur de plan de requête qui résulte en un déversement de grande taille de mémoire dans tempdb.
• Quelqu'un a peut-être déployé le nouveau code d'application qui utilise des tables temporaires pour stocker partiellement traitée les données.

Quel qu'il soit, il existe des moyens simples pour vous permettre de suivre ce qui se passe. La première chose à faire est d'examiner l'utilisation d'espace tempdb avec le sys.dm_db_file_space_usage de la vue de gestion dynamique (DMV). Si vous capturez les résultats de cette DMV toutes les 30 secondes, par exemple, vous serez en mesure de savoir si l'utilisation de l'espace supplémentaire à partir de la banque des versions, les objets utilisateur ou les objets créés pour faciliter le traitement des requêtes.

S'il s'agit de la banque des versions qui occupe tout l'espace, vous pouvez descendre à l'aide des sys.dm_tran_top_version_generators de la DMV. Vous devrez joindre avec sys.partitions et sys.indexes pour obtenir des informations véritablement utiles hors de celui-ci, mais qui vous permettra de savoir quelles sont les tables ont la plupart des versions en cours de génération.

S'il est tout autre trop de place, vous pouvez consulter en capturant les résultats à partir de sys.dm_db_task_space_usage à une fréquence similaire. Puis joindre le DMV avec sys.dm_exec_requests pour savoir quelles connexions et les requêtes sont trop de place.

S'il s'avère être une longue procédure stockée, vous devrez instrumenter la procédure à la quantité d'espace tempdb qu'utilise afin de pouvoir travailler les instructions à l'intérieur de la procédure sont les causes de sortie périodiquement. J'ai eu à faire plusieurs fois sur les systèmes clients.

Vous pouvez trouver beaucoup plus d'informations sur l'utilisation de ces DMV dans le livre blanc «utilisation de tempdb dans SQL Server 2005. » (Cet article s'applique également aux versions ultérieures de SQL Server.)

Bonne Clusters

Q. On m'a demandé de concevoir le schéma d'une base de données qui stocke les données d'une nouvelle application. J'ai lu toutes sortes de conseils sur le choix des clés de l'index ordonné en clusters « bon » pour mes tables. Pouvez-vous expliquer ce qui rend une clé d'index ordonné en clusters « bon » et son importance tellement ?

R : Il s'agit d'une question complexe et il est presque impossible de manière exhaustive répondre ici. En bref, une clé d'index ordonné en clusters « bonne » est celui qui est été soigneusement choisie afin de réduire les performances médiocres et l'espace gaspillé. Les quatre qualités d'une clé d'index ordonné en clusters bonne sont : étroit, statique, unique et sans cesse croissante :

• Affiner (occupent octets aussi peu que possible): tous les enregistrements d'index non cluster incluent la clé d'index ordonné en clusters. Il est grande, les plus d'informations en double d'espace dans les index non cluster occupe.
• Statique (immuable): les modifications apportées aux valeurs de clé sont coûteuses. SQL Server est une mise à jour de clé comme une suppression + opération d'insertion (consultez mon blog ici), et n'importe quel moment une clé d'index ordonné en clusters est mis à jour, toutes les lignes correspondantes dans les index non cluster également doivent être mis à jour. Modifications clés peuvent également conduire à un espace vide dans les pages de fichier de données si cette position clée dans l'index n'est pas utilisée à nouveau.
• Unique : cela évite d'avoir à SQL Server d'ajouter une colonne masquée de quatre octets pour les valeurs de clé en double « uniquify », ce qui rend la clé plus large.
• Croissante: le motif de l'insertion de nouveaux enregistrements entraîne d'insertions aléatoires dans l'index ordonné en clusters qui peut provoquer des opérations coûteuses de fractionnement de la page. Cela conduit à une fragmentation logique et l'espace gaspillé sur les pages de fichiers de données.

N'étant donné ces qualités d'une clé de bons index ordonné en clusters, il est souvent une clé naturelle répondant (par exemple, une dérivée de données de la table), afin que vous deviez utiliser une clé de substitution (par exemple, une colonne de table artificiel). Une colonne BIGINT IDENTITY est un exemple d'une clé de substitution bon. Lire plus des explications détaillées et les justifications de la catégorie de blog de Kimberly Tripp Clé de cluster.

Préparer le pire

Q. À la suite des récentes tremblements de terre en Nouvelle-Zélande et au Japon, j'examiné notre plan de reprise après sinistre et constaté qu'il a réellement obsolète. J'ai essayé sans succès obtenir notre société d'exigences et le plan de test. Ils ne simplement que nous aurons jamais un sinistre. Pouvez-vous me donner quelques conseils sur la façon d'aborder ce avec la gestion ?

R : Je suis heureux de savoir que vous analysez proactive de votre stratégie de reprise après sinistre à la suite de ces catastrophes récentes. De nombreuses entreprises sont complaisance et l'attitude que vous décrivez votre question. Bien que les catastrophes naturelles à grande échelle sont relativement rares, plus les problèmes localisées semblable à la construction des incendies ou des coupures de courant sont relativement courants et une société ne doit pas supposer qu'il est insensible aux défaillances aléatoires.

Même si vous ne parvenez pas gestion de votre côté, il existe un grand nombre de tests que vous pouvez le faire vous-même, tels que la restauration des copies de bases de données à partir de sauvegardes. Cette commande teste l'intégrité des sauvegardes et votre stratégie de sauvegarde et vous assurer que le temps de restauration répond aux exigences des temps d'indisponibilité admissible maximale pour toute base de données particulière. Très souvent, c'est le premier problème constaté lors des tests de stratégie de reprise après sinistre. Volumes de données augmentent au fil du temps et restaurer temps augmente considérablement.

Autres parties de la stratégie de reprise après sinistre sont beaucoup plus difficiles à tester vous-même, tels que basculer sur une base de données mise en miroir de partenariat ou un cluster de basculement. Ces deux nécessitent un temps d'inactivité de l'application (les deux cas de basculement et de restauration automatique).

Dans la mesure convaincant gestion est concerné, demandez-lui si elles tomberaient plutôt hors de la stratégie de reprise après sinistre ne fonctionne pas pendant un test planifié avec tout le personnel disponible pour vous aider à la récupération ou en cas de sinistre de real à 2 heures un jour férié lorsque seul le personnel de la NU-minimum est sur-main.

Il existe une multitude d'incidents hautement autant d'entreprises victimes des interruptions dans la mesure où une stratégie de reprise après sinistre était insuffisante. Gestion souhaite-t-elle leur société pour être celle qui suit dans l'actualité ? Qui peut sembler melodramatic, mais c'est un point équitable.

Récupération d'urgence consiste à réduire le coût lié à l'entreprise et ses clients. Si les clients subissent en raison d'une panne ou perdent la foi dans la capacité de la société pour récupérer rapidement, ils peuvent prendre leurs activités ailleurs. Évidemment, cette méthode nuit aux ligne du bas de la société.

En tant que spécialistes, nous devons demander à la gestion de considérer les catastrophes informatiques en termes de l'impact financier sur la société. J'ai trouvé il s'agit d'une tactique efficace en persuadant celui-ci de gestion d'investir du temps et argent dans lié à la modernisation et le test de la stratégie de reprise après sinistre. En savoir plus sur cette méthode dans mon billet de blog récents ici.

Réduction des coûts

Q. J'aimerais utiliser la fonctionnalité de compression de données dans SQL Server 2008 afin de réduire nos coûts de stockage, mais j'ai lu seulement pour les entrepôts de données et que je vais entraînent des problèmes de performances considérables si vous essayez d'utiliser dans une transaction en ligne (OLTP) système de traitement. Est-ce vrai ?

R : Vous avez raison que la fonctionnalité de compression de données était initialement destinée data warehouse. La compression des données réduit la taille des enregistrements de table et index. Cela signifie que les enregistrements plus tenir sur une page de fichier de données de 8 Ko et donc moins pages de fichier de données sont requises pour stocker les données sur disque. Cela se traduit plus petits espace disque requis pour la base de données contenant les données compressées, qui à son tour peuvent entraîner des économies que moins de stockage de classe entreprise est requise.

Le compromis, est bien entendu, que les données doivent être décompressé avant utilisation. Les données ne sont pas décompressées lorsqu'elles sont lues dans le pool de tampons SQL Server (en mémoire cache des pages de données fichier). Il est uniquement décompressé lorsqu'il est réellement nécessaire pour répondre à une requête. Décompression utilise des ressources du processeur, un compromis est l'utilisation de l'espace par rapport aux ressources du processeur.

Un entrepôt de données classique a une grande quantité de données (pense que des centaines de gigaoctets à plusieurs téraoctets). Le modèle d'accès pour ces données est généralement une grande quantité de données lues dans le pool de tampons, traité une seule fois et puis ne pas réutilisées pour long suffisamment qui il a supprimé de la mémoire.

Avec ce modèle d'accès, il est judicieux de réduire le nombre d'opérations de lecture d'e/S en compressant les données à une taille bien inférieure. Cela nécessiterait moins pages fichier de données SQL Server pour stocker et moins d'opérations d'e/S pour lire ces pages. Ceci conduit généralement à une réalisation plus rapide de ces types de requêtes. Alors un compromis est la vitesse des requêtes par rapport aux ressources du processeur (pour la décompression des données).

Si vous envisagez une charge de travail OLTP, pose généralement volatilité des données beaucoup plus élevée que dans un data warehouse. Cela signifie que si vous utilisez la compression des données, vous serez facturé pour un coût élevé de l'UC en raison de la constante décompression de données en cours de lecture et de compression de données inséré ou mis à jour. Vous devrez examiner plus attentivement les compromis lorsque vous envisagez la compression des données pour une base de données OLTP.

Retour à la question, bien que la compression des données initialement visait à entrepôts de données, de nombreux clients de SQL Server ont constaté qu'ils ont une grande quantité d'UC « salle de tête » sur leurs serveurs. Le processeur supplémentaire et potentiellement plus longs temps d'exécution de requête pour obtenir le gain de place importants et le stockage économies associées à l'aide de la compression des données abordable. Compression de données pouvez être utile pour les environnements OLTP. Assurez-vous simplement que évaluer les coûts de performance et le gain de place pour votre charge de travail avant de passer en production.

Pour réaliser des économies d'espace, vous pouvez utiliser la procédure sp_estimate_data_compression_savings pour vous donner une idée des économies de pourcentage que vous pouvez vous attendre. Il est important d'effectuer cette opération car l'activation (ou la désactivation de) la compression des données est effectuée à l'aide d'une opération de reconstruction. Cela peut être coûteux en soi. Pour plus d'informations, consultez le livre blanc «la Compression des données : Stratégie, de la planification de capacité et de meilleures pratiques. »

**Paul s. Randal**est le directeur général de SQLskills.com, directeur régional Microsoft et un serveur SQL Server MVP. 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 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. Blogs HE à SQLskills.com/blogs/paul, et vous pouvez lui trouver sur Twitter à twitter.com/PaulRandal.

Contenu associé

• Forum aux questions sur SQL : Les goulots d'étranglement et les journaux de transactions
• Forum aux questions sur SQL : Maintenance des journaux et des index
• Forum aux questions sur SQL : Réduction, de la croissance et la restructuration de bases de données