SQL Server : 5 conseils pour éviter les goulets d'étranglement
Les cinq conseils de Basit Farooq aux utilisateurs de SQL Server, de l'élimination des goulets d'étranglement en termes de performances matérielles à la conception des bases de données.
Au cours des dix dernières années, un grand nombre d'applications d'entreprise complexes ont été développées et déployées à l'aide de Microsoft SQL Server. Aujourd'hui, SQL Server est une clé de voûte des applications d'entreprise modernes et est au centre des processus métier de nombreuses entreprises leaders. Les applications SQL Server vont des applications métier de production aux applications de commerce électronique orientées client et self-service Web, en passant par les systèmes internes de gestion de la relation client et d'aide à la décision. Par conséquent, les performances et l'évolutivité de SQL Server figurent en haut des listes de priorités des services informatiques, et l'optimisation de ces deux caractéristiques est l'une des principales tâches de tous les administrateurs de bases de données SQL Server.
Cependant, de nombreux systèmes SQL Server pâtissent de performances et d'une évolutivité médiocres, souvent à cause d'insuffisances dans la conception des bases de données et des index, et d'un système SQL Server mal adapté à la charge de travail.
La raison de cette situation est que le développement de tout projet SQL Server de grande ampleur est principalement axé sur les fonctionnalités : les performances et l'évolutivité sont souvent prises en compte dans un deuxième temps.
Même si le dépannage des problèmes de performance des bases de données SQL Server est une tâche difficile, une amélioration importante des performances est possible en un temps relativement court.
Goulets d'étranglement en termes de performances matérielles
Mémoire
La mémoire a plus d'incidence sur les performances de SQL Server que tout autre composant matériel. Par conséquent, il est nécessaire de surveiller régulièrement son utilisation sur les systèmes SQL Server et de s'assurer que le pourcentage de mémoire disponible est supérieur à 20 %. Si les utilisateurs rencontrent des problèmes de performances et que le pourcentage de mémoire disponible descend en dessous de 20 %, le problème est dû à une allocation de mémoire insuffisante.
Surveillez le compteur des performances d'espérance de vie moyenne des pages et assurez-vous qu'il est toujours au-dessus de 300 secondes (5 minutes). Toute valeur inférieure indique soit une mauvaise conception des index, entraînant une augmentation des entrées/sorties (E/S) sur disque et une utilisation moins efficace de la mémoire, soit une mémoire tout simplement insuffisante. Surveillez les débits de pagination sur le système SQL Server : ceux-ci doivent régulièrement dépasser 1000 pages par seconde.
Vérifiez le gestionnaire de tampons MSSQL (MSSQL Buffer Manager) de l'objet PerfMon ainsi que les compteurs de performance de la mémoire.
Surveillez également le compteur Memory Grants Pending (Demandes de mémoire en attente) dans les compteurs du gestionnaire de mémoire SQL Server (SQL Server Memory Manager) de l'objet PerfMon. Ce dernier indique le nombre total de processus par seconde en attente d'allocation de mémoire de l'espace de travail.
Généralement, les petites transactions OLTP n'ont pas besoin d'allocations de mémoire importantes. Si une allocation de mémoire destinée à ce type de transaction excède zéro, cela indique que la mémoire du système SQL Server est insuffisante.
L'un des moyens de gérer les goulets d'étranglement liés à la mémoire consiste à chercher les processus qui consomment beaucoup de mémoire et peuvent être utilisés pour identifier les problèmes d'application potentiels tels que les fuites de mémoire.
Vous pouvez également modifier les demandes d'optimisation des performances pour réduire la mémoire sollicitée. Il existe une autre approche qui consiste à effectuer un scale-up de l'environnement SQL Server en lui ajoutant de la mémoire physique (RAM). Cette approche est généralement efficace pour résorber les goulets d'étranglement liés à la mémoire.
Utilisation des E/S sur disque
Comparées à d'autres ressources matérielles, les entrées/sorties de stockage sont généralement les ressources systèmes les plus lentes dans SQL Server. Par conséquent, il est important de surveiller le système de stockage afin de déceler tout blocage.
Si vous détectez un goulet d'étranglement, tentez de déterminer si vous pouvez ou non optimiser la conception et la configuration du système de stockage afin de le rendre évolutif et d'en améliorer les performances.
Examinez les compteurs de disque PerfMon Average Disk Sec/Read (Durée moyenne des lectures sur disque) et Average Disk Sec/Write (Durée moyenne des écritures sur disque). Assurez-vous que la durée d'une lecture ou d'une écriture est inférieure à 12 millisecondes (valeur idéale) pour les systèmes OLTP et supérieure pour les systèmes d'aide à la décision.
Comme avec la mémoire, le meilleur moyen de résoudre un goulet d'étranglement lié aux performances des E/S sur disque est d'effectuer un scale-up de l'environnement SQL Server en remplaçant les disques existants par des disques plus rapides. Ceux-ci supporteront mieux la charge E/S et la répartiront entre plusieurs disques physiques. Pensez également à défragmenter régulièrement les disques de données.
CPU
Les goulets d'étranglement liés aux performances du processeur peuvent avoir plusieurs causes. Ils peuvent être dus à un plan de requête non optimal, à une conception inadéquate d'une application ou d'une base de données, à une mauvaise configuration de SQL Server ou à un manque de ressources matérielles.
Examinez les compteurs CPU et de processeur PerfMon du système d'exploitation Processor Queue Length (Longueur de la file d'attente du processeur) afin de vérifier que le nombre de cycles d'UC est de huit maximum. Si ce nombre est supérieur à douze, cela signifie que l'UC est la cause du problème de performances.
Une fois que vous avez identifié un goulet d'étranglement de l'UC, utilisez la vue de gestion dynamique (DMV, dynamic management view) sys.dm_os_wait_stats pour identifier les dix requêtes de l'UC qui ont les pires performances, comme indiqué ci-dessous.
SELECT TOP 10 (a.total_worker_time / a.execution_count) AS [Avg_CPU_Time]
,Convert(VARCHAR, Last_Execution_Time) AS [Last_Execution_Time]
,Total_Physical_Reads
,SUBSTRING(b.TEXT, a.statement_start_offset / 2, (
CASE
WHEN a.statement_end_offset = - 1
THEN len(convert(NVARCHAR(max), b.TEXT)) * 2
ELSE a.statement_end_offset
END - a.statement_start_offset
) / 2) AS [Query_Text]
,dbname = Upper(db_name(b.dbid))
,b.objectid AS 'Object_ID', B.*
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
ORDER BY [Avg_CPU_Time] DESC
Vous pouvez alors optimiser ces requêtes et les index sous-jacents en vue d'éliminer le goulet d'étranglement. De plus, configurez votre système SQL Server de manière à utiliser toutes les machines d'UC disponibles. Vous pouvez également effectuer un scale-up de votre système SQL Server en ajoutant des UC ou en le mettant à niveau vers un nouveau serveur équipé d'UC plus nombreuses et plus rapides.
Problèmes de conception des bases de données
Base de données hautement normalisée
Une base de données mal conçue n'est pas performante. Par exemple, la base de données hautement normalisée est associée à des jointures relationnelles complexes. Il en résulte de longues requêtes qui gaspillent les ressources système telles que l'UC, la mémoire et les E/S du disque. De toute évidence, une base de données hautement normalisée nuit aux performances de SQL Server et des bases de données. La règle d'or, pour écrire des requêtes efficaces, est de revoir la conception de la base de données si une opération exige au moins cinq jointures de table.
Index dupliqués et inutilisés
Les index sont la solution à de nombreux problèmes de performances, mais l'utilisation d'un trop grand nombre d'index sur des tables fréquemment mises à jour peut entraîner un surcroît de gestion. En effet, SQL Server doit alors exécuter des tâches en plus pour actualiser continuellement les index pendant les opérations d'insertion/mise à jour/suppression. Cela signifie que le moteur de base de données SQL Server a besoin de plus de temps lorsqu'il met les données à jour dans la table en fonction du nombre et de la complexité des index.
En outre, la maintenance des index peut accroître l'utilisation de l'UC et des E/S, ce qui risque de nuire aux performances dans un système qui effectue beaucoup d'écritures. Supprimez les éventuels index en double et redondants, car ils épuisent les ressources système sans raison valable.
Dans SQL Server, la vue DMV sys.dm_db_index_usage_stats permet d'identifier les index inutilisés. Elle affiche des statistiques sur l'utilisation un index pour résoudre des requêtes. Vous pouvez également exécuter l'Assistant Paramétrage du moteur de base de données (DTA, Database Engine Tuning Advisor) pour identifier les index inutilisés. Pour plus d'informations, reportez-vous au tutoriel suivant : Database Engine Tuning Advisor
Pour supprimer les index en double, lisez mon article sur la détection et la suppression des index en double.
