Performance Tuning

Introduction SQL Server Performance Tuning

Êtes-vous frustré par la médiocrité des SQL server performance (poor performance peut affecter l'expérience de l'utilisateur, les opérations commerciales et l'évolutivité de votre système)? Si oui, vous êtes au bon endroit. Ici et maintenant, vous recevrez de l'aide pour régler les performances du serveur SQL et une assistance complète avec un rapport détaillé sur le travail effectué. Notre stratégie peut avoir un impact significatif sur la rapidité et l'efficacité de votre application. Nous exploiterons tout le potentiel de votre base de données. Préparez-vous à surcharger votre serveur SQL !

L'optimisation des requêtes est une technique essentielle pour améliorer les performances du serveur SQL. L'optimisation des requêtes SQL consiste à identifier les méthodes les plus efficaces pour améliorer les performances des requêtes. Plusieurs stratégies d'optimisation des requêtes peuvent être employées pour atteindre cet objectif. Une approche courante consiste à créer des index appropriés sur les colonnes utilisées dans les clauses WHERE, JOIN et ORDER BY afin d'accélérer la récupération des données. En outre, le fait de ne sélectionner que les champs nécessaires au lieu d'utiliser SELECT * et d'éviter l'utilisation de DISTINCT dans les instructions SELECT peut également contribuer à optimiser les performances des requêtes. En employant ces techniques, les utilisateurs de SQL Server peuvent améliorer de manière significative la vitesse et l'efficacité de leurs requêtes, ce qui se traduit par de meilleures performances globales.
L'indexation est une autre technique importante pour améliorer les performances du serveur SQL. Les index de SQL Server permettent d'accélérer la recherche de données en fournissant un moyen plus rapide de localiser les données dans une table. Il existe plusieurs bonnes pratiques en matière d'indexation dans SQL Server, telles que la création d'index pertinents et l'évitement de la surindexation. En outre, les outils d'optimisation des performances peuvent être utilisés pour identifier et résoudre les problèmes d'indexation, en recommandant par exemple la création de nouveaux index ou la suppression des index inutiles. En mettant correctement en œuvre les techniques d'indexation, les utilisateurs de SQL Server peuvent s'assurer que leurs bases de données fonctionnent à des niveaux de performance optimaux.
La configuration du serveur est également un facteur critique dans l'optimisation des performances du serveur SQL. Une configuration correcte de l'instance et du matériel du serveur SQL permet de garantir un fonctionnement efficace du serveur. Par exemple, la configuration des paramètres de la mémoire du serveur et de l'affinité du processeur peut contribuer à optimiser les performances. En outre, les tâches de surveillance et d'optimisation des performances peuvent aider à identifier et à isoler les problèmes de performance, tels que les goulets d'étranglement ou les problèmes de performance de la base de données. En suivant les meilleures pratiques de configuration et de surveillance des serveurs, les utilisateurs de SQL Server peuvent optimiser les performances de leur serveur et s'assurer qu'il fonctionne de manière optimale.

Techniques d'amélioration SQL Server performance

En conclusion, l'optimisation des performances du serveur SQL est essentielle pour garantir le fonctionnement efficace des systèmes de bases de données. En mettant en œuvre des stratégies d'optimisation des requêtes, des techniques d'indexation et les meilleures pratiques de configuration du serveur, les entreprises peuvent améliorer les performances de leur serveur SQL et, en fin de compte, leurs opérations commerciales globales. Il est important d'évaluer et d'ajuster régulièrement les performances du serveur SQL pour s'assurer qu'il répond aux besoins de l'organisation et de ses utilisateurs. En mettant en place les bonnes techniques et stratégies, les entreprises peuvent optimiser les performances de leur serveur SQL et obtenir de meilleurs résultats dans leurs activités.

Le sys.dm_exec_sessions DMV inclut les sessions des utilisateurs ainsi que les sessions internes du serveur SQL utilisées pour exécuter les 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 actives du serveur SQL (par exemple, les requêtes en cours d'exécution).

Vue de la mémoire allouée

Fournit des informations plus détaillées sur l'allocation et l'utilisation de la mémoire du top 20 pour des requêtes de base de données spécifiques, ainsi que des champs supplémentaires à des fins de gestion des données.
à des fins de gestion des données. Tables sources de la base de données perf_querystore d'où proviennent les données :

perfsys.dm_exec_sessions - fournit des informations sur toutes les connexions actives des utilisateurs et sur les tâches internes.
Il s'agit d'informations sur le client, telles que les noms d'application et d'hôte, les paramètres de sécurité, les paramètres de configuration et d'autres données spécifiques à la session.
perfsys.dm_exec_requests – fournit des informations sur chaque requête exécutée par le serveur SQL.
Il s'agit de demandes émanant de sessions d'utilisateurs, de sessions de systèmes et de processus internes.
perfsys.dm_exec_query_memory_grants – fournit des informations sur toutes les requêtes qui ont demandé et attendent un octroi de mémoire ou qui ont reçu un octroi de mémoire.
un octroi de mémoire. perfsys.dm_exec_sql_text - informations sur le texte de la requête SQL en fonction de la requête sql_handle.
dbo.tbl_query_store_plan_handle – représente l'identifiant de l'emplacement de mémoire où est stocké le plan d'exécution réel d'une requête donnée.

Le scénario WhoTheFuckIsWrong.sql est un outil puissant d'analyse des performances de SQL Server.

Avec script WhoTheFuckIsWrong.sql vous pouvez facilement vérifier en temps réel quelles sont les requêtes problématiques exécutées sur le serveur SQL.

Dans les résultats de l'exécution de ce script, vous verrez toutes les mesures permettant d'identifier les requêtes problématiques qui utilisent le plus de ressources du serveur SQL.

Quelques-unes des mesures les plus importantes que vous pouvez analyser dans les résultats de WhoTheFuckIsWrong.sql l'exécution :

Mémoire allouée - mémoire allouée sur le serveur pour la requête
Mémoire utilisée - mémoire utilisée pendant l'exécution de la requête
Lectures - 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é occupé par la requête.
Temps total écoulé - indique en millisecondes la durée de l'exécution de la requête
Déclaration SQL - texte SQL de la requête
Plan SQL - affiche 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.
Le scénario WhoTheFuckIsWrong.sql est un outil puissant d'analyse des performances de SQL Server.

Acheter le script ➜

Base de données perf_neartime

La base de données recueille des données en temps réel en capturant les activités du système et en les stockant dans différentes tables. Ces données sont enregistrées toutes les 5 minutes afin de garantir un enregistrement actualisé des performances du système. Le processus de sauvegarde des données est lancé par une tâche nommée "Performance_Neartime" qui exécute une procédure stockée appelée "StorePerformanceData". Cette tâche et cette procédure stockée travaillent ensemble pour extraire les informations nécessaires du système et les stocker dans les tables appropriées de la base de données.

Les données collectées fournissent des informations précieuses sur les performances du système, ce qui permet aux utilisateurs d'analyser et de contrôler divers aspects des activités du système. La collecte de données à intervalles réguliers permet de suivre les changements et d'identifier les tendances au fil du temps. Ces informations sont cruciales pour les administrateurs de systèmes 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 demandes actives, les plans d'exécution, la taille des bases de données, les sessions bloquantes, les statistiques des groupes de charge de travail, les utilisateurs connectés, l'activité d'allocation et de désallocation des pages, les octrois de mémoire, les requêtes en cours d'exécution, 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. Chacune de ces tables contient des colonnes spécifiques qui contiennent des informations pertinentes relatives à la catégorie de données concernée.

Vue d'ensemble des tunnels de performance Base de données complète perf_neartime

Base de données perf_querystore

La fonction de collecte de données en temps réel pour le magasin de requêtes offre des informations précieuses sur les choix et les performances des plans de requête pour SQL Server. Il simplifie grandement la résolution des problèmes de performance en vous permettant d'identifier rapidement les écarts de performance causés par des changements dans le plan de requête. Le magasin de requêtes capture et conserve automatiquement un historique des requêtes, des plans et des statistiques d'exécution pour vous permettre de les consulter. Le processus de sauvegarde des données est lancé par une tâche nommée Performance_QueryStore. TCette tâche appelle un ensemble de procédures stockées qui enregistrent les données des tables système du Query Store et des Dynamical Management Views dans les tables de la base de données perf_querystore. Données sauvegardées utilisées pour créer des vues et des tableaux de rapports contenant des informations permettant d'analyser et de définir la plupart des requêtes de chargement.

Vue d'ensemble des tunnels de performance Base de données complète perf_querystore

SQL Server Performance Tuning Meilleures pratiques

Avant de nous plonger dans les outils et techniques spécifiques d'optimisation des performances, passons en revue quelques-unes des meilleures pratiques générales qui peuvent vous aider à améliorer les performances de vos instances SQL Server.

Utilisez une convention de dénomination et un style de codage cohérents pour vos objets, vos variables et vos requêtes. Cela rendra votre code plus lisible, plus facile à maintenir et plus facile à déboguer.
Évitez d'utiliser des index, des contraintes, des déclencheurs, des vues, des fonctions et des procédures stockées inutiles ou redondants. Ceux-ci peuvent ajouter des frais généraux et de la complexité à votre système, et affecter le plan d'exécution de la requête et les performances.
Utilisez des types de données et des tailles appropriés pour vos colonnes et vos variables. Cela permettra de réduire l'espace de stockage et l'utilisation de la mémoire, et d'améliorer la qualité et l'intégrité des données.
Utilisez des requêtes paramétrées et des procédures stockées plutôt que du SQL dynamique ou des chaînes concaténées. Cela permettra d'éviter les attaques par injection SQL, de réduire le temps d'analyse et de compilation, et d'améliorer la réutilisation et la mise en cache du plan de requête.
Utilisez des opérations par lots et des méthodes d'insertion en bloc au lieu d'opérations ligne par ligne. Cela permettra de réduire les allers-retours sur le réseau et l'activité du journal des transactions, et d'améliorer le débit et la simultanéité.
Utilisez des stratégies et des techniques d'indexation appropriées pour vos tables et vos requêtes. Cela améliorera les performances de la requête en réduisant le nombre d'opérations d'E/S sur le disque et de balayages de données.
N'utilisez les astuces et les options de requête qu'en cas de nécessité et avec prudence. Elles peuvent remplacer le comportement par défaut de l'optimiseur de requêtes et affecter le plan d'exécution et les performances de la requête. Certains de ces conseils et options sont : NOLOCK, RECOMPILE, OPTIMIZE FOR, MAXDOP, etc.
Surveillez et analysez régulièrement les mesures de performance et les compteurs de vos instances SQL Server. Cela vous aidera à identifier les performances de base, à détecter les anomalies ou les goulets d'étranglement et à résoudre les problèmes éventuels.

SQL Server Performance Tuning Outils

Le serveur SQL fournit un certain nombre d'outils et de fonctionnalités qui peuvent vous aider à optimiser les performances. Voici quelques-uns de ces outils :

SQL Server Management Studio (SSMS): Il s'agit de l'outil principal de gestion et d'administration de vos instances SQL Server. Il fournit une interface utilisateur graphique (GUI) permettant d'effectuer diverses tâches, telles que la création et la modification d'objets, l'exécution de requêtes, la visualisation des résultats, le contrôle des performances, etc.
Profiler SQL Server: Il s'agit d'un outil permettant de capturer et d'analyser les événements qui se produisent sur vos instances SQL Server. Il vous permet de retracer l'activité de votre serveur, comme les requêtes exécutées, les erreurs survenues, les transactions validées ou annulées, etc. Vous pouvez utiliser cet outil pour identifier les requêtes qui consomment le plus de ressources ou qui posent des problèmes de performance.
Conseiller d'optimisation du moteur de base de données (DTA): Il s'agit d'un outil permettant d'analyser le schéma de votre base de données et la charge de travail, et de recommander des index, des statistiques, des partitions, etc. optimaux. Il utilise les données de trace collectées par SQL Server Profiler ou d'autres sources pour générer des recommandations de réglage.
Magasin de requêtes: Il s'agit d'une fonctionnalité qui collecte et stocke les plans d'exécution des requêtes et les mesures de performance de vos bases de données. Il vous permet de suivre l'historique de vos requêtes, de comparer différents plans et mesures dans le temps, d'identifier tout changement de plan ou toute régression, etc.
Événements prolongés: Il s'agit d'une fonctionnalité qui fournit un cadre léger et flexible pour la collecte et l'analyse des événements qui se produisent sur vos instances SQL Server. Il vous permet de créer des sessions personnalisées avec divers événements, cibles, actions, filtres, etc. Vous pouvez utiliser cette fonction pour surveiller divers aspects des performances de votre serveur, tels que les temps d'attente, les verrous, les blocages, l'utilisation de la mémoire, etc.
Vues et fonctions de gestion dynamique (DMV): Il s'agit de vues et de fonctions système qui exposent diverses informations sur vos instances SQL Server. Ils vous permettent d'interroger divers aspects de l'état de votre serveur, de sa configuration, de ses performances, etc. Voici quelques-unes de ces vues et fonctions : sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_os_wait_stats, sys.dm_os_performance_counters, sys.dm_db_index_usage_stats, etc.

SQL Server Performance Tuning Techniques

En plus des outils mentionnés ci-dessus, il existe des techniques spécifiques que vous pouvez utiliser pour optimiser les performances de vos instances SQL Server.. Some of these techniques are:

Query Optimization: This involves writing efficient queries that return the desired results with minimal resource consumption. Vous pouvez utiliser différentes méthodes pour optimiser vos requêtes, comme par exemple :

Utilisation de jointures au lieu de sous-requêtes ou de sous-requêtes corrélées
Utiliser EXISTS au lieu de IN ou NOT IN
Utilisation des clauses WHERE pour filtrer les données inutiles
Utiliser les clauses ORDER BY uniquement lorsque cela est nécessaire
Utiliser les clauses GROUP BY avec les fonctions d'agrégation au lieu de DISTINCT
Utilisation d'expressions de tables communes (CTE) ou de tables temporaires au lieu de tables dérivées
Utilisation d'opérations basées sur des ensembles au lieu de curseurs ou de boucles
Utilisation de UNION ALL au lieu de UNION
Utilisation d'opérateurs et de fonctions appropriés, tels que LIKE, BETWEEN, ISNULL, COALESCE, etc..

Optimisation de l'index: Il s'agit de créer et de maintenir des index optimaux pour vos tables et vos requêtes. Vous pouvez utiliser différentes méthodes pour optimiser vos index, comme par exemple :

Choisir le bon type d'index, tel que clustered, non clustertered, filtered, columnstore, etc.
Choisir les bonnes colonnes d'index, telles que celles utilisées dans les clauses WHERE, JOIN, ORDER BY ou GROUP BY.
Choisir le bon ordre d'indexation, par exemple ascendant ou descendant.
Choisir les bonnes options d'indexation, telles que le facteur de remplissage, la compression, le partitionnement, etc.
Mise à jour régulière des statistiques de l'indice pour refléter les changements de données.
Reconstruire ou réorganiser périodiquement la fragmentation de l'index afin d'améliorer les performances de l'index.
Suppression des index inutilisés ou dupliqués afin de réduire la charge de maintenance de l'index.

Optimisation de la base de données: Il s'agit de concevoir et de maintenir des structures et des paramètres de base de données optimaux pour votre système. Vous pouvez utiliser différentes méthodes pour optimiser votre base de données :

Choisir les bons types et tailles de données pour vos colonnes et variables
Choisir le bon niveau de collation et de compatibilité pour votre base de données
Choisir le bon modèle de récupération et la bonne stratégie de sauvegarde pour votre base de données
Choisir le bon groupe de fichiers et la bonne disposition de fichiers pour votre base de données
Choisir le bon schéma et la bonne stratégie de partitionnement pour votre base de données
Choisir l'option et le niveau de compression appropriés pour votre base de données
Normaliser ou dénormaliser le schéma de votre base de données en fonction de vos besoins

Conclusion

SQL Server performance tuning est un processus complexe et continu qui nécessite beaucoup de connaissances et d'expérience. Cependant, en suivant certaines des meilleures pratiques et des outils mentionnés dans cet article de blog, vous pouvez améliorer les performances de vos instances SQL Server de manière significative. N'oubliez pas de toujours tester vos modifications dans un environnement de développement ou de mise en scène avant de les appliquer à la production, et de surveiller l'impact de vos modifications sur les performances du système. Bonne mise au point !

Documentation

Database perf_neartime.docx (1,52 MB)

Database perf_neartime_red.docx (1,79 MB)

Query store results description.docx (359,29 KB)

Database perf_querystore.docx (3,95 MB)

SQL Server Performance Tuning est un processus complexe et minutieux. Confier ce travail à des professionnels.

Je vous assisterai volontiers dans l'optimisation et la mise en place de la base de données de votre entreprise.

Il suffit d'appeler +41 (0)58 590 10 04 ou cnous contacter de la manière qui lui convient le mieux.

nos Services pour vous

Performance Tuning

Introduction SQL Server Performance Tuning

Convention d'utilisation