Les attentes sont aux threads SQL Server ce qu’elles sont aux patients dans une salle d’attente. Voilà !

Bien entendu ce n’est pas aussi simple que cela. Démarrons donc une série de billets sur les attentes au sein du moteur de bases de données de SQL Server, en détaillant un peu le mode d’exécution des requêtes.

Modes d’exécution des processus : avant et après SQL Server 2000

Sur les versions antérieures à SQL Server 2005, une grande partie des opérations étaient effectuées par le système d’exploitation lui-même. Ceci fait que jusqu’à SQL Server 2000, il était difficile de comprendre un problème de performance, c’est à dire d’obtenir des détails sur le traitement interne des processus par Windows. Cinq ans et une tétra-volée de lignes de code plus tard, Microsoft nous livre un nouvel opus de SQL Server, estampillé 2005, avec un nouveau composant majeur : SQL Operating System (SQLOS). Celui-ci est la fondation du succès actuel du moteur, et il nous permet, à l’aide des vues et fonctions de gestion dynamique (DMVs, DMFs, ou DMOs dans la littérature), de mieux comprendre ce qui se passe lorsque l’on trouve que l’instance, la base de données, ou une requête se comportent anormalement.

Outre le fait que le moteur est, avec cette version, devenu hautement configurable, il implémente un mode d’exécution simultané de processus en mode coopératif, à la différence de Windows, qui les exécute par préemption. En effet, le système d’exploitation exécute un processus en octroyant à celui-ci un certain temps, qu’on appelle quantum, et une priorité par rapport aux autres processus. Cette priorité varie en fonction des ressources matérielles disponibles, de l’activité courante, … Il fait donc s’exécuter tour à tour des processus en interrompant l’exécution d’autres.

On voit donc que cela se pose directement en opposition à la concurrence d’accès que doit supporter un moteur de bases de données relationnelles SQL : les interruptions d’exécutions peuvent se produire pour plusieurs processus de SQL Server, mais aussi être générés par les processus d’autres applications s’exécutant sur la même machine (antivirus par exemple, …). De là le mode coopératif, qui laisse le soin au moteur de gérer ses propres processus, à de rares exceptions près (création des fichiers d’une base de données, …). Ce mode procure un excellent gain de performance observable dès la fin de la migration d’une base SQL Server 2000 à 2005, mais pose aussi une nouvelle problématique : la concurrence d’accès aux ressources logiques ou physiques que gère le moteur, qui doivent souvent être sérialisées.

Planificateurs, tâches, et unités d’exécution
Le mode coopératif d’exécution de processus est supporté par un modèle de traitement différent, qui suit le schéma suivant :

Les sessions
C’est la connexion d’une application cliente à l’instance SQL Server. On peut connaître le détail de chacune des sessions ouvertes sur l’instance en interrogeant la vue de gestion dynamique sys.dm_exec_sessions. Les sessions dont la colonne session_id ont une valeur inférieure à 50 sont des sessions ouvertes par le système lui même, pour la gestion de ses processus d’arrière-plan. Il est possible que, lorsqu’une charge de travail importante s’exécute, le moteur ouvre une session dont la valeur de session_id est supérieur à 50. Si on veut donc être sûr de ne requêter que les sessions utilisateur, on peut filtrer par la colonne is_user_process.

Les requêtes
Ce sont, au sens logique, les requêtes en cours d’exécution par le moteur. Elles sont elles aussi exposées par une vue de gestion dynamique : sys.dm_exec_requests. Ici aussi, on retrouve de nombreuses caractéristiques, parmi lesquelles le type et la durée d’attente (wait_type, last_wait_type, et wait_time), le numéro de la session qui bloque l’exécution de la requête (blocking_session_id). A ce stade du modèle d’exécution des requêtes, on voit donc que la gestion des accès concurrentiels aux ressources est au cÅ“ur du moteur de base de données.

En suivant le schéma ci-dessus, on peut lier sys.dm_exec_sessions à sys.dm_exec_requests sur la colonne session_id, et cette dernière à sys.dm_os_tasks sur la colonne task_address. Et la colonne task_address, me direz-vous ? voici …

Les tâches
Elles incarnent le travail qui doit être effectué par SQLOS pour résoudre une requête; à une requête peuvent correspondre plusieurs tâches. Lorsque le moteur soumet une requête à exécution, il crée une série de tâches. On peut en obtenir les détails en interrogeant la vue sys.dm_os_tasks. Même sur une instance où aucune requête n’est en cours d’exécution, l’interrogation de cette vue nous retourne quelques lignes : comme évoqué, SQL Server exécute ses propres processus en arrière-plan; c’est d’ailleurs ce que l’on peut constater en observant la colonne session_id : nombreuses sont les lignes pour lesquelles la valeur est inférieure à 50. Ceci correspond à ce que nous avons observé à l’interrogation de sys.dm_exec_sessions. Il en va de même pour la colonne request_id : elle nous permet de réaliser une jointure avec la vue sys.dm_exec_requests. Et la colonne worker_address, me direz-vous ? voici …

Les unités d’exécution
Les unités d’exécution effectuent le travail de résolution de la requête que l’on vient de soumettre, qui leur est ordonné par les tâches. A une tâche correspond une seule unité d’exécution.

Elles sont groupées dans un pool, et à tout instant, on peut (généralement !) les classer en deux catégories : soit elles sont inoccupées (elles bullent, potentiellement en regardant les copines travailler), soit elles exécutent une tâche. Lorsque toutes les unités d’exécution sont occupées, la tâche est alors mise en attente jusqu’à ce que l’une d’entre-elles devienne disponible.

Le nombre d’unités d’exécution est automatiquement calculé par SQL Server durant l’installation. Pour connaître le nombre d’unités d’exécution créées par SQL Server, il suffit de s’en remettre à la table exposée sur cette page de la documentation.

Le nombre d’unités d’exécution est configurable, et est par défaut à zéro : c’est à dire qu’on laisse SQL Server avec la configuration d’installation. Dans la très grande majorité des cas, il n’y a aucun besoin de modifier le nombre d’unités d’exécution. Bien sûr, un fil d’exécution nécessite un peu de mémoire pour s’exécuter : 2 Mo sur les machines dont le CPU fonctionne avec des mots de 64 bits.

On peut en obtenir les détails en interrogeant la vue de gestion dynamique sys.dm_os_workers. Celle-ci expose plusieurs colonnes renseignant sur l’état de l’unité, avec ses statistiques d’exécution, et expose deux colonnes : scheduler_address, qui permet de faire la jointure avec la vue sys.dm_os_schedulers, et task_address avec la vue sys.dm_os_tasks. La colonne la plus intéressante pour le sujet que nous traitons est state, qui peut prendre les valeurs suivantes :

• INIT : SQLOS prépare l’unité d’exécution
• RUNNING : l’unité d’exécution exécute actuellement un travail sur un processeur
• RUNNABLE : l’unité d’exécution est en attente d’un processeur pour exécuter un travail
• SUSPENDED : l’unité d’exécution est en attente de l’accès à une ressource

Comme nous le verrons plus loin dans cet article, les unités d’exécution passent de l’état RUNNING à SUSPENDED, puis à RUNNABLE, et de nouveau à RUNNING jusqu’à ce que le travail qu’elles ont à exécuter soit terminé.

Et la colonne thread_address, me direz-vous ? voici …

Les fils d’exécution
L’unité d’exécution ne réalise pas précisément l’exécution elle-même : elle demande un fil au système d’exploitation. C’est ce qu’expose la vue de gestion dynamique sys.dm_os_threads.

Les planificateurs
Un planificateur est en charge de gérer les unités d’exécution. En ce sens, c’est un peu le chef d’orchestre de l’exécution concurrente des requêtes, qu’elles proviennent d’applications ou des processus internes à SQL Server. Lorsqu’une tâche requiert un core pour s’exécuter, c’est le planificateur qui assigne la tâche à un des cores disponibles.

C’est aussi ce composant qui s’assure que les unités d’exécution coopèrent en cédant le core auxquels elles sont attachées lorsqu’elles atteignent leur quantum. On pourrait alors argumenter que ce mode d’exécution n’est pas si coopératif que son nom le laisse entendre. Le quantum est en fait en place parce que le planificateur ne permet qu’à une seule unité d’exécution d’occuper un core. S’il n’existait pas, alors l’unité d’exécution pourrait occuper un core indéfiniment.

Chaque core de CPU, qu’il soit logique ou physique, dispose d’un (et d’un seul) planificateur. Par exemple, supposons que l’on dispose d’une machine équipée de deux processeurs à huit cÅ“urs chacun : il y a aura donc 16 planificateurs.

On peut obtenir les détails de chaque planificateur en interrogeant la vue de gestion dynamique sys.dm_os_schedulers :

On peut y voir à quel core le planificateur est attaché, son statut (VISIBLE ONLINE : utilisé pour les requêtes utilisateur, HIDDEN ONLINE : utilisé par les processus d’arrière plan du moteur), le quantum qui lui est attribué (toujours à 4000 µs, et ce n’est pas configurable), et le temps CPU consommé par chaque planificateur. Si l’on rajoute les colonnes :

• current_workers_count
• active_workers_count
• current_tasks_count
• runnable_tasks_count
• work_queue_count

On a alors une idée de la charge que supporte l’instance SQL Server étudiée. Par exemple, si la dernière colonne de cette liste montre des nombres élevés, il est probable que SQL Server soit actuellement sous pression CPU.

Vous remarquerez aussi un planificateur dont le statut est VISIBLE ONLINE (DAC) : la fonctionnalité Dedicated Administrator Connection permet de se connecter à une instance SQL Server lorsqu’elle ne réagit plus (surcharge CPU par exemple, …). Ce planificateur lui est dédié.

Nous avons vu comment le moteur distribue le travail aux CPUs pour maximiser la concurrence d’exécution des requêtes. Voyons ce qui se passe lorsque le planificateur doit placer une requête en attente, que ce soit pour l’accès à une ressource, l’acquisition d’un verrou, ou tout simplement lorsque le quantum est atteint …

Les attentes

Les fils d’exécution suivent les phases et les états suivant les deux graphes suivants, qui sont superposables :

Comme nous l’avons vu, les unités d’exécution passent de l’état RUNNING à SUSPENDED à RUNNABLE, dans cet ordre (à de rares exceptions près, notamment lors de l’utilisation de la fonctionnalité Resource Governor). Les attentes représentent le temps que les unités d’exécution participant à l’exécution d’une requête on passé entre le passage d’un de ces états à un autre. Ce temps est subdivisé en deux catégories :

• L’attente de l’accès à une ressource : la ressource peut être un planificateur, le gestionnaire de ressources pour les requêtes parallélisées, un objet de verrouillage (pose d’un verrou sur base de données, fichier de base de données, un schéma, une table, une page, une clé, une étendue, les métadonnées, les unités d’allocation, la CLR, ou pose de loquets (latch dans la littérature) … On l’appelle compendieusement attente de ressource.
• L’attente de la disponibilité d’un processeur après que la ressource soit devenue disponible : c’est ce qu’on appelle de façon plus concise l’attente de signal.

Le temps d’attente est la somme de ces deux temps. Forts de cela, votre Å“il avisé vous a certainement amené à vous demander : Pourquoi avoir utilisé les deux termes Liste et Queue sur le schéma ci-dessus ?

La liste des unités d’exécution
Cette liste des unités d’exécution à l’état SUSPENDED n’a pas d’ordre ni de priorité de traitement. Toute unité d’exécution peut y rester sans limite de temps, et le nombre d’éléments que cette liste peut contenir n’a pas non plus de limite.

L’unité d’exécution est ajoutée à cette liste lorsque son état passe de RUNNING à SUSPENDED. Elle en est retirée lorsque son état passe de SUSPENDED à RUNNABLE : ceci se produit lorsque cette dernière est notifiée que la ressource qu’elle attendait est maintenant disponible, et ce toujours sans ordre ni priorité de traitement.

Ces attentes sont exposées par la vue de gestion dynamique sys.dm_os_waiting_tasks.

Si cela vous a fait penser à l’attente de pintes au zinc d’un bar bondé, sachez que vous n’êtes pas seul !

La queue des unités d’exécution en attente d’un core
Cette queue contient la liste des unités d’exécution dont l’état est RUNNABLE, et fonctionne comme un FIFO. L’unité d’exécution qui se trouve donc être entrée la première dans la queue est la première à être attachée à un core disponible.

Une unité d’exécution entre dans cette queue lorsque son état passe de SUSPENDED à RUNNABLE. Ceci se produit par exemple lorsqu’une autre unité d’exécution en cours de traitement par l’un des cores alloués à l’instance SQL Server dépasse son quantum, ou que son état devient SUSPENDED.

Le nombre d’éléments de cette queue est exposé par la vue de gestion dynamique sys.dm_os_schedulers, à travers la colonne runnable_tasks_count.

Si cela vous a fait penser à l’attente pour l’accès aux toilettes d’un bar bondé, sachez que vous n’êtes pas seul !

Pour illustrer tout cela, le schéma plus haut peut être complété par les deux losanges rouges ci-dessous :

Voilà pour la description du fonctionnement de l’exécution des requêtes.
Dans un billet à venir, nous verrons les types d’attentes les plus communs, et comment on peut les interpréter.

A bientôt, et bonne gestion des attentes. N’hésitez pas à commenter cet article !