Hi SQL Experts,
Can anyone tell me what are the meanings of below terms in layman terms. Later I can go through documentation.
Wanted to study SQL OS and I am little confused with these terms.
1. Pre-emptive scheduling
2. non-Pre-emptive scheduling
3. Yielding
4. Non-yielding
Thanks,
Sam
September 9, 2019 at 8:02 pm
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
This is an older article that explains troubleshooting scheduler and yielding: https://blogs.msdn.microsoft.com/psssql/2018/04/05/troubleshooting-sql-server-scheduling-and-yielding/
You can start here to review the DMV's associated with SQL Server Operating System: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sql-server-operating-system-related-dynamic-management-views-transact-sql?view=sql-server-2017
Here is a general outline: https://www.mssqltips.com/sqlservertip/4403/understanding-sql-server-schedulers-workers-and-tasks/
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 9, 2019 at 9:05 pm
Those are all part of SQL Server and related to how the scheduling of tasks across the CPUs is managed. SQL Server is multithreaded with multiple task assigned to schedulers. For different tasks, there are different points in which a thread may yield or pause what they are doing for a bit, which allows another task on that scheduler to run. But all of it is related to scheduling threads, what tasks actively run on the schedulers. Check that last link that was pasted above to get an idea on how it all fits together.
Sue
September 10, 2019 at 9:53 pm
Scheduling refers to how the operating system handles running threads from multiple separate processes on each CPU core. A CPU Core can only do one thing at a time. If there is more than one process that need time on CPU, then they get in a line and take turns running. 'Pre-emptive scheduling' vs. 'non-preemptive scheduling' refers to how those threads get their turn on CPU. Note: in multi-core systems, what I'm describing occurs separately on each CPU.
Preemptive scheduling (the method used by the Windows OS) sets a hard quantum (time slice) that each thread will get. When your quantum is up, the OS preempts your thread, pulling it off the CPU and putting the next one on. This is better for concurrency at the expense of performance - if you get 3ms on CPU, but you complete your work in 1ms, then 2ms just gets wasted. If you only get 3ms and your thread needs 4ms to complete a specific task, then it will be pulled from CPU before completing the task, with an overhead hit of serializing the state of the thread so it can continue when it gets scheduled again, which includes a deserialization step. Quantum length in a preemptive system can change depending on the number of waiting threads - lots of threads = lower quantum so everything gets a little time on CPU, even if its not enough.
Non-preemptive scheduling (the method used by SQL OS, which runs as a process in Windows or Linux) sets a soft quantum value for threads. When a thread on CPU either finishes its work or crosses the time threshold set by the soft quantum, it is expected to yield, that is, take itself off of the CPU, and if there's more work to do then get back in line. The quantum is not enforced by the OS - threads can go way over the quantum and not give up the CPU (this is rare in SQL Server, and usually means problems). This allows short threads to complete and give back the CPU time they didn't use and allows threads needing an extra millisecond or two to finish and release its resources to do so. The SQL OS determines the order of threads going to CPU, and on what CPU they will run. SQL Server maps a Scheduler object to a CPU core (read up on sys.dm_os_schedulers). The quantum in SQL Server is 4ms (SELECT os_quantum FROM sys.dm_os_sys_info).
Yielding is when a non-preemptive thread on CPU takes itself off the CPU and informs the OS.
Non-yielding is when a non-preemptive thread on CPU stays on CPU longer than its quantum. In SQL Server, a non-yielding scheduler is usually caused by a SQL Server internal call to an external resource that's hung or dragging. For example, bugs in a storage driver that cause I/O calls to hang.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply