Le paramétrage des requêtes est souvent le moyen le plus rapide d’améliorer les performances de SQL Server. Souvent, les mesures d’amélioration des performances de serveur (mémoire, processeurs, etc.) au niveau du système sont inefficaces et coûteuses. Les développeurs experts pensent que la plupart des problèmes de performance peuvent être imputés à des requêtes mal écrites et à des indexations inefficaces, et non à des contraintes matérielles. En effet, certains problèmes de performance ne peuvent être résolus qu’en modifiant les requêtes.
En matière de paramétrage des requêtes SQL Server, les administrateurs de base de données se demandent souvent par où commencer. Comment évaluer une requête ? Comment découvrir les défauts de rédaction d’une requête ? Comment découvrir les opportunités d’amélioration cachées ? Le paramétrage des requêtes est à la fois une science et un art, car il n’y a ni bonnes ni mauvaises réponses, seulement la solution la plus appropriée en fonction de la situation.
Le paramétrage des requêtes SQL Server peut être réparti en trois grandes étapes :
Voici 12 astuces rapides qui peuvent aider un administrateur de base de données à améliorer les performances de requêtes de manière mesurable, tout en fournissant la garantie que l’altération spécifique améliore réellement la vitesse de la requête.
-
Analyse de base des requêtes
Les administrateurs de base de données ont besoin de voir toutes les couches et informations des requêtes coûteuses afin d’isoler la cause première. Un paramétrage efficace nécessite la connaissance des principales instructions SQL, des principaux types d’attente, des plans SQL, des requêtes bloquées, des conflits de ressources ainsi que de l’effet produit par des index manquants. Commencez par les bases ; vous devez savoir exactement de quoi il s’agit avant de vous lancer.
Conseil 1 : Connaître les tables et le nombre de lignes
Tout d’abord, assurez-vous de vraiment travailler sur une table, pas une vue ou une fonction tabulaire. Les fonctions tabulaires ont leurs propres exigences de performances. Vous pouvez utiliser SSMS pour placer votre curseur sur les éléments de requête et observer ces détails. Vérifiez le nombre de lignes en interrogeant les vues de gestion dynamiques.
Conseil 2 : Examiner les filtres de requête et les clauses WHERE et JOIN, et noter le nombre de lignes filtrées
S’il n’y a pas de filtres, et que la majorité de la table est renvoyée, demandez-vous si vous avez besoin de toutes les données. S’il n’y a absolument aucun filtre, cela doit être un signal d’alarme et demande des recherches supplémentaires. Cela peut vraiment ralentir une requête.
Conseil 3 : Connaître la sélectivité de vos tables
En fonction des tables et des filtres des deux conseils précédents, vous devez connaître le nombre de lignes sur lesquelles vous travaillez, ou la taille de l’ensemble logique réel. Nous vous recommandons d’utiliser les diagrammes SQL comme outils puissants d’évaluation des requêtes et de sélectivité des requêtes.
Conseil 4 : Analyser les colonnes de requête supplémentaires
Observez attentivement les fonctions SELECT * ou scalaires pour déterminer si des colonnes supplémentaires sont impliquées. Plus vous incluez de colonnes, moins un plan d’exécution peut utiliser de façon optimale certaines opérations d’indexation, ce qui peut engendrer une baisse des performances.
-
Analyse avancée des requêtes
Le fait de connaître et d’utiliser les contraintes peut être utile pour commencer le paramétrage. Vérifiez les clés, contraintes et index existants pour vous assurer d’éviter la duplication d’effort ou le chevauchement d’index.Pour obtenir des informations relatives à vos index, exécutez la procédure stockée sp_helpindex.
Conseil 6 : Examiner le plan d’exécution réel (pas estimé)
Les plans estimés utilisent des statistiques estimées pour déterminer les lignes estimées ; les plans réels utilisent des statistiques réelles pendant l’exécution. Si les plans réels et estimés sont différents, vous devrez peut-être effectuer des recherches supplémentaires.
Conseil 7 : Enregistrer vos résultats, en s’intéressant au nombre d’E/S logiques
Si vous n’enregistrez pas les résultats, vous ne pourrez pas déterminer l’impact réel de vos modifications.
Conseil 8 : Ajuster la requête en fonction de vos résultats et apporter de petites modifications une par une
Si vous apportez un trop grand nombre de modifications à la fois, cela peut s’avérer inefficace, car elles peuvent s’annuler mutuellement. Commencez par rechercher les opérations les plus coûteuses. Il n’y a ni bonne réponse, ni mauvaise réponse, mais seulement une réponse optimale en fonction de la situation donnée.
Conseil 9 : Exécuter de nouveau la requête et enregistrer les résultats de la modification apportée
Si vous voyez une amélioration dans les E/S logiques, mais que l’amélioration est insuffisante, revenez au conseil 8 pour observer d’autres facteurs pouvant nécessiter un ajustement. Continuez à apporter une modification à la fois, réexécuter la requête et comparer les résultats jusqu’à ce que vous pensiez avoir traité toutes les opérations coûteuses possibles.
Conseil 10 : Si d’autres améliorations sont requises, envisager d’ajuster les index pour réduire les E/S logiques
L’ajout ou l’ajustement des index n’est pas toujours la meilleure chose à faire, mais si vous ne pouvez pas modifier le code, ce peut être la seule option disponible. Vous pouvez vous tourner sur les index existants, un index couvrant et un index filtré pour vos améliorations.
Conseil 11 : Réexécuter la requête et enregistrer les résultats
Si vous avez apporté des ajustements, réexécutez la requête et enregistrez de nouveau les résultats.
Conseil 12 : Supprimer les éléments inutiles
Recherchez les inhibiteurs de performances fréquemment trouvés comme les premiers générateurs de code, l’utilisation excessive de caractères génériques, les fonctions scalaires, les vues imbriquées, les curseurs et le traitement ligne par ligne.
-
Utilisez un outil de surveillance des performances de base de données pour effectuer le paramétrage des requêtes.
Les outils traditionnels de surveillance de base de données se concentrent sur les mesures d’intégrité. Les outils de gestion de performance des applications actuels fournissent des conseils, mais ne permettent pas de trouver la cause première.
Conseil :
Vous pouvez rendre le paramétrage des requêtes beaucoup plus facile en utilisant une solution de surveillance des performances de base de données continue telle que SolarWinds Database Performance Analyzer (DPA) pour regrouper les informations de performances dans un emplacement unique. Avec DPA, vous pouvez :
- Identifier précisément la requête qui a été retardée
- Identifier le goulet d’étranglement (événement d’attente) qui cause le retard
- Afficher l’impact temporel du goulet d’étranglement identifié
En savoir plus sur SolarWinds Database Performance Analyzer (DPA)