Sélectionner une page

Les problèmes de performances sont la plaie des bases SQL, en particulier des bases Oracle. Si disposer de liaisons performantes, de bases de données optimisées et de requêtes propres permet déjà une bonne gestion du temps de traitement global, sans même toucher aux performances matérielles, il peut cependant subsister des problèmes de ralentissements conséquents. Pour ceux qui aiment le peaufinage, ainsi que pour les administrateurs de bases de données qui doivent trouver une solution rapide pour améliorer les performances d’une database bancale en place, il est possible de s’intéresser aux temps d’attente sur les instances sql server.

Quel est l’impact des attentes SQL sur le temps total d’exécution d’une requête ?

Quand on envoie une requête au serveur SQL, elle passe par plusieurs étapes qui consistent principalement à… attendre. D’abord, elle va attendre que l’appli cliente envoie le code de la requête à l’instance. Puis attendre qu’une tâche lui soit associée. Attendre à nouveau qu’un plan d’exécution soit déterminé, puis attendre encore que les pages de données concernées soient lues, que ce soit en mémoire ou sur disque…  ensuite, attendre que toutes ces données soient renvoyées au client… etc.

Pendant que la tâche associée à la requête consomme du CPU, on pourrait penser que la requête elle-même est en attente de « fin de traitement CPU ». Pourtant, le temps passé dans le CPU n’est pas considéré comme du temps « attendu ». Si on exclut le cas de plusieurs requêtes sql lancées simultanément (parallélisme), le temps d’exécution global ressemble à cette formule :

Temps d’exécution de la requête sql = temps passé en CPU + temps passé sur chaque attente (ventilation des postes d’attente)

Si l’on connaît la somme des attentes sql, on peut alors comparer les résultats et déterminer si la requête a passé plus de temps en CPU ou en attentes. De cette façon, il est possible de chercher à optimiser l’un ou l’autre aspect pour augmenter la performance.

Mise en situation : analyse d’attentes sql

Voici 3 exemples d’ une même requête sur sql serveur, dont le temps d’exécution est de 10 secondes entre le moment où l’utilisateur clique sur “exécuter” et celui où il récupère la main.

1er cas : la requête passe 8 secondes à attendre sur des demandes de verrous, et 2 secondes pour la somme de toutes les autres attentes.

Il semble évident ici que l’on va surtout chercher à optimiser la durée des demandes de verrou/déverrouillage.

2ème cas : la requête passe 6 secondes à transférer des infos entre le client et l’instance, 1 sur du CPU, 1 sur des verrous, 2 à lire des données disque.

Cette fois, optimiser les attentes sur les verrous ne permettra de gagner qu’une seconde, soit 10% seulement du temps total d’exécution. Mais 60%  du temps d’exécution est occupé par la communication entre l’appli et l’instance SQL server : c’est sur ce point qu’il conviendrait de porter ses efforts d’optimisation.

3ème cas : la requête met 0,5 secondes à communiquer avec le réseau, 9 secondes de lecture de données sur disque, 0,1 seconde sur des verrous, 0,3 de consommation CPU et 0,1 seconde de compilation du plan d’exécution.

Pour réussir à diviser par 10 le temps total d’exécution de cette requête sql, il n’y a qu’un seul moyen : optimiser le temps passé à lire les données. Cependant, ce calcul nous montre seulement qu’il y a un problème de performance, sans révéler sa nature. On ignore en effet à quoi est due cette attente excessive sur la lecture des données disque : ce peut être à cause de disques trop lents, ou de balayages de fichiers, de pages ou de tables (etc.) trop volumineux… Il conviendra d’analyser précisément la situation afin de savoir où se situe le problème exactement.

Optimiser les performances côté instance server

Pour déterminer la cause d’une saturation d’instance SQL server ou du ralentissement global d’une application, il est nécessaire d’observer de près l’ensemble des tâches qui s’y exécutent. Avant de décider de rajouter de la mémoire ou des CPU pour augmenter les capacités, on peut commencer par rechercher une contention sur l’instance sql, chercher à connaître son profil d’activité ou sa performance à un instant donné.

Par exemple, Si 10 tâches sur les 11 de la requête actuellement en attente le sont parce qu’elles attendent qu’un verrou se libère, le problème se situe nettement au niveau de la concurrence d’accès (contention).
Si sur ces 11 tâches, 10 sont en attente de lecture de données sur disque, il est fort possible que ce soit le matériel qui a du mal à suivre la demande SQL serveur.

Monitorer les attentes SQL

Pour accéder à un aperçu de la répartition des attentes (à un temps donné seulement), vous pouvez lancer le moniteur d’activités de Management Studio. Les attentes SQL y sont listées par catégorie d’attente, afin de faciliter la détection d’éventuelles contentions.

Basculez sur les vues sys.dm_exec_requests et sys.dm_os_waiting_tasks pour examiner l’attente actuelle des requêtes en cours d’exécution, ainsi que la durée depuis laquelle chaque requête attend. Vous y verrez aussi le cas échéant la requête sql bloquante dans le cas d’une attente pour libération de verrou. Ces données agrégées par type d’attente donnent une vue de l’activité globale de l’instance SQL server à un moment bien précis.

Enfin, la vue sys.dm_os_wait_stats donne la somme des attentes (soit le temps total attendu additionné au nombre de tâches ayant attendu) sur l’instance sql, soit depuis son lancement, soit depuis la dernière demande de réinitialisation manuelle (exécutez dbcc sqlperf(‘sys.dm_os_wait_stats’ clear))

Cependant, il y a dans ces vues de « fausses » attentes qui peuvent compliquer la lecture de la performance. Il convient de prendre en compte les attentes subies lors d’une exécution de requêtes, et de filtrer le reste, comme les types d’attente jamais encore observés par exemple.

select * from sys.dm_os_wait_statsWHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER')and wait_type not like 'SLEEP%'and wait_time_ms>0

Il existe près de 700 attentes pour SQL server (2014) : impossible de toutes les connaître par cœur ! Par contre, il est possible de les classer en types, chacun liés à un mécanisme précis de SQL server. Comprendre à quoi chaque classe d’attente correspond permettra ensuite de savoir comment les optimiser et augmenter la performance globale des requêtes lancées.