{"id":1283,"date":"2010-08-01T22:52:40","date_gmt":"2010-08-01T21:52:40","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=1283"},"modified":"2023-01-04T08:50:38","modified_gmt":"2023-01-04T07:50:38","slug":"mythe-sql-server-associe-un-thread-a-chaque-connexion","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/mythe-sql-server-associe-un-thread-a-chaque-connexion\/","title":{"rendered":"Mythe: SQL Server associe un thread \u00e0 chaque connexion"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

C’est \u00e9videmment faux, mais il est facile de se m\u00e9prendre sur la question. Pourtant il existe une mani\u00e8re simple de le v\u00e9rifier. Sur une instance non-idle (avec de l’activit\u00e9), il suffit de r\u00e9cup\u00e9rer plusieurs fois de suite la valeur de os_thread_id associ\u00e9 \u00e0 sa propre connexion:<\/p>\n

select os_thread_id from sys.dm_os_threads T\r\ninner join sys.dm_os_workers W on W.worker_address = T.worker_address\r\ninner join sys.dm_os_tasks TA on TA.task_address = W.task_address\r\ninner join sys.dm_exec_requests R on R.session_id = TA.session_id\r\nwhere R.session_id = @@spid;<\/span>\r\nGO<\/span><\/span><\/span><\/pre>\n
Vous verrez qu’il finit par changer, et pourtant la connexion n’a pas \u00e9t\u00e9 interrompue. L’explication r\u00e9side dans le mod\u00e8le de planification adopt\u00e9 par SQL Server depuis 1997 (la 7.0)<\/p>\n
Tasks et Workers:<\/h2>\n
Dans SQL Server, la requ\u00eate SQL ou l’initialisation de la connexion est une t\u00e2che <\/strong>(sys.dm_os_tasks<\/em>). Le porteur de l’ex\u00e9cution de la t\u00e2che est le worker<\/strong> (sys.dm_os_workers<\/em>). L’id\u00e9e principale est la suivante: pour pouvoir supporter de nombreuses  ex\u00e9cutions de batches SQL, il faut dissocier la t\u00e2che  (task) de l’ex\u00e9cutant (worker), ce qui permet \u00e0 un m\u00eame worker d’ex\u00e9cuter plusieurs t\u00e2ches successivement. Si vous ouvrez deux connexions sur un syst\u00e8me idle, et que dans chaque session vous ex\u00e9cutez la requ\u00eate ci-dessus, vous avez des chances de tomber sur le m\u00eame os_thread_id, c’est la preuve que le worker est dissoci\u00e9 de la session ou de la t\u00e2che \u00e0 ex\u00e9cuter. L’objectif est de maintenir un worker en ex\u00e9cution tant qu’il reste des choses \u00e0 faire.<\/p>\n
Organisation des workers:<\/h2>\n
Les workers sont regroup\u00e9s par scheduler (UMS ou SQLOS) dans un pool, dont la taille maximale est d\u00e9finie telle que =\u00a0 (max worker threads \/ nb of user schedulers). Au cours de l’ex\u00e9cution, les workers sont r\u00e9partis dans diff\u00e9rentes listes:
\n– worker-list<\/strong>:\u00a0 les workers y sont stationn\u00e9s au d\u00e9marrage de SQL Server. A cet instant l\u00e0, aucune t\u00e2che n’est arriv\u00e9e au moteur d’ex\u00e9cution.
\n– runnable-list<\/strong>:\u00a0 \u00e0 chaque nouvelle t\u00e2che qui arrive, un worker est pris dans la  worker-list, li\u00e9 \u00e0 la t\u00e2che et plac\u00e9 dans la runnable-list. Il est initialis\u00e9 en attente infinie sur un ‘event<\/em>‘ priv\u00e9 en utilisant la primitive WaitForSingleObject(). <\/em>L’event est un objet du noyau windows  qui sert comme un drapeau lev\u00e9 ou baiss\u00e9 \u00e0 signaler \u00e0 un worker qu’il  peut poursuivre son ex\u00e9cution. N’importe quel worker peut prendre n’importe quelle  t\u00e2che. La runnable-list est l’antichambre de l’ex\u00e9cution.
\n– waiter-list<\/strong>: lorsqu’une t\u00e2che doit attendre la fin d’une op\u00e9ration (la lib\u00e9ration d’une ressource comme un latch par exemple), elle est plac\u00e9e en waiter-list. C’est \u00e0 la charge du worker qui d\u00e9tient la ressource de lui indiquer que la ressource est lib\u00e9r\u00e9e.
\n– I\/O-list<\/strong>: toutes les t\u00e2ches en attente de retour d’une I\/O asynchrone sont cha\u00een\u00e9es dans cette liste. C’est le worker  qui rend la main qui ex\u00e9cute les routines de completion d’I\/Os et qui replace ces workers dans la runnable-list ensuite (cf  section suivante).
\n– timer-list<\/strong>:\u00a0 g\u00e8re toutes les t\u00e2ches en attente de l’expiration d’un timer (comme une trace SQL avec une dur\u00e9e d’ex\u00e9cution…). Comme pour les workers en I\/O list, elles sont remises dans la runnable-list par le worker qui rend la main.<\/p>\n
Cycle de l’ex\u00e9cution:<\/h2>\n
Lorsque c’est \u00e0 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\u00e9cution. A son tour ensuite, il va ex\u00e9cuter sa t\u00e2che plus un certain nombre de sub-routines comme par exemple v\u00e9rifier la liste des I\/Os asynchrones en attente et lancer les routines de completion d’I\/Os, v\u00e9rifier la liste des workers en attente d’\u00e9veil en timer list, et v\u00e9rifier l’expiration de certains timers, enfin signaler l’event du worker en t\u00eate de runnable-list et se replacer en worker-list en attendant une nouvelle t\u00e2che \u00e0 ex\u00e9cuter.<\/p>\n
\"\"<\/a><\/p>\n
Si l’instance est peu sollicit\u00e9e, le m\u00eame worker peut se retrouver \u00e0 ex\u00e9cuter plusieurs batches diff\u00e9rents \u00e0 la suite en repassant par la worker-list, la runnable-list puis en ex\u00e9cution. C’est ce qui donne l’impression qu’une connexion\u00a0 est toujours sur le m\u00eame thread, alors que c’est juste parce que les autres workers n’ont rien \u00e0 faire de particulier et restent en worker-list. Il n’y a qu’un seul cas o\u00f9 un nouveau worker est cr\u00e9\u00e9 lorsqu’une nouvelle connexion arrive, c’est lorsque la worker-list est vide et que tous les workers sont r\u00e9partis dans les diff\u00e9rentes listes waiter, timer, I\/O ou runnable. Tant que la valeur de max worker threads n’est pas atteinte, SQL Server cr\u00e9\u00e9 un nouveau worker et le place avec la t\u00e2che de g\u00e9rer la nouvelle connexion en runnable-list, mais encore une fois, le worker n’est pas li\u00e9 \u00e0 la connexion. Une fois revenu en worker-list, il prendra une t\u00e2che en provenance d’une autre connexion.<\/p>\n
En r\u00e9sum\u00e9:<\/h2>\n
Ce qu’il faut se dire, c’est qu’une instance n’a pas besoin de 1000 threads pour servir 1000 connexions simultan\u00e9es. De nombreuses connexions restent idle, et il est tr\u00e8s rare de devoir manuellement augmenter la valeur de max worker threads m\u00eame avec une tr\u00e8s forte activit\u00e9 et de nombreux utilisateurs. Quelques workers suffisent, et gr\u00e2ce \u00e0 son syst\u00e8me de planification coop\u00e9ratif, SQL Server peut absorber la charge ais\u00e9ment.<\/p>\n
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\u00e9alit\u00e9 que ne l’\u00e9tait sysprocesses. Une session en AWAITING COMMAND n’avait pas de thread associ\u00e9, c’\u00e9tait vaguement une instance de classe cha\u00een\u00e9e avec d’autres dans une liste.\u00a0 A partir de SQL Server 2005, seule sys.dm_exec_sessions est li\u00e9e aux tables de tasks, workers et threads. sys.dm_exec_connections reste \u00e0 part, ce qui avait tendance \u00e0 d\u00e9router au d\u00e9but mais qui au final s’av\u00e8re beaucoup plus logique.<\/p>\n
A+ David B.<\/p>\n