sys.dm_os_schedulers (Transact-SQL)
Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks.
Column name |
Data type |
Description |
|---|---|---|
scheduler_address |
varbinary(8) |
Memory address of the scheduler. Is not nullable. |
parent_node_id |
int |
ID of the node that the scheduler belongs to, also known as the parent node. This represents a nonuniform memory access (NUMA) node. Is not nullable. |
scheduler_id |
int |
ID of the scheduler. All schedulers that are used to run regular queries have ID numbers less than 1048576. Those schedulers that have IDs greater than or equal to 1048576 are used internally by SQL Server, such as the dedicated administrator connection scheduler. Is not nullable. |
cpu_id |
smallint |
CPU ID assigned to the scheduler. Is not nullable.
|
status |
nvarchar(60) |
Indicates the status of the scheduler. Can be one of the following values:
Is not nullable. HIDDEN schedulers are used to process requests that are internal to the Database Engine. VISIBLE schedulers are used to process user requests. OFFLINE schedulers map to processors that are offline in the affinity mask and are, therefore, not being used to process any requests. ONLINE schedulers map to processors that are online in the affinity mask and are available to process threads. DAC indicates the scheduler is running under a dedicated administrator connection. HOT ADDED indicates the schedulers were added in response to a hot add CPU event. |
is_online |
bit |
If SQL Server is configured to use only some of the available processors on the server, this configuration can mean that some schedulers are mapped to processors that are not in the affinity mask. If that is the case, this column returns 0. This value means that the scheduler is not being used to process queries or batches. Is not nullable. |
is_idle |
bit |
1 = Scheduler is idle. No workers are currently running. Is not nullable. |
preemptive_switches_count |
int |
Number of times that workers on this scheduler have switched to the preemptive mode. To execute code that is outside SQL Server (for example, extended stored procedures and distributed queries), a thread has to execute outside the control of the non-preemptive scheduler. To do this, a worker switches to preemptive mode. |
context_switches_count |
int |
Number of context switches that have occurred on this scheduler. Is not nullable. To allow for other workers to run, the current running worker has to relinquish control of the scheduler or switch context.
|
idle_switches_count |
int |
Number of times the scheduler has been waiting for an event while idle. This column is similar to context_switches_count. Is not nullable. |
current_tasks_count |
int |
Number of current tasks that are associated with this scheduler. This count includes the following:
When a task is completed, this count is decremented. Is not nullable. |
runnable_tasks_count |
int |
Number of workers, with tasks assigned to them, that are waiting to be scheduled on the runnable queue. Is not nullable. |
current_workers_count |
int |
Number of workers that are associated with this scheduler. This count includes workers that are not assigned any task. Is not nullable. |
active_workers_count |
int |
Number of workers that are active. An active worker is never preemptive, must have an associated task, and is either running, runnable, or suspended. Is not nullable. |
work_queue_count |
bigint |
Number of tasks in the pending queue. These tasks are waiting for a worker to pick them up. Is not nullable. |
pending_disk_io_count |
int |
Number of pending I/Os that are waiting to be completed. Each scheduler has a list of pending I/Os that are checked to determine whether they have been completed every time there is a context switch. The count is incremented when the request is inserted. This count is decremented when the request is completed. This number does not indicate the state of the I/Os. Is not nullable. |
load_factor |
int |
Internal value that indicates the perceived load on this scheduler. This value is used to determine whether a new task should be put on this scheduler or another scheduler. This value is useful for debugging purposes when it appears that schedulers are not evenly loaded. The routing decision is made based on the load on the scheduler. SQL Server also uses a load factor of nodes and schedulers to help determine the best location to acquire resources. When a task is enqueued, the load factor is increased. When a task is completed, the load factor is decreased. Using the load factors helps SQL Server OS balance the work load better. Is not nullable. |
yield_count |
int |
Internal value that is used to indicate progress on this scheduler. This value is used by the Scheduler Monitor to determine whether a worker on the scheduler is not yielding to other workers on time. This value does not indicate that the worker or task transitioned to a new worker. Is not nullable. |
last_timer_activity |
bigint |
In CPU ticks, the last time that the scheduler timer queue was checked by the scheduler. Is not nullable. |
failed_to_create_worker |
bit |
Set to 1 if a new worker could not be created on this scheduler. This generally occurs because of memory constraints. Is nullable. |
active_worker_address |
varbinary(8) |
Memory address of the worker that is currently active. Is nullable. For more information, see sys.dm_os_workers (Transact-SQL). |
memory_object_address |
varbinary(8) |
Memory address of the scheduler memory object. Not NULLABLE. |
task_memory_object_address |
varbinary(8) |
Memory address of the task memory object. Is not nullable. For more information, see sys.dm_os_memory_objects (Transact-SQL). |
quantum_length_us |
bigint |
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. Exposes the scheduler quantum used by SQLOS. |
Permissions
Requires VIEW SERVER STATE permission on the server.
Examples
A. Monitoring hidden and nonhidden schedulers
The following query outputs the state of workers and tasks in SQL Server across all schedulers. This query was executed on a computer system that has the following:
Two processors (CPUs)
Two (NUMA) nodes
One CPU per NUMA node
Affinity mask set to 0x03.
SELECT
scheduler_id,
cpu_id,
parent_node_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count
FROM sys.dm_os_schedulers;
Here is the result set.
scheduler_id cpu_id parent_node_id current_tasks_count
------------ ------ -------------- -------------------
0 1 0 9
257 255 0 1
1 0 1 10
258 255 1 1
255 255 32 2
runnable_tasks_count current_workers_count
-------------------- ---------------------
0 11
0 1
0 18
0 1
0 3
active_workers_count work_queue_count
-------------------- --------------------
6 0
1 0
8 0
1 0
1 0
The output provides the following information:
There are five schedules. Two schedulers have an ID value < 1048576. Schedulers with ID >= 1048576are known as hidden schedulers. Scheduler 255 represents the dedicated administrator connection (DAC). There is one DAC scheduler per instance. Resource monitors that coordinate memory pressure use scheduler 257 and scheduler 258, one per NUMA node
There are 23 active tasks in the output. These tasks include user requests in addition to resource management tasks that have been started by SQL Server. Examples of SQL Server tasks are RESOURCE MONITOR (one per NUMA node), LAZY WRITER (one per NUMA node), LOCK MONITOR, CHECKPOINT, and LOG WRITER.
NUMA node 0 is mapped to CPU 1 and NUMA node 1 is mapped to CPU 0. SQL Server typically starts on a NUMA node other than node 0.
With runnable_tasks_count returning 0, there are no actively running tasks. However, active sessions may exist.
Scheduler 255 representing DAC has 3 workers associated with it. These workers are allocated at SQL Server startup and do not change. These workers are used to process DAC queries only. The two tasks on this scheduler represent a connection manager and an idle worker.
active_workers_count represents all workers that have associated tasks and are running under non-preemptive mode. Some tasks, such as network listeners, run under preemptive scheduling.
Hidden schedulers do not process typical user requests. The DAC scheduler is the exception. This DAC scheduler has one thread to process requests.
B. Monitoring nonhidden schedulers in a busy system
The following query shows the state of heavily loaded nonhidden schedulers, where more requests exist than can be handled by available workers. In this example, 256 workers are assigned tasks. Some tasks are waiting for an assignment to a worker. Lower runnable count implies that multiple tasks are waiting for a resource.
[!UWAGA]
You can find the state of workers by querying sys.dm_os_workers. For more information, see sys.dm_os_workers (Transact-SQL).
Here is the query:
SELECT
scheduler_id,
cpu_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;
Here is the result set.
scheduler_id current_tasks_count runnable_tasks_count
------------ ------------------- --------------------
0 144 0
1 147 1
current_workers_count active_workers_count work_queue_count
--------------------- -------------------- --------------------
128 125 16
128 126 19
By comparison, the following result shows multiple runnable tasks where no task is waiting to obtain a worker. The work_queue_count is 0 for both schedulers.
scheduler_id current_tasks_count runnable_tasks_count
------------ ------------------- --------------------
0 107 98
1 110 100
current_workers_count active_workers_count work_queue_count
--------------------- -------------------- --------------------
128 104 0
128 108 0
Zobacz także
Odwołanie
Dynamic Management Views and Functions (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)