In this post you will learn
What is Multitasking , Preemptive and Non preemptive Scheduling
What is SOS_SCHEDULER_YELD wait
How to know whether you CPU is under pressure (Why ?)
In the above posts we learned how to understand the waits and how to monitor them , specially CXPACKET which comes in to the picture when achieving parallelism
I have been researching about the specific wait type called SOS_SCHEDULER_YEILD I got some interesting things to share and Views from famous MVP and SQL gurus
Before going in to details we need to understand the concept of Multitasking please use below link to understand in detail about multitasking
I’ll try to highlight the main points in the above post
What is Multitasking ?
On a single-processor multitasking system, multiple processes don’t actually run at the same time since there’s only one processor. Instead, the processor switches among the processes that are active at any given time. Because computers are so fast compared with people, however, it appears to the user as though the computer is executing all of the tasks at once. Multitasking also allows the computer to make good use of the time it would otherwise spend waiting for I/O devices and user input–that time can be used for some other task that doesn’t need I/O at the moment.
So CPU switches its time spent on the Multiples processes so fast so that for the end-user it looks like the processor is doing multitasking
In order to perfectly switch between processes or tasks there should be a Scheduler to manage the switches the way scheduler stores the contents of the processes or threads into a registers or L1 or L2 or Memory is called Context Switching
There are majorly two types scheduling algorithms Preemptive and Non Preemptive Scheduling
Preemptive :: Means Non – cooperative – If a CPU time(Quantum)allocated to a particular thread is exceeded or if a high priority task needs to be executed the OS will interrupt the previous processes and executes the high priority task by maintining a queue of suspended or runnable tasks
Non Preemptive:: Means Co operative mode – .i.e. here OS will not interrupt the currently running process in stead it waits untill the currently running process voluntary yields
Now we will learn how these scheduling effects the sql server performance
Sql server OS (level of layer between OS and user) have the Non – preemptive mode as we learned the active thread will voluntarily yield for the next runnable thread , the SQL OS is designed such a way that active thread will not starve other runnable threads SQL decides when should the active thread should yield to other during this process SQl OS records this value as last wait type in the DMV’s ‘SOS_SCHEDULAR_YEILD’
Most operating systems uses preemptive mode scheduling but all scheduling done by the SQL OS is in Non preemptive mode so , when thread or processes can change Non preemptive mode to preemptive mode Eg: when using XP_CMD sehll and running batch commands, accessing files and directory , Extended stored procedures , linked servers… when these occurs it record in the DMV SYS.DM_OS_WAIT_STATS
all the preemptive wait stats are recorded in this format Preemptive_>category<_>function<
in the above case PREEMPTIVE_OS_%
There are 190 preemptive waits that SQL OS records in the DMV
in this case PREEMPTIVE_OS_WAITFORSINGLEOBJECT
Waits until the specified object is in the signaled state or the time-out interval elapses.
The WaitForSingleObject function checks the current state of the specified object. If the object’s state is nonsignaled, the calling thread enters the wait state until the object is signaled or the time-out interval elapses.
The function modifies the state of some types of synchronization objects. Modification occurs only for the object whose signaled state caused the function to return. For example, the count of a semaphore object is decreased by one.
The WaitForSingleObject function can wait for the following objects:
- Change notification
- Console input
- Memory resource notification
- Waitable timer
In our case it may any thing OS may preempt the thread for any of the above objects
these below are documented in MSDN
|PREEMPTIVE_ABR||Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.|
|PREEMPTIVE_AUDIT_ACCESS_EVENTLOG||Occurs when the SQL Server Operating System (SQLOS) scheduler switches to preemptive mode to write an audit event to the Windows event log.|
|PREEMPTIVE_AUDIT_ACCESS_SECLOG||Occurs when the SQLOS scheduler switches to preemptive mode to write an audit event to the Windows Security log.|
|PREEMPTIVE_CLOSEBACKUPMEDIA||Occurs when the SQLOS scheduler switches to preemptive mode to close backup media.|
|PREEMPTIVE_CLOSEBACKUPTAPE||Occurs when the SQLOS scheduler switches to preemptive mode to close a tape backup device.|
|PREEMPTIVE_CLOSEBACKUPVDIDEVICE||Occurs when the SQLOS scheduler switches to preemptive mode to close a virtual backup device.|
|PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL||Occurs when the SQLOS scheduler switches to preemptive mode to perform Windows failover cluster operations.|
|PREEMPTIVE_COM_COCREATEINSTANCE||Occurs when the SQLOS scheduler switches to preemptive mode to create a COM object.|
please use this below links to better understand waits
once now you have an idea what switching is happening more in the SQl server next step is to find out whether CPU is under pressure or not
Best DMV for this information is SYS.DM_OS_SCHEDULERS
schedulersare basically they schedule tasks or they manage tasks between processors or processor, here in this DMV we will have a scheduler for each processor all the schedulers re not available for user query processing, some are used by the Database engine or may be disabled for SQL server use using affinity masks and each scheduler have some number of active workers which take care of utilizing quantum for processing
So each scheduler can have no tasks waiting for the scheduler (cpu) runnable_task_count
and each worker has queue of work waiting for the worker thread to pick worke_queue_count
runnable_task_count should not be in double digits and avg should be less than one
now you found out that there is cpu pressure now we have to investigate, why?
best way is to find out the execution plans of the top 20 max_worker_time in SYS.dm_EXEC_QUERY_STATS
max worker time is the amount of time spent executing and also the execution count so that we will have a clear idea of queries using maximum cpu , which resulting in Preemptive_OS _%% wait and non preemptive SOS_SCHEDULER_YIELD
SELECT TOP 10 st.text
FROM sys.dm_exec_query_stats qs
ORDER BY qs.total_worker_time DESC
WHERE scheduler_id < 255
@msdn Slava Oks blog
- Q. Do I need to by more CPUs?
In order to answer this question you have to find out if your load is really CPU bounded. Your load is really CPU bounded if a number of runnable tasks per each scheduler always greater than 1 and all of your queries have correct plan. The latter statement is very important, your load can be CPU bounded due to the fact that somehow optimizer generated bad plan – it can happen if your statistics out of date or you tried to perform handcrafted optimization. In this case you don’t want to run toCircuitCityto buy more CPUs right a way – you want to fix the plan. Here is the query to find out average length of a runable queue on the system:
status = ‘VISIBLE ONLINE’
Buying more CPUs has also to do with capacity planning. You have to be very careful when performing capacity planning on hardware with HT enabled – remember you don’t have extra physical CPUs. Keep in mind that if your load runs at 60% CPU utilization – it doesn’t mean that you have 40% of extra CPU capacity. You will be very surprise how fast CPU load will jump from 60% to 80% and then even faster to 100% once you apply more and more load.
- Q. What is affinity of my schedulers to CPUs?
CAST (cpu_id as varbinary) AS scheduler_affinity_mask
- Does my machine have either hard or soft NUMA configuration enabled?
CASE count( DISTINCT parent_node_id)
WHEN 1 THEN ‘NUMA disabled’
ELSE ‘NUMA enabled’
where parent_node_id <> 32
- Q. Should I configure SQL Server to use more threads – sp_configure ‘max server threads’?
You can answer this question by looking at the work queue length for each scheduler. If on average such value is above 1 then you might benefit from adding more threads to the system but only if
A. Your load currently is not CPU bounded (See info above on how to find out if your load is CPU bound)
B. Your load currently doesn’t experience any other heavy waits (If you add more threads in this case they will just end up waiting as everyone else)
status = ‘VISIBLE ONLINE’
- Q: Is my system I/O bound?
You can answer this question by monitoring length of I/O queues.
If over time they keep on growing or you are seeing periodic jumps or numbers stay relatively high most likely your system is I/O bound. In order to identify the cause you will have to dive further.