SQL - Quels sont vos trucs de performance préférés?

Question

Lorsque vous avez une requête ou une procédure stockée qui nécessite une optimisation des performances, quelles sont les premières choses que vous essayez?

Answer 1

Voici le handy dandy liste des choses que j'ai toujours donner à quelqu'un de me poser des questions sur l'optimisation.
Nous utilisons principalement Sybase, mais la plupart des conseils qui s'appliquent à l'ensemble du conseil d'administration.

SQL Server, par exemple, est livré avec une multitude de suivi de la performance / tuning bits, mais si vous n'avez pas quelque chose comme ça (et peut-être même si vous n') alors je voudrais envisager la suite...

99% des problèmes que j'ai vu sont causés par mettre trop de tables dans une jointure. Le correctif de ce est de faire la moitié de la jointure (avec certaines tables) et de mettre en cache les résultats dans une table temporaire. Puis faire le reste de la requête rejoindre sur cette table temporaire.

Requête De Liste De Vérification De L'Optimisation

• Exécuter la mise à JOUR des STATISTIQUES sur les tables sous-jacentes
  • De nombreux systèmes d'exécuter cette planifié hebdomadaire du travail
• Supprimer des enregistrements dans les tables sous-jacentes (éventuellement archiver les enregistrements supprimés)
  • Envisager de faire cela automatiquement une fois par jour ou une fois par semaine.
• Reconstruire Les Index
• Reconstruire les Tables (bcp de données/)
• Dump / Recharger la base de données (drastique, mais pourrait résoudre la corruption)
• Construire de nouvelles, plus approprié index
• Exécutez DBCC pour voir si il est possible de corruption dans la base de données
• Serrures / Blocages
  • Vérifiez qu'aucun autre processus en cours d'exécution dans la base de données
    • Surtout DBCC
  • Êtes-vous à l'aide de lignes ou de pages de niveau de verrouillage?
  • Verrouiller les tables exclusivement avant le départ de la requête
  • Vérifiez que tous les processus soient accéder à des tables dans le même ordre
• Sont des indices utilisés de manière appropriée?
  
  • Jointures seulement l'utilisation de l'indice si les deux expressions sont exactement du même type de données
  • L'Index ne sera utilisé que si le premier champ(s) sur l'index correspondent à la requête
  • Sont regroupés les indices utilisés, le cas échéant?
    • plage de données
    • OÙ le terrain entre valeur1 et valeur2
• Les petites Jointures de Nice Rejoint
  • Par défaut, l'optimiseur de ne considérer les tableaux 4 à la fois.
  • Cela signifie que dans les jointures avec plus de 4 tables, il a une bonne chance de choisir un plan de requête non optimal
• Briser le Rejoindre
  • Pouvez-vous briser le rejoindre?
  • Pré-sélectionner la clé étrangère dans une table temporaire
  • Faire la moitié de la rejoindre et de mettre les résultats dans une table temporaire
• Maintenir Des Tableaux De Synthèse
  • Construire avec des déclencheurs sur les tables sous-jacentes
  • Construire quotidiennes / horaire / etc.
  • Construire ad-hoc
  • Construire progressivement ou de démontage / reconstruction
• Voir ce que le plan de requête est avec SHOWPLAN SUR
• Voir ce qui est réellement passe avec un ENSEMBLE de STATISTIQUES SUR IO
• La Force d'un index à l'aide de la pragma: (indice: myindex)
• La Force de l'ordre de la table à l'aide de SET forceplan avait SUR
• La Détection Des Paramètres:
  • Pause Procédure Stockée en 2
  • appel proc2 de proc1
  • permet à l'optimiseur de choisir l'indice de proc2 si @paramètre a été modifié par proc1
• Pouvez-vous améliorer votre matériel?
• À quelle heure êtes-vous en cours d'exécution? Est-il un moment plus calme?
• Est Serveur de Réplication (ou d'autres non-stop processus) en cours d'exécution? Vous pouvez le suspendre? Exécuter par exemple. horaire?

Answer 2

1. Avoir une assez bonne idée de la trajectoire optimale de l'exécution de la requête dans votre tête.
2. Vérifier le plan de requête - toujours.
3. Tourner sur les STATISTIQUES, de sorte que vous pouvez examiner à la fois d'e / s et des performances de l'UC. Se concentrer sur la conduite de ces numéros, pas nécessairement la durée de la requête (comme ça peut être influencé par d'autres activités, cache, etc.).
4. Look pour un grand nombre de lignes à venir dans un opérateur, mais en petit nombre à venir. Généralement, un index de l'aider en limitant le nombre de lignes (ce qui économise de la lecture du disque).
5. L'accent sur le coût le plus important de la sous-arborescence d'abord. Changement à cette sous-arborescence peuvent souvent changer la totalité du plan de requête.
6. Problèmes courants que j'ai vu sont:
   • Si il y a beaucoup de jointures, parfois, Sql Server va choisir d'étendre les jointures, puis d'appliquer les clauses where. Habituellement, vous pouvez résoudre ce problème en déplaçant le OÙ les conditions de la clause de JOINTURE, ou une table dérivée avec les conditions inline. Les vues peuvent causer les mêmes problèmes.
   • Sous-optimale des jointures (BOUCLE vs HACHAGE vs FUSION). Ma règle d'or est d'utiliser une jointure de BOUCLE lorsque la rangée du haut a très peu de lignes par rapport à la partie inférieure, une FUSION lorsque les ensembles sont à peu près égales et ordonné, et une valeur de HACHAGE pour tout le reste. L'ajout d'un indicateur de jointure vous permettra de tester votre théorie.
   • La détection des paramètres. Si vous avez exécuté la procédure stockée avec des valeurs irréalistes au premier (par exemple, pour les tests), puis la mise en cache de plan de requête peut être sous-optimale pour votre production. Courir à nouveau AVEC RECOMPILER devriez vérifier cela. Pour certains stockées procs, en particulier ceux qui traitent de la taille variable des plages de dire, toutes les dates entre hier et aujourd'hui - ce qui impliquerait un INDEX de recherche - ou, toutes les dates entre l'année dernière et cette année, ce qui serait mieux avec un INDEX SCAN) vous pouvez l'exécuter AVEC RECOMPILER à chaque fois.
   • Mauvaise indentation...Ok, Sql Server n'a pas un problème avec cela - mais je suis sûr que s'il est impossible de comprendre une requête jusqu'à ce que j'ai fixé jusqu'à la mise en forme.

Answer 3

Légèrement hors sujet, mais si vous avez le contrôle sur ces questions...
De haut niveau et à fort Impact.

• Pour la haute IO environnements assurez-vous que vos disques sont pour RAID 10 ou RAID 0+1 ou imbriquée de la mise en œuvre du raid 1 et raid 0.
• N'utilisez pas de disques de moins de 1500K.
• Assurez-vous que vos disques ne sont utilisés que pour votre Base de données. IE pas de connexion pas de système d'exploitation.
• Désactiver l'auto à grandir ou une fonction similaire. Laisser la base de données utiliser toutes les capacités de stockage est prévu. Pas nécessairement ce qui est actuellement utilisé.
• la conception de votre schéma et les index pour le type de requêtes.
• si c'est un journal de type tableau (insérer uniquement) et doit être dans la DB ne pas indexer.
• si vous faites beaucoup de reporting (complexe sélectionne avec beaucoup de jointures) alors vous devriez regarder à la création d'un entrepôt de données avec une étoile ou flocon de neige schéma.
• N'ayez pas peur de la réplication de données dans exchange pour la performance!

Answer 4

CREATE INDEX

Assurer il y a des indices disponibles pour votre WHERE et JOIN clauses. Cela permettra d'accélérer l'accès aux données beaucoup.

Si votre environnement est un data mart ou de l'entrepôt,les indices ne manquent pas pour presque n'importe quelle requête.

Dans un environnement transactionnel, le nombre d'index doit être plus faible et leurs définitions plus stratégique, de sorte que la maintenance d'index ne pas glisser vers le bas de ressources. (Indice est quand les feuilles de l'index doit être modifié pour refléter un changement dans la table sous-jacente, comme INSERT, UPDATE, et DELETE des opérations.)

Aussi, soyez conscient de l'ordre des champs dans l'index - le plus sélectif (plus de cardinalité) un champ, le plus tôt dans l'index, il devrait s'afficher. Par exemple, dire que vous êtes interrogation pour les automobiles:

SELECT   i.make, i.model, i.price
FROM     dbo.inventory i
WHERE    i.color = 'red'
  AND    i.price BETWEEN 15000 AND 18000

Prix généralement plus élevés de la cardinalité. Il peut y avoir seulement quelques dizaines de couleurs disponibles, mais tout à fait peut-être des milliers de différents prix demandé.

De ces index des choix, idx01 fournit le plus rapide chemin pour satisfaire à la requête:

CREATE INDEX idx01 ON dbo.inventory (price, color)
CREATE INDEX idx02 ON dbo.inventory (color, price)

C'est parce que les voitures de moins de satisfaire le niveau de prix que le choix de la couleur, donnant le moteur de recherche de beaucoup moins de données à analyser.

J'ai été connu pour avoir deux très similaires indices ne diffèrent que dans le champ de commande de la vitesse des requêtes (firstname, lastname) dans l'un et l' (nom, prénom) dans l'autre.

Answer 5

Un truc que j'ai appris récemment que SQL Server peut mettre à jour les variables locales ainsi que les champs, dans une instruction de mise à jour.

UPDATE table
SET @variable = column = @variable + otherColumn

Ou la version plus lisible:

UPDATE table
SET
    @variable = @variable + otherColumn,
    column = @variable

J'ai utilisé ce pour remplacer compliqué curseurs/jointures lors de l'implémentation récursive calculs, et a également acquis beaucoup dans la performance.

Voici les détails et un exemple de code qui fait fantastique des améliorations de la performance: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx