Introduction à
SQL Server Performance tuning
Techniques d'amélioration des performances de SQL Server
En résumé, l'optimisation des performances de SQL Server est essentielle pour garantir que les systèmes de base de données fonctionnent de manière efficiente et efficace. En mettant en œuvre des stratégies d'optimisation des requêtes, des techniques d'indexation et des pratiques d'excellence pour la configuration du serveur, les entreprises peuvent améliorer les performances de SQL Server et, en fin de compte, améliorer l'ensemble de leurs activités. Il est important d'évaluer et d'optimiser régulièrement les performances de SQL Server afin de s'assurer qu'il répond aux besoins de l'organisation et de ses utilisateurs. En utilisant les bonnes techniques et stratégies, les entreprises peuvent optimiser les performances de SQL Server et obtenir de plus grands succès dans leurs activités commerciales.
Base de données perf_querystore
Base de données perf_neartime
Les données collectées fournissent un aperçu précieux des performances du système et permettent aux utilisateurs d'analyser et de surveiller différents aspects de l'activité du système. La collecte de données à intervalles réguliers permet de suivre les changements et d'identifier les tendances dans le temps. Ces informations sont essentielles pour les administrateurs système et les analystes afin d'optimiser les performances du système, de résoudre les problèmes et de prendre des décisions éclairées concernant l'allocation des ressources et la configuration du système.
Les données collectées comprennent des informations sur le serveur telles que le nom du serveur, la version, l'heure de connexion et les détails de la session. Il capture également les requêtes actives, les plans d'exécution, la taille des bases de données, les sessions de blocage, les statistiques des groupes de charge de travail, les utilisateurs connectés, les activités d'allocation et de partage de pages, les allocations de mémoire, les requêtes exécutées, l'état du pool de ressources, les transactions ouvertes, l'état d'attente, les tâches planifiées et l'utilisation actuelle de la mémoire. Chacun de ces tableaux contient des colonnes spécifiques qui fournissent des informations pertinentes pour la catégorie de données concernée.
sys.dm_exec_sessions
DMV comprend les sessions utilisateur ainsi que les sessions internes de SQL Server utilisées pour exécuter des tâches d'arrière-plan.sys.dm_exec_connections
contient des informations supplémentaires pour les sessions établies par des clients externes, y compris les détails du protocole.sys.dm_exec_requests contient des informations sur les requêtes SQL Server actives (par exemple, l'exécution de requêtes).
Vue de la mémoire allouée
perfsys.dm_exec_sessions
- fournit des informations sur toutes les connexions d'utilisateurs actives et les tâches internes. Il s'agit notamment d'informations sur les clients, telles que les noms d'application et d'hôte, les paramètres de sécurité, les paramètres de configuration et d'autres informations spécifiques à la session. Daten.perfsys.dm_exec_requests
– fournit des informations sur chaque requête exécutée dans SQL Server. Cela inclut les requêtes des sessions utilisateur, des sessions système et des sessions internes. Processus.perfsys.dm_exec_query_memory_grants
– fournit des informations sur toutes les requêtes qui ont demandé une mémoire et qui sont en attente d'obtenir une bourse ou qui ont obtenu une bourse de mémoire. perfsys.dm_exec_sql_text
– Informations sur le texte de la requête SQL selon la requête sql_handle.dbo.tbl_query_store_plan_handle
– représente l'identifiant de l'emplacement où se trouve le plan d'exécution réel pour une requête donnée est stocké.
Le script WhoTheFuckIsWrong.sql est un outil puissant d'analyse des performances de SQL Server.
Avec le script WhoTheFuckIsWrong.sql vous pouvez facilement vérifier quelles requêtes problématiques sont exécutées sous SQL Server en temps réel.
Dans les résultats de l'exécution de ce script, vous verrez toutes les métriques permettant d'identifier les requêtes problématiques qui consomment le plus de ressources du serveur SQL.
Certaines des principales métriques que vous pouvez voir dans les résultats de la WhoTheFuckIsWrong.sql-de l'exécution :
- Mémoire allouée - mémoire allouée sur le serveur pour la requête
- Mémoire utilisée - Fait que de la mémoire a été utilisée pendant l'exécution de la requête.
- Reads - nombre de lectures effectuées par la requête
- écritures - nombre d'écritures effectuées par la requête
- Temps CPU - indique en millisecondes combien de temps le CPU a été utilisé lors de la requête
- Temps total écoulé - affiche la durée d'exécution de la requête en millisecondes
- Instruction SQL - est le texte SQL de la requête
- Plan SQL - Afficher le plan d'exécution de l'instruction de requête
Vous pouvez également voir à partir de quel serveur et de quelle application la requête a été lancée et quel utilisateur a exécuté la requête.
Script WhoTheFuckIsWrong.sql est un outil puissant d'analyse des performances sur SQL Server.
Optimisation des performances Aperçu Base de données complète perf_neartime
Aperçu de l'optimisation des performances Base de données complète perf_querystore
Documentation
Optimisation des performances des requêtes pour de meilleurs résultats
- 1. Utiliser des plans d'exécution pour optimiser les requêtes
- 2. Optimiser l'utilisation de l'index
- 3. Éliminer les index inutilisés
- 4. Optimiser la comparaison des requêtes avec des caractères génériques
- 5. Optimiser les opérations JOIN
- 6. Évitez les conditions OR multiples dans les prédicats FILTER
- 7. Utilisez les champs SELECT au lieu de SELECT *.
- 8. Évitez SELECT DISTINCT
- 9. Actualiser régulièrement les statistiques de consultation
- 10. Surveiller et optimiser les performances des requêtes
- Conclusion
Optimisation des performances de SQL Server
SQL Server est un système de gestion de base de données puissant utilisé par les entreprises et les organisations pour stocker et extraire de grandes quantités de données. Toutefois, lorsque la taille des bases de données et la complexité des requêtes augmentent, des problèmes de performance peuvent survenir, entraînant une lenteur d'exécution des requêtes et une baisse de la productivité. Pour relever ces défis, il est important d'optimiser les requêtes SQL et d'améliorer les performances des requêtes. Dans cet article, nous allons examiner des conseils et des techniques efficaces d'optimisation des requêtes SQL afin d'améliorer les performances de votre base de données SQL Server.
Les bases de l'optimisation des requêtes
L'optimisation des requêtes est un processus crucial qui vise à améliorer les performances des requêtes SQL en utilisant efficacement les ressources du système et en optimisant les métriques de performance. L'objectif de l'optimisation des requêtes est de réduire le temps de réponse, de minimiser la consommation de ressources et d'identifier et de corriger les mauvaises performances des requêtes. En analysant les étapes d'exécution d'une requête, en appliquant des techniques d'optimisation et en tenant compte d'autres facteurs tels que les index et les plans d'exécution, vous pouvez améliorer considérablement les performances de vos requêtes SQL Server.
Le réglage des performances de SQL Server est un processus complexe et fastidieux. Confiez ce travail à des professionnels.
C'est avec plaisir que je vous aiderai à optimiser et à mettre en place la base de données de votre entreprise. Il suffit de téléphoner +41 (0)56 511 60 17 ou Contactez-moi de n'importe quelle manière.
1. Utiliser des plans d'exécution pour l'optimisation des requêtes
L'un des principaux outils d'optimisation des requêtes SQL est le plan d'exécution. Lorsqu'une requête est exécutée, l'optimiseur de requêtes SQL Server génère un plan d'exécution qui décrit les étapes nécessaires à l'exécution de la requête. Le plan d'exécution fournit des informations précieuses sur le traitement de la requête, notamment sur l'utilisation des index, les opérations de jointure et les méthodes de récupération des données. En analysant le plan d'exécution, vous pouvez identifier les goulets d'étranglement potentiels et optimiser la requête en conséquence.
Pour obtenir le plan d'exécution d'une requête, vous pouvez utiliser des outils tels que SQL Server Management Studio ou des outils de profilage des requêtes tels que dbForge Studio pour SQL Server. Ces outils fournissent des représentations visuelles du plan d'exécution, ce qui facilite l'identification des problèmes de performance et des possibilités d'amélioration. En comprenant le plan d'exécution, vous pouvez prendre des décisions éclairées concernant la création d'index, l'optimisation des jointures et d'autres techniques d'optimisation des requêtes.
2. Optimiser l'utilisation de l'index
Les index jouent un rôle essentiel dans l'optimisation des performances des requêtes. Ils permettent d'extraire plus efficacement les données des tables en créant une structure ordonnée des lignes de la table. Lors de l'exécution d'une requête, l'optimiseur de requêtes SQL Server peut suggérer la création d'index manquants en fonction du plan d'exécution de la requête. Ces index suggérés peuvent améliorer considérablement les performances des requêtes en réduisant la nécessité d'effectuer des analyses complètes des tables et en permettant une récupération plus rapide des données.
Pour optimiser l'utilisation des index, il est important de vérifier régulièrement les plans d'exécution de vos requêtes et d'identifier les index manquants. Vous pouvez utiliser des vues administratives dynamiques telles que sys.dm_db_missing_index_details
et sys.dm_db_missing_index_columns
pour recueillir des informations sur les index manquants proposés. Envisagez également d'utiliser des index couvrants, qui comprennent tous les champs nécessaires à une requête, afin d'améliorer encore les performances de la requête.
Il est toutefois important de noter que l'ajout d'index doit être fait avec précaution. Si les index peuvent améliorer les performances des requêtes, ils entraînent également un surcroît de travail en termes d'utilisation et de maintenance de l'espace de stockage. Il convient donc d'évaluer soigneusement l'impact de l'ajout d'index sur les opérations d'insertion, de mise à jour et de suppression afin de garantir un équilibre entre les performances des requêtes et les performances globales de la base de données.
3. Éliminez les index inutilisés
Si l'ajout d'index manquants peut améliorer les performances des requêtes, il est tout aussi important d'identifier et de supprimer les index non utilisés. Les index non utilisés consomment de l'espace mémoire et nécessitent une maintenance sans offrir d'avantages significatifs en termes de performances. Examinez régulièrement les statistiques d'utilisation de vos index et identifiez ceux qui ne sont pas utilisés par vos requêtes.
Une raison fréquente pour laquelle les index ne sont pas utilisés est la conversion implicite des types de données. Si les requêtes impliquent des conversions de type de données, par exemple la comparaison d'une colonne d'entiers avec une valeur de chaîne de caractères, les index ne doivent pas être utilisés. Pour éviter ce problème, assurez-vous que les comparaisons sont effectuées avec des types de données compatibles ou, si nécessaire, utilisez une conversion explicite de type de données.
Une autre considération est l'utilisation d'index filtrés. Ces index contiennent une condition de filtrage qui limite l'index à un sous-ensemble de lignes dans une table. Si la condition de filtrage n'est pas remplie dans une requête, l'index filtré ne sera pas utilisé. Examinez vos index filtrés et assurez-vous que les conditions de filtrage correspondent aux requêtes qui doivent accéder aux données indexées.
En identifiant et en éliminant les index inutilisés, vous pouvez réduire la charge de travail liée à la gestion des index et améliorer les performances globales des requêtes.
4. Optimiser la comparaison des requêtes avec les caractères génériques
Les caractères génériques sont des outils puissants pour la comparaison de modèles dans les requêtes SQL. Toutefois, une mauvaise utilisation des caractères génériques peut avoir un impact négatif sur les performances de la requête. Lors de l'utilisation de caractères génériques, il est recommandé de les placer à la fin d'une expression et non au début. Placer les caractères génériques à la fin permet de récupérer les données plus efficacement, car les structures d'index sont utilisées si elles sont disponibles.
Imaginez par exemple une requête qui récupère des enregistrements de clients en fonction de leur prénom. Au lieu d'utiliser un caractère générique au début du terme de recherche, par exemple LIKE '%John'
, verwenden Sie ihn am Ende, z. B. LIKE 'John%'
. Cela permet à l'optimiseur de requêtes d'utiliser efficacement les index et de récupérer plus rapidement les données pertinentes.
En outre, lorsque vous recherchez des motifs spécifiques qui concernent les derniers caractères d'un mot ou d'une expression, vous devriez utiliser la fonction REVERSE()
et envisager la création d'une colonne calculée persistante. En inversant les valeurs et en appliquant la recherche par caractères génériques à la colonne inversée, vous pouvez optimiser les recherches et améliorer les performances des requêtes.
5. Optimiser les opérations JOIN
Les opérations JOIN peuvent avoir un impact considérable sur les performances des requêtes, en particulier lors de la manipulation de grandes tables. Pour optimiser les opérations JOIN, analysez soigneusement le plan d'exécution et identifiez tous les JOIN inutiles ou redondants. Dans certains cas, il peut être possible d'éliminer les JOIN en divisant la requête en parties distinctes et en combinant les résultats avec l'opérateur UNION.
Une autre technique d'optimisation des JOINs est l'élimination des JOINs. Il s'agit notamment de supprimer les JOINs inutiles en réécrivant la requête afin d'éliminer les tables ou sous-requêtes redondantes. La simplification de la structure de la requête permet d'améliorer les performances de la requête et de réduire la charge de calcul associée aux opérations JOIN.
Il est également important de s'assurer que les tables impliquées dans les opérations JOIN sont correctement indexées. L'indexation des colonnes utilisées dans les conditions JOIN peut améliorer considérablement les performances des requêtes en permettant une récupération plus rapide des données et en réduisant la nécessité de procéder à une analyse complète des tables.
6. Évitez les conditions OU multiples dans les prédicats FILTER
Pour les requêtes SQL, l'utilisation de plusieurs conditions OR dans les prédicats FILTER peut entraîner une mauvaise performance de la requête. En présence de plusieurs conditions OU, l'optimiseur SQL Server évalue chaque condition individuellement, ce qui peut entraîner une exécution inefficace de la requête.
Pour optimiser les performances de la requête, il est recommandé d'éviter d'utiliser plusieurs conditions OU dans les prédicats FILTER. Envisagez plutôt de diviser la requête en instructions SELECT distinctes et de combiner les résultats à l'aide de l'opérateur UNION. Cela permet à l'optimiseur de traiter chaque condition séparément et d'optimiser le plan d'exécution en conséquence.
En minimisant l'utilisation des conditions OU et en structurant efficacement les requêtes, vous pouvez améliorer considérablement les performances des requêtes et réduire la consommation de ressources.
7. Utilisez les champs SELECT au lieu de SELECT *
Lors de l'extraction de données d'une base de données SQL Server, il est courant d'utiliser la syntaxe SELECT * pour extraire toutes les colonnes d'une table. Cette approche peut toutefois conduire à une exécution inefficace de la requête et à une consommation inutile de ressources.
Pour optimiser les performances de la requête, il est recommandé de spécifier les colonnes exactes à récupérer plutôt que d'utiliser SELECT *. En sélectionnant explicitement les champs requis, vous réduisez la quantité de données transmises de la base de données au client et minimisez la charge de calcul liée au traitement des colonnes inutiles.
En outre, l'utilisation de champs SELECT permet une meilleure optimisation des requêtes, car l'optimiseur de requêtes peut générer des plans d'exécution plus efficaces basés sur les colonnes spécifiques récupérées. Il peut en résulter une exécution plus rapide des requêtes et une amélioration des performances globales.
8. Évitez SELECT DISTINCT
L'opérateur SELECT DISTINCT est souvent utilisé pour supprimer les lignes en double dans les résultats des requêtes. Toutefois, cela peut avoir un effet négatif sur les performances de la requête, en particulier lorsque vous traitez de grands ensembles de données.
Au lieu d'utiliser SELECT DISTINCT, vous devriez envisager d'autres approches pour obtenir la fonctionnalité souhaitée. Vous pouvez par exemple utiliser des clauses GROUP BY ou des sous-requêtes pour éliminer les doublons tout en conservant de meilleures performances de requête.
En évitant d'utiliser SELECT DISTINCT et en explorant d'autres structures de requêtes, vous pouvez optimiser les performances des requêtes et améliorer l'efficacité globale de la base de données.
9. Mettre régulièrement à jour les statistiques des requêtes
Pour garantir l'exactitude des statistiques de requêtes, il est important de les mettre à jour régulièrement, en particulier après des modifications importantes des données ou des index. En mettant à jour les statistiques de requête, vous fournissez à l'optimiseur de requêtes des informations actualisées sur la répartition des données, ce qui lui permet de générer des plans d'exécution plus précis et plus efficaces.
Vous pouvez utiliser la commande UPDATE STATISTICS ou les mises à jour automatisées des statistiques fournies par SQL Server pour maintenir les statistiques de requête à jour. En intégrant des mises à jour régulières des statistiques dans votre routine de maintenance, vous pouvez optimiser les performances des requêtes et garantir une optimisation précise des requêtes.
10. Surveiller et affiner les performances des requêtes
Envisagez également de mettre en place des benchmarks de performances des requêtes et de comparer les performances de vos requêtes dans le temps. Cela vous permettra de suivre l'amélioration des performances et d'identifier les domaines dans lesquels des améliorations supplémentaires sont nécessaires.
En surveillant et en affinant les performances des requêtes, vous pouvez vous assurer que votre base de données SQL Server fonctionne avec une efficacité optimale et fournit des résultats de requêtes rapides et réactifs.