Les attentes dans SQL Server

Lundi, avril 27, 2015
By Benjamin VESAN in SQL Server (bvesan@capdata-osmozium.com) [41 article(s)]

Voici une série d’articles sur un sujet qui me semble important: La gestion des performances au travers des « attentes ».

 

  • Que vous ayez assisté à l’une des présentations de David Baffaleuf ou de moi-même lors des journées SQL Server ( JSS2012JSS2013,JSS2014-1 et JSS2014-2  ),
  • que vous ayez entendu parler des « attentes »  dans la documentation officielle de SQL Server, dans un forum ou dans un des nombreux ouvrages traitant de ce sujet,
  • ou que vous ayez simplement un jour lancé le Moniteur d’Activité de Management Studio,

Vous avez compris que l’on peut identifier et résoudre un grand nombre de problèmes de performances en s’intéressant aux attentes observées sur une instance de bases de données.

 

Pourquoi s’intéresser aux attentes ?

 

Côté requête

Lorsqu’une requête est exécutée, on peut considérer qu’elle passe son temps à attendre:

  • Attendre que le code de la requête soit envoyé depuis l’application cliente jusqu’à l’instance
  • Attendre qu’une tâche soit associée à la requête
  • Attendre que le plan d’exécution soit choisi
  • Attendre que les pages de données soient lues en mémoire ou sur disque
  • Attendre que ces données soient triées
  • Attendre que les données soient renvoyées à l’application cliente

On pourrait considérer que la requête,  lorsque la tâche associée consomme du CPU, est en attente de « fin de traitement CPU ». Mais il est d’usage de dire que le temps passé dans le CPU n’est pas du temps attendu.

 

On peut donc dire (en excluant pour l’instant le cas particulier du parallélisme ) que le temps d’exécution d’une requête correspond à la somme du temps passé dans le CPU et du temps passé sur chaque attente.

Donc en connaissant le temps passé sur chaque attente (on parle de « ventilation des postes d’attente »), il est possible de savoir où la requête a passé plus de temps. Donc de savoir quoi optimiser .

Quelques exemples pour une requête dont le temps d’exécution (entre le moment où l’utilisateur clique sur « Exécuter » et celui où l’application cliente lui rend la main) est de  10 secondes:

  • 6 secondes passées sur le transfert des informations entre l’application cliente et l’instance SQL Server, 1 seconde passée à consommer du CPU, 1 seconde à attendre sur des verrous, 2 secondes passées à lire des données sur disque.

En cherchant à éliminer les attentes sur les verrous, on ne pourra gagner qu’une seconde, soit 10% du temps total d’exécution. Par contre, en s’intéressant la la communication entre l’application et l’instance, on peut espérer gagner jusqu’à 60% de ce temps d’exécution

  • 0.5 seconde pour la communication réseau, 9 secondes à lire des données sur disque, 0.1 seconde  à attendre sur des verrous, 0.3 seconde à consommer du CPU, 0.1 seconde à pour compiler le plan d’exécution.

Il n’y a ici qu’en cherchant à réduire le temps passé à lire les données sur disque que l’on peut sensiblement réduire le temps total d’exécution, et on peut espérer diviser par 10 cette durée ! Attention, on sait simplement que l’on attend « trop » sur la lecture des données, pas si cette attente est due à des disques trop lents ou à des balayages d’objets trop volumineux. Il faudra nécessairement pousser l’investigation pour savoir comment réduire cette attente.

  • 8 secondes à attendre sur des verrous, 2 secondes pour la somme de toutes les autres attentes.

Sans surprise ici, c’est en cherchant à réduire la durée de la ou des demandes de verrous que l’on réduira la temps total d’exécution de la requête.

 

Côté instance

En regardant à un instant T l’ensemble des tâches qui s’exécutent sur l’instance, en regardant pour chaque tâche le poste d’attente, on peut savoir s’il y a une éventuelle contention sur l’instance, ou plus globalement connaître le profile d’activité à cet instant.

Si on observe par exemple 10 tâches en attente de libération d’un verrou pour 11 tâches en tout, il s’agit clairement d’un problème de contention sur la concurrence d’accès

Si on observe 10 tâches en attente de lecture de données sur disque sur 11 tâches en tout, les disques ne sont a priori pas adaptés à soutenir la demande faite par SQL Server.

 

Disposer de ce type d’informations est crucial lorsqu’il s’agit d’identifier la cause d’une saturation d’instance, d’un ralentissement général d’une application. Il permet aussi de savoir s’il peut être intéressant par exemple d’ajouter de la mémoire ou des CPUs à une instance SQL Server…

 

 

 

Mesurer les attentes

 

Le Moniteur d’Activité de Management Studio donne un aperçu de la répartition des attentes à l’instant T. Ces attentes sont classées par catégorie pour faciliter l’identification de contentions.

Les vues sys.dm_exec_requests et sys.dm_os_waiting_tasks donnent l’attente actuelle des requêtes en cours d’exécution, le temps depuis laquelle chaque requête attend , et éventuellement la requête bloquante lorsque l’attente concerne un verrou. En agrégeant les données de ces vues par type d’attente, on peut connaître l’activité globale de l’instance à l’instant T

La vue sys.dm_os_wait_stats permet de connaître la somme des attentes (temps total attendu et nombre de tâches ayant attendu) sur l’instance, depuis son démarrage ou depuis que les compteurs ont été réinitialisés (via l’exécution de dbcc sqlperf(‘sys.dm_os_wait_stats’ clear))

 

Attention, toutes les attentes remontées dans les vues ci-dessus ne correspondent pas à des attentes subies lors d’exécution de requêtes !

Lors de consultation de ces vues, il convient de filtrer ces « fausses attentes ». Les filtres ci-dessous permettent de ne pas afficher la grande majorité de ces fausses attentes, en plus des types d’attentes qui n’ont jamais été observés:

 

select * from sys.dm_os_wait_stats
WHERE 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

 

 

 Comprendre et réduire les attentes

Il existe plusieurs centaines d’attentes (plus de 700 pour SQL Server 2014), les connaitre toutes relève donc de l’impossible. Mais il est revanche possible de connaître les grandes familles, de comprendre à quels mécanismes de SQL Server elles correspondent, et donc de savoir comment les réduire.

C’est ce que je vais tenter de développer dans les quelques articles à venir.

 

 

 

Continuez votre lecture sur le blog :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags: , ,

Leave a Reply