SQL Q & A: En quête de performances

  • Article

Réduire la charge de travail et des fonctions de mise en miroir de lissage est toujours une bonne idée, mais la réduction des bases de données n'est pas.

Paul S. Randal

Contournement de la charge de travail

Q. Je travaille avec une équipe de développeurs qui changent d'une application peut utiliser SQL Server pour stocker les données. Les données a été précédemment stockées localement sur les ordinateurs clients. Pouvez-vous me donner une liste de considérations pour les développeurs donc ils peuvent conduire le montant possible moins de charge de travail de SQL Server ?

**R :**En nous efforçant de faire la demande d'appel vers le bas à la couche de données autant que possible, vous prenez une excellente approche. En mettant l'accent sur l'application est malheureusement atypique. La principale chose à garder à l'esprit est d'avoir l'application de récupérer des données SQL Server le moins possible. Quand il récupère les données, l'ai récupérer uniquement les données dont il a besoin aussi efficacement que possible.

Voici quelques conseils pour vos développeurs d'examiner la manière dont l'application interroge les données SQL Server. Prêter attention à ces permettra d'éviter la charge de travail inutile et un impact négatif sur le CPU, mémoire et e/s :

  • **Traitement :**Pour les données qui sont tirées de SQL Server, l'application doit évite de traiter l'une ligne de données à la fois. Ceci est communément appelé RBAR, ou ligne par ligne angoissante, traitement. N'importe quel moment, que SQL Server envoie les données à l'application, il a un thread en attente de la demande de reconnaître les données envoyées à travers. Traitement RBAR peut conduire à hautes ASYNC_NETWORK_IO temps d'attente dans SQL Server. L'application doit mettre en cache localement les données entrantes et rapidement réponse à SQL Server qu'il a données.
  • **Filtrage :**L'application devrait éviter de filtrer les données localement avant d'utiliser ou d'afficher ces données. Il est beaucoup plus efficace pour pousser le prédicat de filtre vers le bas pour SQL Server et avoir le minimum de données retournées à l'application. SQL Server est très bonne au filtrage des données, étant données les bon index non cluster pour prendre en charge les prédicats de filtre.
  • **One Size Fits tous (OSFA) :**Minimiser le nombre de colonnes de la table retournée aux seules personnes nécessaires. Les développeurs doivent également éviter d'essayer de construire une boîte de dialogue « one size fits all ». À l'aide d'une liste de sélection ciblée au lieu de SELECT * permettra de réduire la quantité de données traitées et retournées. Avec moins de colonnes demandés, SQL Server peut également avoir plus de façons de se rendre à ces données, ce qui amélioreraient les performances optimale.
  • **De commande :**Si les données renvoyées n'a pas besoin d'être triés avec une clause ORDER BY, alors évitez de spécifier ORDER BY que cela peut découper une opération de tri. Les opérations de tri peuvent souvent être coûteuses car ils finissent par nécessitant un coûteux déversement de tri dans tempdb.
  • **Juste au cas où :**Reporter les opérations SELECT jusqu'à ce qu'ils sont vraiment nécessaires. Si une application émet une instruction SELECT juste au cas où, l'utilisateur clique sur un bouton de l'application. Il pourrait ensuite être gaspillé traitement. Il est préférable d'attendre jusqu'à ce que le bouton est poussé en fait avant d'émettre la sélectionner, supprimer tous les traitement lorsque le bouton n'est pas enfoncé.
  • **Envisager la mise en cache :**Si vous effectuez une requête encore et encore les mêmes données, mettre en cache localement et d'émettre une instruction SELECT à nouveau les données changent. C'est idéal lorsque les données ne changent pas fréquemment ou si vous ne nécessitent pas des données à la minute.

Compte tenu de ces facteurs peut avoir un effet profond sur la quantité de travail que SQL Server doit faire, en particulier si un seul changement dans la logique de requête de demande est multiplié par des centaines ou des milliers d'instances de l'application en cours d'exécution simultanément.

Demandez à votre équipe de développement d'application d'examiner comment l'application est à l'aide de SQL Server. Cela pourrait grandement bénéficier de votre charge de travail existante. La cause première des problèmes de performances est trop souvent supposée pour être SQL Server, au lieu de la façon dont l'application est à l'aide de SQL Server.

Miroir, miroir

Q. Nous avons utilisé la mise en miroir de base de données depuis plusieurs années maintenant. Nous avons récemment eu des problèmes. Nous avons réalisé un basculement, et la base de données miroir prend plusieurs heures pour se mettre en ligne, qui était tout à fait inattendue. Y a-t-il des compteurs de performance que nous pouvons surveiller pour dire si cela se produit à nouveau ?

**R :**Mise en miroir de base de données est devenu extrêmement populaire car il a été correctement introduit dans SQL Server 2005 SP1. Cependant, il y a un problème omniprésent sur les systèmes clients. Il semble être une hypothèse que dès que vous implémentez mise en miroir de base de données, vous pouvez en toute sécurité l'oublier et s'en prévaloir pour fonctionner parfaitement lorsqu'une défaillance se produit, il apportera toujours la base de données protégée en ligne sur le serveur miroir sans perte de données et les temps d'arrêt minimal.

Bien que cela puisse être vrai dans certains cas, c'est une supposition dangereuse. Pour réduire les risques de catastrophe, il est absolument essentiel de surveiller la taille de la file d'attente de l'envoi et la rétablir d'une session de mise en miroir :

  • La taille de la file d'envoi montre combien journal des transactions a été généré sur le serveur principal, mais n'a pas encore été envoyée au serveur miroir. S'il n'est pas zéro, cela signifie qu'il ne peut y avoir un basculement automatique et de l'état de mise en miroir n'est pas synchronisé. En outre, la taille de la file d'envoi indique le montant des pertes de données qui seront produit si la base de données principale subit un désastre. Vous devez surveiller pour s'assurer que la taille de la file d'envoi ne dépasse pas votre perte maximale de données autorisée Service Level Agreement (SLA) — ou objectif de Point de récupération (RPO) — pour la base de données en miroir.
  • La taille de file d'attente de restauration par progression montre combien journal des transactions existe dans la base de données miroir qui n'a pas encore été relu sur la base de données miroir. N'oubliez pas, les enregistrements du journal doivent juste être durci — ne pas relus — sur le lecteur du journal de la base de données miroir. C'est fait comme un processus continu sur le serveur miroir. En cas d'un basculement en miroir, vous ne peut pas accéder à la base de données miroir jusqu'à ce que tous les enregistrements du journal des transactions dans la file d'attente de restauration par progression ont été relus sur la base de données miroir. Essentiellement, cela signifie qu'une récupération sur incident se déroule. Plus la file d'attente REDO, plus un basculement aura. N'oubliez pas que dans l'édition entreprise, récupération rapide entre en jeu et la base de données devient disponible après la phase REDO de la récupération est terminée, mais avant l'annulation phase commence. Vous devez surveiller pour s'assurer que la taille de la file d'attente de restauration par progression ne dépasse pas votre temps d'arrêt maximal admissible SLA — ou objectif de temps de récupération (RTO) — pour la base de données en miroir.

La plus ancienne transaction non envoyée est un autre moyen pour contrôler le nombre d'instantanés de perte de données que vous subirait en cas de sinistre de la base de données principale. Elle s'applique à tous les modes de mise en miroir de base de données, parce que même si vous utilisez la mise en miroir synchrone, le principal et le miroir peuvent se faire hors circuits ou vous pourriez suspendre la mise en miroir.

Vous pouvez surveiller les files d'attente SEND et rétablir l'utilisation du moniteur de mise en miroir de base de données dans SQL Server Management Studio pour définir les alertes. Vous pouvez également surveiller directement à l'aide des mise en miroir de base de données objet compteurs perfmon journal envoyer file d'attente Ko et refaire la KB de file d'attente.

Si vous trouvez la taille de file d'attente de restauration par progression de plus en plus, cela implique que le serveur miroir ne peut pas suivre la quantité du journal transmis par le serveur principal. Il pourrait être il y a la charge de travail supplémentaire sur le serveur miroir qui empêche le journal de base de données miroir de relecture aussi vite que possible. Il peut également être que le matériel physique sur le serveur miroir n'est pas aussi capable que celle sur le serveur principal.

Rétrécir vers le haut

Q. L'un de nos fournisseurs d'applications est obligatoire que nous courons cohérence régulière de la base de données (DBCC) SHRINKDATABASE opérations contre les bases de données application et tempdb de contrôle. Le vendeur insiste pour que cela est nécessaire pour assurer un fonctionnement correct. Vous pouvez me donner quelques conseils ?

**R :**Cette question revient assez régulièrement. Un fournisseur de l'application peut refuser de vous permettra de supprimer les opérations de compactage régulier parce qu'ils sont considérés comme « nécessaires à la performance. » Réduction des bases de données entraîne une fragmentation de l'index, consomme beaucoup de ressources CPU et i/o. Il génère également un lot de journal des transactions. Cela peut provoquer des problèmes de base de données mise en miroir, les groupes de disponibilité AlwaysOn, réplication et tout ce qui doit expédier les enregistrements du journal autour. Il y a certaines circonstances, toutefois, où les opérations de compactage ponctuels sont nécessaires.

Bases de données ne doivent jamais être réduits régulièrement. Diminue régulièrement les bases de données est une mauvaise chose à faire car si la base de données augmente à plusieurs reprises après être rétréci, tout ce qui réduit le travail est complètement gaspillage d'efforts. C'est s'apparente à ce que la réduction automatique activée pour la base de données.

De nombreuses équipes de demande du vendeur ne sais pas que ces choses à propos de rétrécissement. C'est souvent parce qu'ils ont porté l'application depuis un autre système de base de données et sont peu enclins à écouter n'importe qui essayant d'informer sur le fonctionnement de SQL Server.

Je vais parfois impliqué avec un client et l'équipe de vendeurs d'application. Les justifications de l'équipe de vendeurs d'application sont généralement le long des lignes de ce qui suit (paraphrase) :

  • Les index dans la base de données sont déjà fragmentées, ce retrait ne fait pas plus mal.
  • De personne jamais plaint de performance avant, alors pourquoi êtes-vous ?
  • Nous devons avoir un psy régulier parce que les opérations que nous ne provoquent pas la base de données d'étendre beaucoup et clients tiennent à leur retour d'espace disque.
  • Nous devons réduire tempdb car les opérations nous entraînent à se développer continuellement.

Aucun d'entre eux sont des motifs valables pour diminue régulièrement les bases de données. En fait, il est répertorié dans article KB 307487 que rétrécissement tempdb lorsqu'il y a l'activité des utilisateurs peut entraîner une corruption de tempdb. Aussi, le "travailler avec Tempdb dans SQL Server 2005" livre blanc (applicable à toutes les versions) stipule que : « Réduction fichiers n'est pas une pratique recommandée. »

N'importe quel moment qu'un fournisseur fait rétrécir est nécessaire, elle démontre soit une méconnaissance fondamentale de comment gérer SQL Server ou une carence dans le comportement de l'application enveloppé par une diminution régulière. La meilleure façon de s'engager avec des fournisseurs qui ont mandat de réduction régulière doit pointer à l'article KB de Microsoft ou le livre blanc. Puis ils ne peuvent pas faire valoir qu'ils vous respectent les meilleures pratiques Microsoft.

Malheureusement, il n'y a aucun moyen d'empêcher les opérations de compactage s'ils êtes vendeur obligatoire. Suppression de l'opération de réduction pourrait entraîner l'annulation un contrat de support. La meilleure chose que vous pourriez faire, c'est avoir un travail d'Agent SQL Server qui s'exécute toutes les 15 secondes à la recherche de connexions qui diminuent les bases de données et puis de les tuer. Tuant une opération de réduction ne causera pas la corruption ou autres problèmes. Cette approche peut vous aider à rester au sein de la Convention de soutien, tout en empêchant également les problèmes de performances sur votre serveur de production.

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 l'équipe SQL Server Storage Engine 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. Randal est un expert de récupération après incident, haute disponibilité et de maintenance de base de données et présente fréquemment à des conférences dans le monde entier. Il blogs à SQLskills.com/blogs/paul, et vous pouvez le retrouver sur Twitter à Twitter.com/PaulRandal.

Contenu associé