sql-server add tag
John K N (imported from SE)
# 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][L1] 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 [][L2] 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"[^1].

[^1]: This setting can be found via "System Settings | Advanced (tab) | Performance (section) | Settings ..." which will open "Performance 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[^2].

[^2]: ***Reference:** [Comment to "Master Your Quantum"][L2] (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[^3]. 

[^3]: ***Reference:** [SOS_SCHEDULER_YIELD][L5] (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**[^4].

[^4]: ***Reference:** [Understanding SQL Server Schedulers, Workers and Tasks][L4] (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**[^5].

[^5]: ***Reference:** [Detect SQL Server CPU Pressure][L6] (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[^6]:

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

[^6]: ***Reference:** [sys.dm_os_schedulers (Transact-SQL)][L7] (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?


  [L1]: https://blogs.msdn.microsoft.com/embedded/2006/02/20/know-thy-tick/
  [L2]: https://blogs.msdn.microsoft.com/embedded/2006/03/04/master-your-quantum/
  [L3]: https://docs.microsoft.com/en-us/windows-hardware/test/wpt/cpu-analysis
  [L4]: https://www.mssqltips.com/sqlservertip/4403/understanding-sql-server-schedulers-workers-and-tasks/
  [L5]: https://www.sqlskills.com/help/waits/sos_scheduler_yield/
  [L6]: https://www.mssqltips.com/sqlservertip/2890/detect-sql-server-cpu-pressure/
  [L7]: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-schedulers-transact-sql?view=sql-server-2017
Top Answer
david browne microsoft (imported from SE)
> 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.  
Answer #2
SE Anon or Wiki user (imported from SE)
>How is the SQL Server Quantum (4 ms) synchronised with the Server OS Quantum (normally: 187.5 ms)?

It's not and SQL Server doesn't use preemptive scheduling. Work items are expected to hit yield points and if they don't, you get things such as `NONYIELDING` schedulers. There is no parity. SQL Server doesn't distribute the time. It makes certain threads attractive to Windows to schedule and Windows schedules them. Quantum is just nomenclature for a piece of time. That's it. SQL Server isn't preemptive, it's the responsibility of whatever is running to yield itself throughout the code. – [Sean Gallardy][1]

When the OS quantum expires the thread is forcefully descheduled. This is transparent to SQL Server. SQLOS has no way of detecting when this happens. There is no Win32 API for that. Scheduling is transparent to user mode threads. The Windows scheduler does not know or care what user mode threads are doing. Windows only sees threads that are runnable and lets them run till the end of their OS quantum or until they block. – [usr][2]

In [How to handle excessive SOS_SCHEDULER_YIELD wait type values in SQL Server][3] by  Nikola Dimitrijevic, the term "quantum" is used to mean essentially "the time a task actually spends assigned to a worker", but that's not the same sense as a Windows quantum, which is a period of time after which the OS will remove a thread from a CPU. They're just different concepts. If the OS forces a thread to end execution because the OS quantum has been reached, a context switch happens. SQL Server's thread is suspended, just like any other program. – [David Browne - Microsoft][4] and [George.Palacios][5].

---

Extracts from the documentation: [Inside the SQL Server 2000 User Mode Scheduler][6] (written for SQL Server 2000, but still relevant):

>### Preemptive vs. Cooperative Tasking
>
>UMS, by contrast, relies on threads to yield voluntarily. UMS takes the approach it does in order to keep from involving the Windows kernel any more than absolutely necessary. In a system where worker threads can be counted on to yield when they should, a cooperative scheduler can actually be more efficient than a preemptive one because the scheduling process can be tailored to the specific needs of the application. As I said earlier, UMS knows the scheduling needs of SQL Server better than the operating system can be expected to.
>
>### How UMS Takes Over Scheduling
>
>If UMS is to handle SQL Server's scheduling needs rather than allowing Windows to do so, UMS must somehow prevent the OS from doing what it does with every other process: schedule threads on and off the system's processor(s) as it sees fit. How do you do that in a preemptive OS? UMS pulls this off through some clever tricks with Windows event objects. Each thread under UMS has an associated event object. For purposes of scheduling, Windows ignores threads it does not consider viable—threads that cannot run because they are in an infinite wait state. Knowing this, UMS puts threads to sleep that it does not want to be scheduled by having them call **WaitForSingleObject** on their corresponding event object and passing **INFINITE** for the timeout value.
>
>In order to prevent Windows from scheduling multiple threads on the same processor and thereby incurring the overhead and expense of context switches, UMS attempts to keep just one thread viable—that is, not in an infinite wait state—per processor.

  [1]: https://dba.stackexchange.com/users/41723/sean-gallardy
  [2]: https://dba.stackexchange.com/users/6819/usr
  [3]: https://www.sqlshack.com/how-to-handle-excessive-sos_scheduler_yield-wait-type-values-in-sql-server/
  [4]: https://dba.stackexchange.com/users/126936/david-browne-microsoft
  [5]: https://dba.stackexchange.com/users/44945/george-palacios
  [6]: https://docs.microsoft.com/en-us/previous-versions/sql/legacy/aa175393(v=sql.80)

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.