LinkedIn | FaceBook | Twitter
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:
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.
Running or Idle.
# Preemptive Switches
# Context Switches
Within each Scheduler process, you get another graph that gives you more information per thread that is running:
Memory address of the scheduler.
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:
Currently Executing SQL Statement
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.
PingBack from http://www.artofbam.com/wordpress/?p=8655
I've completed documenting all of the Standard Reports in SQL Server Management Studio. You can get to