Mythe: SQL Server associe un thread à chaque connexion

Dimanche, août 1, 2010
By David Baffaleuf in SQL Server (dbaffaleuf@capdata-osmozium.com) [71 article(s)]

C’est évidemment faux, mais il est facile de se méprendre sur la question. Pourtant il existe une manière simple de le vérifier. Sur une instance non-idle (avec de l’activité), il suffit de récupérer plusieurs fois de suite la valeur de os_thread_id associé à sa propre connexion:

select os_thread_id from sys.dm_os_threads T
inner join sys.dm_os_workers W on W.worker_address = T.worker_address
inner join sys.dm_os_tasks TA on TA.task_address = W.task_address
inner join sys.dm_exec_requests R on R.session_id = TA.session_id
where R.session_id = @@spid;
GO

Vous verrez qu’il finit par changer, et pourtant la connexion n’a pas été interrompue. L’explication réside dans le modèle de planification adopté par SQL Server depuis 1997 (la 7.0)

Tasks et Workers:

Dans SQL Server, la requête SQL ou l’initialisation de la connexion est une tâche (sys.dm_os_tasks). Le porteur de l’exécution de la tâche est le worker (sys.dm_os_workers). L’idée principale est la suivante: pour pouvoir supporter de nombreuses exécutions de batches SQL, il faut dissocier la tâche (task) de l’exécutant (worker), ce qui permet à un même worker d’exécuter plusieurs tâches successivement. Si vous ouvrez deux connexions sur un système idle, et que dans chaque session vous exécutez la requête ci-dessus, vous avez des chances de tomber sur le même os_thread_id, c’est la preuve que le worker est dissocié de la session ou de la tâche à exécuter. L’objectif est de maintenir un worker en exécution tant qu’il reste des choses à faire.

Organisation des workers:

Les workers sont regroupés par scheduler (UMS ou SQLOS) dans un pool, dont la taille maximale est définie telle que =  (max worker threads / nb of user schedulers). Au cours de l’exécution, les workers sont répartis dans différentes listes:
- worker-list:  les workers y sont stationnés au démarrage de SQL Server. A cet instant là, aucune tâche n’est arrivée au moteur d’exécution.
- runnable-list:  à chaque nouvelle tâche qui arrive, un worker est pris dans la worker-list, lié à la tâche et placé dans la runnable-list. Il est initialisé en attente infinie sur un ‘event‘ privé en utilisant la primitive WaitForSingleObject(). L’event est un objet du noyau windows qui sert comme un drapeau levé ou baissé à signaler à un worker qu’il peut poursuivre son exécution. N’importe quel worker peut prendre n’importe quelle tâche. La runnable-list est l’antichambre de l’exécution.
- waiter-list: lorsqu’une tâche doit attendre la fin d’une opération (la libération d’une ressource comme un latch par exemple), elle est placée en waiter-list. C’est à la charge du worker qui détient la ressource de lui indiquer que la ressource est libérée.
- I/O-list: toutes les tâches en attente de retour d’une I/O asynchrone sont chaînées dans cette liste. C’est le worker qui rend la main qui exécute les routines de completion d’I/Os et qui replace ces workers dans la runnable-list ensuite (cf section suivante).
- timer-list:  gère toutes les tâches en attente de l’expiration d’un timer (comme une trace SQL avec une durée d’exécution…). Comme pour les workers en I/O list, elles sont remises dans la runnable-list par le worker qui rend la main.

Cycle de l’exécution:

Lorsque c’est à son tour, le premier worker dans la runnable-list se voit signaler son event par le worker qui rend la main (= yield), et il peut passer en exécution. A son tour ensuite, il va exécuter sa tâche plus un certain nombre de sub-routines comme par exemple vérifier la liste des I/Os asynchrones en attente et lancer les routines de completion d’I/Os, vérifier la liste des workers en attente d’éveil en timer list, et vérifier l’expiration de certains timers, enfin signaler l’event du worker en tête de runnable-list et se replacer en worker-list en attendant une nouvelle tâche à exécuter.

Si l’instance est peu sollicitée, le même worker peut se retrouver à exécuter plusieurs batches différents à la suite en repassant par la worker-list, la runnable-list puis en exécution. C’est ce qui donne l’impression qu’une connexion  est toujours sur le même thread, alors que c’est juste parce que les autres workers n’ont rien à faire de particulier et restent en worker-list. Il n’y a qu’un seul cas où un nouveau worker est créé lorsqu’une nouvelle connexion arrive, c’est lorsque la worker-list est vide et que tous les workers sont répartis dans les différentes listes waiter, timer, I/O ou runnable. Tant que la valeur de max worker threads n’est pas atteinte, SQL Server créé un nouveau worker et le place avec la tâche de gérer la nouvelle connexion en runnable-list, mais encore une fois, le worker n’est pas lié à la connexion. Une fois revenu en worker-list, il prendra une tâche en provenance d’une autre connexion.

En résumé:

Ce qu’il faut se dire, c’est qu’une instance n’a pas besoin de 1000 threads pour servir 1000 connexions simultanées. De nombreuses connexions restent idle, et il est très rare de devoir manuellement augmenter la valeur de max worker threads même avec une très forte activité et de nombreux utilisateurs. Quelques workers suffisent, et grâce à son système de planification coopératif, SQL Server peut absorber la charge aisément.

En ce sens, la vision que renvoient les DMV sys.dm_exec_sessions et sys.dm_exec_connections est beaucoup plus consistante avec la réalité que ne l’était sysprocesses. Une session en AWAITING COMMAND n’avait pas de thread associé, c’était vaguement une instance de classe chaînée avec d’autres dans une liste.  A partir de SQL Server 2005, seule sys.dm_exec_sessions est liée aux tables de tasks, workers et threads. sys.dm_exec_connections reste à part, ce qui avait tendance à dérouter au début mais qui au final s’avère beaucoup plus logique.

A+ David B.


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