Achieving and maintaining optimal operational performance is the holy grail of every database designer and administrator who has created a normalized table. And, like that elusive icon, the search is seemingly never-ending and all-consuming.
Before you can optimize a SQL Server database, you first have to understand how Microsoft has designed the application. With the release of SQL Server 7.0, it was decided that the application would manage more of the CPU task scheduling, removing the burden from, and dependency on, the Windows operating system kernel. The application designated to handle task scheduling is known as the User Mode Scheduler (UMS).
Acting as a thin layer between the operating system and SQL Server, the primary function of the UMS is to keep as much of the scheduling process as possible in user mode. The basis of this logic is that SQL Server is better attuned to its own scheduling needs than the Windows operating system.
In addition to being a better judge of its own needs, SQL Server, through the UMS, also operates in cooperative mode rather than preemptive mode like Windows. The operating system approach forces threads off the processor to allow other threads access. The UMS relies on threads to voluntarily yield when they are finished with the processor.
Because SQL Server runs in an environment where threads are expected to voluntarily yield, a cooperative scheduler like UMS can achieve higher overall efficiency. When to yield is decided at the application level rather than arbitrarily by the operating system.
For a comprehensive look at the nuts and bolts of Microsoft SQL Server, you should consider Ken Henderson's book, "The Guru's Guide to SQL Server Architecture and Internals," published by Addison-Wesley.
When SQL Server is initiated, a UMS scheduler is created for each processor in the machine. The worker pool for the server is distributed evenly across the UMS schedulers, with each scheduler maintaining five support lists that control the scheduling of each thread.
The worker list tracks the available UMS workers. A UMS worker is a Windows thread object or a fiber. Each worker processes a work request in its entirety—automatically. This eliminates context switching and improves the overall efficiency of the requested operation.
The list of UMS workers ready to execute an existing work request is called runnable. Each worker in this list is waiting for its event object to be signaled. When a UMS worker yields, it will check the scheduler's runnable list for the next ready and waiting worker, and signal it to start processing.
UMS workers waiting on a resource to become available are on the waiter list. When the UMS worker using the resource in question is finished, it will check the waiter list for the next available worker and release the resource to it.
Outstanding asynchronous Input/Output requests are maintained by the I/O list. Once an I/O request is added to the list, it is the responsibility of each worker that yields the processor to check to see whether the I/O operation has been completed. If it has, that UMS worker will remove the entry from the I/O list and call the I/O completion routine.
The timer list tracks outstanding UMS timer requests, which is when a UMS worker is instructed to wait for a resource for a specific amount of time before timing out. When a UMS worker yields, it looks for expired timers on the list and, if it finds one, removes it from the timer list and places it on the runnable list.
|Scheduler list flow|
The flowchart shown in Figure A shows the interaction of the scheduler lists. Because scheduling is controlled by SQL Server and the User Mode Scheduler, the database developer and designer have a greater responsibility to write efficient code. Careful consideration and attention must be paid to when and where in the code each operation yields the processor.
Of course, this level of control is also where the true power of coding for SQL Server can be found. Using the UMS, developers can scale server operations more readily, without sacrificing performance and efficiency.
Mark W. Kaelin has been writing and editing stories about the IT industry, gadgets, finance, accounting, and tech-life for more than 25 years. Most recently, he has been a regular contributor to BreakingModern.com, aNewDomain.net, and TechRepublic.