Dans cet article, nous explorerons les bases de SQLOS – la planification du CPU dans SQL Server. SQLOS (SQL Server Operating System) est une couche au sein de SQL Server qui gère l’allocation et la planification des ressources système, y compris le temps CPU.
Lorsqu’un utilisateur se connecte à une instance de SQL Server, un ID de connexion unique et un ID de session lui sont attribués. Ces ID peuvent être consultés à l’aide de la vue de gestion dynamique (DMV) sys.dm_exec_connections.
Les requêtes exécutées par les sessions utilisateur sont disponibles dans les DMV sys.dm_exec_requests et sys.dm_exec_sql_text(plan_handle). Ces DMV fournissent des informations sur les requêtes en cours d’exécution, y compris le plan d’exécution.
Une fois que le plan d’exécution d’une requête est généré, il est divisé en une ou plusieurs tâches. Le nombre de tâches dépend de la parallélisme de la requête. Ces tâches sont attribuées à des travailleurs, qui sont responsables de l’exécution des tâches. Le nombre maximum de travailleurs attribués à SQL Server dépend du nombre de CPU et de l’architecture matérielle (32 bits ou 64 bits).
Chaque travailleur est associé à un thread, qui est une unité d’exécution au sein du système d’exploitation. La DMV sys.dm_os_threads fournit des informations sur les threads associés aux travailleurs.
Le planificateur est responsable de la planification du temps CPU pour les tâches/travailleurs. Lorsque le service SQL Server démarre, il crée un planificateur pour chaque CPU logique. La DMV sys.dm_os_schedulers fournit des informations sur les planificateurs.
Pendant l’exécution d’une tâche, le planificateur peut maintenir la tâche dans un état RUNNING, RUNNABLE ou SUSPENDED pour diverses raisons. La DMV sys.dm_os_schedulers peut être utilisée pour surveiller l’état des tâches.
Une fois qu’une tâche est terminée, toutes les ressources consommées sont libérées.
Vérifions ce flux avec une expérience simple:
-- Créez une table et insérez quelques enregistrements dans une base de données de test.
DROP DATABASE SQLOS_SCHEDULING
GO
CREATE DATABASE SQLOS_SCHEDULING
GO
USE SQLOS_SCHEDULING
GO
CREATE TABLE tEmployee(intRoll int, strName varchar(50))
GO
SET NOCOUNT ON
INSERT INTO tEmployee VALUES(1001,'AAAA')
GO 10000
-- Obtenez l'ID SPID de cette session. À utiliser ultérieurement.
SELECT @@SPID
-- Exécutez une requête *médiocre* (le parallélisme est forcé avec le traceflag 8649).
SELECT * FROM tEmployee A CROSS JOIN tEmployee B OPTION (RECOMPILE, QUERYTRACEON 8649)
GO 100
La requête ci-dessus prendra quelques secondes à quelques minutes pour récupérer les données. Ouvrez une nouvelle session utilisateur et exécutez les requêtes suivantes une par une:
-- Requête 1: Connexion utilisateur et requête en tant que demande.
SELECT REQ.connection_id, REQ.database_id, REQ.session_id, REQ.command, REQ.request_id, REQ.start_time, REQ.task_address, QUERY.text
FROM SYS.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 53 -- Remplacez 53 par l'ID SPID de la session où la requête tEmployee est en cours d'exécution
-- Requête 2: La requête utilisateur est divisée en 3 tâches (parallélisme forcé)
SELECT task.task_address, task.parent_task_address, task.task_state, REQ.request_id, REQ.database_id, REQ.session_id, REQ.start_time, REQ.command, REQ.connection_id, REQ.task_address, QUERY.text
FROM SYS.dm_exec_requests req
INNER JOIN sys.dm_os_tasks task ON req.task_address = task.task_address OR req.task_address = task.parent_task_address
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 53
-- Requête 3: Chaque tâche est attribuée à un travailleur
SELECT worker.worker_address, worker.last_wait_type, worker.state, task.task_address, task.parent_task_address, task.task_state, REQ.request_id, REQ.database_id, REQ.session_id, REQ.start_time, REQ.command, REQ.connection_id, REQ.task_address, QUERY.text
FROM SYS.dm_exec_requests req
INNER JOIN sys.dm_os_tasks task ON req.task_address = task.task_address OR req.task_address = task.parent_task_address
INNER JOIN SYS.dm_os_workers worker ON task.task_address = worker.task_address
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 53
-- Requête 4: Requête utilisateur en tant que tâches. Tâche attribuée à un travailleur. Chaque travailleur est associé à un thread
SELECT thread.thread_address, thread.priority, thread.processor_group, thread.started_by_sqlservr, worker.worker_address, worker.last_wait_type, worker.state, task.task_address, task.parent_task_address, task.task_state, REQ.request_id, REQ.database_id, REQ.session_id, REQ.start_time, REQ.command, REQ.connection_id, REQ.task_address, QUERY.text
FROM SYS.dm_exec_requests req
INNER JOIN sys.dm_os_tasks task ON req.task_address = task.task_address OR req.task_address = task.parent_task_address
INNER JOIN SYS.dm_os_workers worker ON task.task_address = worker.task_address
INNER JOIN sys.dm_os_threads thread ON worker.thread_address = thread.thread_address
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 53
-- Requête 5: Le temps CPU est planifié pour la tâche par le planificateur
SELECT sch.scheduler_address, sch.runnable_tasks_count, sch.cpu_id, sch.status, thread.thread_address, thread.priority, thread.processor_group, thread.started_by_sqlservr, worker.worker_address, worker.last_wait_type, worker.state, task.task_address, task.parent_task_address, task.task_state, REQ.request_id, REQ.database_id, REQ.session_id, REQ.start_time, REQ.command, REQ.connection_id, REQ.task_address, QUERY.text
FROM SYS.dm_exec_requests req
INNER JOIN sys.dm_os_tasks task ON req.task_address = task.task_address OR req.task_address = task.parent_task_address
INNER JOIN SYS.dm_os_workers worker ON task.task_address = worker.task_address
INNER JOIN sys.dm_os_threads thread ON worker.thread_address = thread.thread_address
INNER JOIN sys.dm_os_schedulers sch ON sch.scheduler_address = worker.scheduler_address
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 53
En exécutant ces requêtes, vous pouvez observer le flux du processus de planification du CPU de SQL Server SQLOS.
Pour plus d’informations approfondies sur SQLOS et les internes de SQL Server, vous pouvez vous référer au livre “Professional SQL Server 2008 Internals and Troubleshooting” de Wrox Press.
Références: