If a worker thread doesn’t yield, is it guaranteed to run?
Put another way, can Windows preempt our worker thread and perform a context switch even though it has been “scheduled” by the SQL OS (SOS) and SQL thinks it is running? Of course it can.
SQL Server implements a cooperative scheduling mechanism to make the most efficient use of the CPUs as it can. However, Windows ultimately schedules the thread and uses a general purpose pre-emptive scheduler and does not give special considerations to SQL Server when the dispatcher selects a thread – there all just threads to Windows (though they can have priority). So if a higher priority thread comes in from an interrupt or if we’ve exhausted our quantum at the OS level and another ready thread is waiting from another process to run it’s timeslice, then our worker thread will get preempted, regardless of what SQL wants. SQL controls this behavior to the best of it’s ability amongst it’s own worker threads by making sure no more threads are viable for scheduling (from within SQL) than the number of CPUs available to SQL Server. Also, SQL Server realizes there is no sense in scheduling a thread that is waiting on a database resource – like a transactional lock – something that Windows doesn’t understand. If a thread reaches its quantum at the OS and no other threads are viable to the Windows dispatcher, we can get another quantum and maximize SQL Server’s use of the CPU. In this way, we hope to reduce context switches – pure overhead from an execution standpoint. SQL controls the scheduling of it’s own worker threads by placing them into wait states with APIs like WaitForSingleObject. Then when a thread is ready to run because a lock has been freed, a timer has expired, or a task / request has come in from a user, SQL Server signals it to come out of the wait state and execute. Even as it sits in the RUNNABLE queue, it is actually in one of these wait APIs (though the SQL status will be RUNNABLE). As the “running” thread leaves, part of it’s “good-citizen” cooperative scheduling responsibilities is to “signal” the next worker thread in the RUNNABLE queue so that it can get it’s 15ms of fame.
This is a good reason to limit as much as possible what other software executes on your SQL Server. However, let’s see an example stack taken from my machine where just this scenario happened.
First, let’s look at a “normal” example:
ntoskrnl.exe!KiSwapContext+0x7a ntoskrnl.exe!KeSignalGateBoostPriority+0x1c0 ntdll.dll!ZwWaitForSingleObject+0xa KERNELBASE.dll!WaitForSingleObjectEx+0x79 sqlservr.exe!SOS_Scheduler::SwitchContext+0x26d sqlservr.exe!SOS_Scheduler::SuspendNonPreemptive+0xca sqlservr.exe!SOS_Scheduler::Suspend+0x2d sqlservr.exe!EventInternal<Spinlock<153,1,0> >::Wait+0x1a8 sqlservr.exe!EventInternal<Spinlock<153,1,0> >::WaitAllowPrematureWakeup+0x59 sqlservr.exe!CXPacketList::RemoveHead+0xf0 sqlservr.exe!CXPipe::Pull+0x8b sqlservr.exe!CXTransLocal::AllocateBuffers+0x5b sqlservr.exe!CQScanXProducerNew::AllocateBuffers+0x31 sqlservr.exe!CQScanXProducerNew::GetRowHelper+0x1c2 sqlservr.exe!FnProducerOpen+0x58 sqlservr.exe!FnProducerThread+0x4df sqlservr.exe!SubprocEntrypoint+0x794 sqlservr.exe!SOS_Task::Param::Execute+0x12a sqlservr.exe!SOS_Scheduler::RunTask+0x96 sqlservr.exe!SOS_Scheduler::ProcessTasks+0x128 sqlservr.exe!SchedulerManager::WorkerEntryPoint+0x2d2 sqlservr.exe!SystemThread::RunWorker+0xcc sqlservr.exe!SystemThreadDispatcher::ProcessWorker+0x2db sqlservr.exe!SchedulerManager::ThreadEntryPoint+0x173 MSVCR80.dll!_callthreadstartex+0x17 MSVCR80.dll!_threadstartex+0x84 kernel32.dll!BaseThreadInitThunk+0xd ntdll.dll!RtlUserThreadStart+0x1d
So here we have a thread that is part of a parallel query that goes into a wait. Ever see those CXPACKET waits? Well, you’re looking at one now. So whlie we wait on CXPACKET or any other wait type – we can’t do anything. Let’s “switch context” and have the SQL OS (SOS) take us off the scheduler – let someone else have their time – and we can be signaled when it’s time to go again. As we “switch”, the SOS scheduler puts us in a wait state (waiting on an event) with WaitForSingleObject. At the frame that reads ZwWaitForSingleObject, you are seeing the transition from user mode into kernel mode as Windows takes over. Then at the top of the stack, the Windows dispatcher records our context record and switches us off the CPU and finds which thread is ready to run – which is very likely NOT another SQL Server thread in *this case* – but on a dedicated SQL Server we’d like it to be – and most of the time it should be.
Now, let’s see if SQL can have the rug pulled out from under it… Here’s one:
ntoskrnl.exe!KiSwapContext+0x7a ntoskrnl.exe!KiCommitThreadWait+0x1d2 ntoskrnl.exe!KeWaitForSingleObject+0x19f ntoskrnl.exe!KiSuspendThread+0x54 ntoskrnl.exe!KiDeliverApc+0x201 ntoskrnl.exe!KiApcInterrupt+0xd7 sqlservr.exe!CQScanXProducerNew::GetRowHelper+0x289 sqlservr.exe!FnProducerOpen+0x58 sqlservr.exe!FnProducerThread+0x4df sqlservr.exe!SubprocEntrypoint+0x794 sqlservr.exe!SOS_Task::Param::Execute+0x12a sqlservr.exe!SOS_Scheduler::RunTask+0x96 sqlservr.exe!SOS_Scheduler::ProcessTasks+0x128 sqlservr.exe!SchedulerManager::WorkerEntryPoint+0x2d2 sqlservr.exe!SystemThread::RunWorker+0xcc sqlservr.exe!SystemThreadDispatcher::ProcessWorker+0x2db sqlservr.exe!SchedulerManager::ThreadEntryPoint+0x173 MSVCR80.dll!_callthreadstartex+0x17 MSVCR80.dll!_threadstartex+0x84 kernel32.dll!BaseThreadInitThunk+0xd ntdll.dll!RtlUserThreadStart+0x1d
Notice at the top – Windows has performed the context switch and let someone else run. Once again, Windows will save our execution context in something known as a context record (so it knows where it was and what we were doing when we finally do get back on the CPU) and let another thread execute – very likely a thread from a different process. Also, notice that the last frame from SQL Server (sqlservr!*) is more parallelism code. There is no call to sleep, no call to SwitchContext, nothing from the SQL OS at all. We are just rudely interrupted by the kernel (ntoskrnl.exe) and told – you’re done. Actually, at this point it was processing the APC queue (part of the kernel) and went into it’s own WaitForSingleObject call as a part of the context switch – but not one invoked by SQL Server. Also, kernel stacks work the same as user mode stacks – so other kernel code could have already run and been “popped” off the stack at this point. We’ll get more time in a matter of milliseconds, but the more this happens, the more your SQL code waits, the more the duration goes up – and yet no progress is made.
So you really want to limit how much non-SQL server software you execute on your server, SQL Server can’t control other processes or Window’s scheduling of those processes.
Finally, how did I get this? First, I used Mark Russinovich’s excellent Process Explorer utility to capture both the user and kernel mode stacks in one nice combined stack trace. To easily “create” this mess and find an offending thread, I ran a very busy query (a cross join) and also ran an external utility I wrote that does nothing but burn cpu, it’s literally called “EatCPU”.
- Jay