SQL Server Management Studio Standard Reports – Scheduler Health - Carpe Datum - Site Home

I’m continuing a series on the Standard Reports you can find in SQL Server Management Studio (SSMS).

The Scheduler Health report shows information about each scheduled process that the Instance of SQL Server runs on the processor(s) on your system.

In asymmetric multiprocessing operating systems, each level of operations must be specified to the processor it runs on. In asymmetric multiprocessor operating systems (like Windows) you don't have to do that in your application code. You just hand off the code to the operating system and it schedules it in one of a few different modes, one at a time, around the processor ring.

SQL Server is different than many other Microsoft products, since it was originally based on a UNIX product, Sybase. Because of that heritage, you have more control over the processes, threads and even fibers that the CPU uses. It's kind of a strange mix of symmetric and asymmetric modes of operation. For the most part, you can leave these at the defaults, and SQL Server will handle all the work for you. But in some cases you want more control.

You can control three basic parts of the processor. Using the Properties of the Instance you can set how many processors are used by SQL Server, whether the system uses fiber-level control versus thread-level, and what priority SQL Server processes are given. Again, unless you know why you want to change any of these settings, leave them alone!

In the following graphs, I have as much information as I can hyperlinked out to other documentation in case you're not familiar with a particular term or concept.

This report groups information about each Scheduler process under its identification number, or ID. So the first set of information you get is in the main graph:

Heading	Description
SchedulerID	ID of the scheduler. All schedulers that are used to run regular queries have ID numbers less than 255. Those schedulers that have IDs greater than or equal to 255 are used internally by SQL Server, such as the dedicated administrator connection scheduler.
Status	Running or Idle.
CPU ID	ID of the CPU with which this scheduler is associated. If SQL Server is configured to run with affinity, the value is the ID of the CPU on which the scheduler is supposed to be running. 255 = Affinity mask is not specified.
# Preemptive Switches	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	Number of context switches that have occurred on this scheduler. To allow for other workers to run, the current running worker has to relinquish control of the scheduler or switch context.
# Idle Switches	Number of times the scheduler has been waiting for an event while idle.
# Tasks	Number of current tasks that are associated with this scheduler. This count includes the following: Tasks that are waiting for a worker to execute them. Tasks that are currently waiting or running (in SUSPENDED or RUNNABLE state). When a task is completed, this count is decremented.
# Workers	Number of workers that are associated with this scheduler. This count includes workers that are not assigned any task.
Queue Length	Number of tasks in the pending queue on the CPU(s). These tasks are waiting for a worker to pick them up.
# Pending Requests	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.
Load Factor	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. In SQL Server 2000, a task is routed to a particular scheduler. However, in SQL Server 2005, the routing decision is made based on the load on the scheduler. SQL Server 2005 also uses a load factor of nodes and schedulers to help determine the best location to acquire resources. When a task is queued, 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.

Within each Scheduler process, you get another graph that gives you more information per thread that is running:

Heading	Description
Worker Address	Memory address of the scheduler.
Mode	Shows whether the process is Cooperative (will give up its place) or Preemptive (must be interrupted).
Type	Thread or Fiber. Fibers are smaller units of work.
State	Shows whether the process is suspended or running.
IO Count	How much IO the process is using.
Memory Used	How much memory the process is using.
Context Switches Count	Number of context switches that have occurred on this scheduler. To allow for other workers to run, the current running worker has to relinquish control of the scheduler or switch context.
Task Address	Shows the memory address of the current task within the process.
Task IO Count	Shows the IO being used by the task within the process.
# Task Context Switches	Shows the number of context switches for the task within the process.

And even within that graph, you get another one with information about that particular process. A lot of this information is set within the application, so if it doesn't send anything along there might not be a value in all of the columns:

Heading	Description
Session ID	Shows the SQL Server session ID for the task.
Status	Shows the status of the task.
Host Name	Shows the Host name of the system issuing the commands for the task. Sent by the application.
Program Name	Shows the program name of the system issuing the commands for the task. Sent by the application.
Currently Executing SQL Statement	Shows a portion of the SQL statement that is running in the task. Won't show anything for pure engine calls.
Login Name	Shows the login name for the user running the task. Sent by the application.

To find this report, open SSMS, right-click a Server, and then select “Reports” and then “Standard Reports”. This report takes a few moments to render.