Sql-server – Windows OS Quantum vs. SQL OS Quantum

sql server

Simple Question

How is the SQL Server Quantum (4 ms) synchronised with the Server OS Quantum (normally: 187.5 ms)?

Simple Question Explained

After 184 ms of OS quantum being used (which corresponds to 46 full SQL quantums) the OS quantum has 3.5 ms of time before it will have to hand over the schedule to a different process. The SQL OS starts a quantum (4 ms) and after 3.5 ms, the OS quantum has decided to stop the current SQL OS thread which still has 0.5 ms before it would yield the schedule. What happens now?

Deep Dive on OS Quantum

In the next couple of sections I'll write up what I have found so far regarding the OS quantum and how the duration of a quantum can be calculated. The duration of an OS "quantum" is based on "ticks" and the duration of the "tick" itself is based on the "clock interval" which is normally 15.625000 ms. But let me elaborate a bit…

Tick

In the Blog article Know Thy Tick the author Jim explains the basics of clock intervals (aka "ticks") and what they are for.

When I read something like “the clock interval… for most x86 multiprocessors is about 15 milliseconds” I’m compelled to determine the value of my clock, or “tick”, interval. Fortunately, the book I read this quote in, Windows Internals Fourth Edition provides a reference for helping me with my affliction.
…
The author, Mark Russinovich, of the aforementioned book has graciously made the utility ClockRes available on his web site. Running this utility, I was able to determine that the clock interval on my x86 multiprocessor PC is 15.625000 ms. Interesting, but my curious mind wants to know more.

Quantum

The author of the article goes on to explain in his second article that…

Of course the real reason why the tick interval is important is that it affects thread scheduling. The Windows scheduler gives each thread a “quantum” of time to execute before allowing another task, at the same priority level, to run. The quantum that the scheduler assigns to a thread is a multiple of the tick interval. The specific quantum value chosen for a specific thread is bit beyond where I want to go with this article.

Ok, so I know what a quantum is, but not how long a quantum will run.

For now, let’s just examine the default quantum value for a foreground thread in XPe. In this case the Windows scheduler assigns a quantum of 18 or 6 tick intervals. (Yes, to convert quantum to tick intervals, one must divide by 3. …, but the reason for the multiple is to allow the scheduler the ability to “charge” a thread for doing an operation which causes it to suspend.)

We now know that a clock interval (tick) should be around 15.625000 ms and on a Windows Desktop OS where the default quantum is 18 that this will result in 6 ticks or 93.750000 ms (18 / 3 * 15.625000 ms).

On a Windows Server OS the default quantum is different. The "Processor scheduling" setting is set to "Background Services"

This setting can be found via "System Settings | Advanced (tab) | Performance (section) | Settings …" which will open "Perofrmance Options | Advanced (tab) | Processor scheduling"

The default quantum settings are then 36 (Background) to 36 (Foreground). The quantum is larger and hence longer. This is double the amount of the 93.750000 ms of the 18 (6 tick) quantum foreground setting on a Windows Desktop OS, which on a server OS set up for Background Services is around 187.500000 ms.

Observation / Explanation

When you change the setting from "Background services" to "Applicaitons" on either a server or desktop, then the HKLM\SYSTEM \CurrentControlSet\ Control\ PriorityControl\ Win32PrioritySeparation key in the registry is changed from 0x18 to 0x02. What is the default quantum value for 0x02? This can be found in a comment:

The value 0x02 implies that the "Short vs. Long" and "Variable vs. Fixed" fields are the default for the OS.

The default for these fields for XPe & XP Pro is: Short & Variable which is the same as having the following bits additional bits set: 0x24.

OR'ing this value in with 0x02 gives you 0x26, which you will find in the table in the article.

Reference: Comment to "Master Your Quantum" (MSDN Blogs)

The table explaining the quantum settings from the same article:

Win32PrioritySeparation   Foreground   Background
0x28, 0x29, 0x2A                  18           18
0x18, 0x19, 0x1A                  36           36
0x24                               6            6
0x25, 0x14                        12            6
0x26                              18            6
0x15                              24            6
0x16                              36            6

Short Summary of OS Quantum

Based on the above information and article quotes, we know that a quantum is not a fixed size, but rather derived from an OS setting in the System Properties. A quantum varies depending on the Win32PrioritySeparation setting in the registry which normally corresponds to one of the setting in the "System Properties" (either "Background services" or "Applications").

A quantum at OS level is

  • for the "Applications" setting
    • 18 (which is 6 ticks) for foreground applications (93.75 ms)
    • 6 (which is 2 ticks) for background applications (31.25 ms)
  • for the "Background Services" setting
    • 36 (which is 18 ticks) for foreground applications (187.5 ms)
    • 36 (which is 18 ticks) for background applications (187.5 ms)

So now we know that an OS quantum on a Windows Server setup to be optimised for Background Services is …

36 / 3 * 15.625000 ms = 187.5 ms

SQL OS Quantum

This section lists what I have found on SQL OS quantum…

SOS_SCHEDULER_YIELD Wait Type

From Paul Randall's description on the SOS_SCHEDULER_YIELD wait type:

This wait type is when a thread was able to execute for its full thread quantum (4 milliseconds in all versions of SQL Server, unchangeable), and so voluntarily yielded the scheduler, moving to the bottom of the Runnable Queue in its scheduler.

Reference: SOS_SCHEDULER_YIELD (SQLSkills.com Wait Types)

Schedulers in SQL Server DMVs

In an explanation on SQL Server DMVs for the sys.dm_os_schedulers DMV.

[…] Windows uses a preemptive scheduling mechanism and assigns a quantum of CPU time to every thread, when a thread consumes its quantum it is sent to a queue and other threads are granted execution.

In opposition, SQL Server uses a cooperative scheduling mechanism when threads can voluntary yield its quantum of time (you can see this behavior when you have a SOS_SCHEDULER_YIELD wait type). This allows SQL Server to optimize CPU utilization, because when a thread is signaled for execution but is not ready to run it can yield its quantum of time in favor of other threads.

Reference: Understanding SQL Server Schedulers, Workers and Tasks (MSSQLTips.com)

Detect SQL Server CPU Pressure

This is a very small section of an article regarding CPU pressure in SQL Server.

Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.

Reference: Detect SQL Server CPU Pressure (MSSQLTips.com)

sys.dm_os_schedulers (Microsoft Docs)

I guess the following quote is the most important snippet of information regarding SQL OS quantum that I could find:

quantum_length_us bigint  Identified for informational purposes only. 
                          Not supported. Future compatibility is not guaranteed. 
                          Exposes the scheduler quantum used by SQLOS.

Reference: sys.dm_os_schedulers (Transact-SQL) (Microsoft | Docs)

My Conundrum

The Server OS Quantum regulates how much time the SQL Server Service is granted to execute "tasks". The SQL Server OS Quantum is defined as 4 ms. If I divide the 187.5 ms by 4 ms then I am left with 3.5 ms.

And we haven't even started the discussion of when the Clock Interval is set to something other than the default of 15.625000 ms….

Simple Question

How is the SQL Server Quantum (4 ms) synchronised with the Server OS Quantum (normally: 187.5 ms)?

Simple Question Explained

After 184 ms of OS quantum being used (which corresponds to 46 full SQL quantums) the OS quantum has 3.5 ms of time before it will have to hand over the schedule to a different process. The SQL OS starts a quantum (4 ms) and after 3.5 ms, the OS quantum has decided to stop the current SQL OS thread which still has 0.5 ms before it would yield the schedule. What happens now?

Best Answer

Even though the scheduler isn't preemptive, the SQL Server scheduler still adheres to a concept of a quantum. Rahter than SQL Server tasks be forced to give up the CPU by the operating system, they can request to be put on a wait queue periodically, and if they have exceeded the internally defined 4 millisecond quantum and aren't in the middle of an operation that can't be stopped, they voluntarily relinquish the CPU.

-"Microsoft SQL Server 2012 Internals", Kalen Delaney et. al. pp38

-Chapter 2 "The SQLOS" Jonathan Kehayias

So the notion of a "quantum" inside SQL Server is more of a "guideline" for programming tasks. IE when you write a task, like say, a task that performs a table scan, if you don't hit any page latch, IO latch, or lock waits for a while, you should stop what you're doing and ask to be put back on the runnable queue, in case there are other tasks waiting.

But it's up to the task programmer to implement this, and it might not be exactly 4ms for each kind of task. For instance the table scan task might use a simple heuristic based on the number of pages scanned to implement the yield points.

So

The SQL OS starts a quantum (4 ms) and after 3.5 ms, the OS quantum has decided to stop the current SQL OS thread which still has 0.5 ms before it would yield the schedule. What happens now?

If the SQL Server thread is pre-empted by Windows while a task is running, it will be paused, and when its thread is next scheduled on a CPU it will continue where it left off. Presumably it will continue to consume the balance of its 4ms quantum, as it wouldn't know any difference. But again, the yield behavior is an implementation detail of the task, not a behavior of SQLOS, so different tasks might behave differently here.

Related Question