Introduction to wait stats part 2- SOS_SCHEDULER_YIELD

 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 ?)

https://sqljunkieshare.com/2011/12/15/introduction-to-wait-stats-performance-monitoring-and-tuning/

https://sqljunkieshare.com/2012/01/04/problem-solution-approach-for-parallel-query-processing-cxpacketinsufficient-memorydata-skewsstatistics/

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

https://sqljunkieshare.com/2012/01/06/preemptive-vs-non-preemptive-and-multitasking-vs-multithreading/

I’ll try to highlight the main points in the above post

Multitasking:

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

PREEMPTIVE_OS_GENERICOPS
PREEMPTIVE_OS_AUTHENTICATIONOPS
PREEMPTIVE_OS_ACCEPTSECURITYCONTEXT
PREEMPTIVE_OS_ACQUIRECREDENTIALSHANDLE
PREEMPTIVE_OS_COMPLETEAUTHTOKEN
PREEMPTIVE_OS_DECRYPTMESSAGE
PREEMPTIVE_OS_DELETESECURITYCONTEXT
PREEMPTIVE_OS_ENCRYPTMESSAGE
PREEMPTIVE_OS_FREECREDENTIALSHANDLE
PREEMPTIVE_OS_INITIALIZESECURITYCONTEXT
PREEMPTIVE_OS_LOGONUSER
PREEMPTIVE_OS_QUERYSECURITYCONTEXTTOKEN
PREEMPTIVE_OS_VERIFYSIGNATURE
PREEMPTIVE_OS_AUTHORIZATIONOPS
PREEMPTIVE_OS_AUTHZGETINFORMATIONFROMCONTEXT
PREEMPTIVE_OS_AUTHZINITIALIZECONTEXTFROMSID
PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER
PREEMPTIVE_OS_LOOKUPACCOUNTSID
PREEMPTIVE_OS_REVERTTOSELF
PREEMPTIVE_OS_SETNAMEDSECURITYINFO
PREEMPTIVE_OS_CLUSTEROPS
PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL
PREEMPTIVE_OS_COMOPS
PREEMPTIVE_COM_COCREATEINSTANCE
PREEMPTIVE_COM_COGETCLASSOBJECT
PREEMPTIVE_COM_CREATEACCESSOR
PREEMPTIVE_COM_DELETEROWS
PREEMPTIVE_COM_GETCOMMANDTEXT
PREEMPTIVE_COM_GETDATA
PREEMPTIVE_COM_GETNEXTROWS
PREEMPTIVE_COM_GETRESULT
PREEMPTIVE_COM_GETROWSBYBOOKMARK
PREEMPTIVE_COM_LBFLUSH
PREEMPTIVE_COM_LBLOCKREGION
PREEMPTIVE_COM_LBREADAT
PREEMPTIVE_COM_LBSETSIZE
PREEMPTIVE_COM_LBSTAT
PREEMPTIVE_COM_LBUNLOCKREGION
PREEMPTIVE_COM_LBWRITEAT
PREEMPTIVE_COM_QUERYINTERFACE
PREEMPTIVE_COM_RELEASE
PREEMPTIVE_COM_RELEASEACCESSOR
PREEMPTIVE_COM_RELEASEROWS
PREEMPTIVE_COM_RELEASESESSION
PREEMPTIVE_COM_RESTARTPOSITION
PREEMPTIVE_COM_SEQSTRMREAD
PREEMPTIVE_COM_SEQSTRMREADANDWRITE
PREEMPTIVE_COM_SETDATAFAILURE
PREEMPTIVE_COM_SETPARAMETERINFO
PREEMPTIVE_COM_SETPARAMETERPROPERTIES
PREEMPTIVE_COM_STRMLOCKREGION
PREEMPTIVE_COM_STRMSEEKANDREAD
PREEMPTIVE_COM_STRMSEEKANDWRITE
PREEMPTIVE_COM_STRMSETSIZE
PREEMPTIVE_COM_STRMSTAT
PREEMPTIVE_COM_STRMUNLOCKREGION
PREEMPTIVE_OS_CRYPTOPS
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT
PREEMPTIVE_OS_CRYPTIMPORTKEY
PREEMPTIVE_OS_DEVICEOPS
PREEMPTIVE_OS_RSFXDEVICEOPS
PREEMPTIVE_OS_DIRSVC_NETWORKOPS
PREEMPTIVE_OS_DSGETDCNAME
PREEMPTIVE_OS_NETGROUPGETUSERS
PREEMPTIVE_OS_NETLOCALGROUPGETMEMBERS
PREEMPTIVE_OS_NETUSERGETGROUPS
PREEMPTIVE_OS_NETUSERGETLOCALGROUPS
PREEMPTIVE_OS_NETUSERMODALSGET
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE
PREEMPTIVE_OS_DOMAINSERVICESOPS
PREEMPTIVE_OS_DTCOPS
PREEMPTIVE_DTC_ABORT
PREEMPTIVE_DTC_ABORTREQUESTDONE
PREEMPTIVE_DTC_BEGINTRANSACTION
PREEMPTIVE_DTC_COMMITREQUESTDONE
PREEMPTIVE_DTC_ENLIST
PREEMPTIVE_DTC_PREPAREREQUESTDONE
PREEMPTIVE_OS_FILEOPS
PREEMPTIVE_OS_CLOSEHANDLE
PREEMPTIVE_OS_COPYFILE
PREEMPTIVE_OS_CREATEDIRECTORY
PREEMPTIVE_OS_CREATEFILE
PREEMPTIVE_OS_DELETEFILE
PREEMPTIVE_OS_DEVICEIOCONTROL
PREEMPTIVE_OS_FINDFILE
PREEMPTIVE_FILESIZEGET
PREEMPTIVE_OS_FLUSHFILEBUFFERS
PREEMPTIVE_OS_GETCOMPRESSEDFILESIZE
PREEMPTIVE_OS_GETDISKFREESPACE
PREEMPTIVE_OS_GETFILEATTRIBUTES
PREEMPTIVE_OS_GETFILESIZE
PREEMPTIVE_OS_GETLONGPATHNAME
PREEMPTIVE_OS_GETVOLUMEPATHNAME
PREEMPTIVE_OS_GETVOLUMENAMEFORVOLUMEMOUNTPOINT
PREEMPTIVE_OS_MOVEFILE
PREEMPTIVE_OS_OPENDIRECTORY
PREEMPTIVE_OS_REMOVEDIRECTORY
PREEMPTIVE_OS_SETENDOFFILE
PREEMPTIVE_OS_SETFILEPOINTER
PREEMPTIVE_OS_SETFILEVALIDDATA
PREEMPTIVE_OS_WRITEFILE
PREEMPTIVE_OS_WRITEFILEGATHER
PREEMPTIVE_OS_LIBRARYOPS
PREEMPTIVE_OS_FREELIBRARY
PREEMPTIVE_OS_GETPROCADDRESS
PREEMPTIVE_OS_LOADLIBRARY
PREEMPTIVE_OS_MESSAGEQUEUEOPS
PREEMPTIVE_ODBCOPS
PREEMPTIVE_OLEDBOPS
PREEMPTIVE_OLEDB_ABORTTRAN
PREEMPTIVE_OLEDB_ABORTORCOMMITTRAN
PREEMPTIVE_OLEDB_GETDATASOURCE
PREEMPTIVE_OLEDB_GETLITERALINFO
PREEMPTIVE_OLEDB_GETPROPERTIES
PREEMPTIVE_OLEDB_GETPROPERTYINFO
PREEMPTIVE_OLEDB_GETSCHEMALOCK
PREEMPTIVE_OLEDB_JOINTRANSACTION
PREEMPTIVE_OLEDB_RELEASE
PREEMPTIVE_OLEDB_SETPROPERTIES
PREEMPTIVE_OS_PIPEOPS
PREEMPTIVE_OS_DISCONNECTNAMEDPIPE
PREEMPTIVE_OS_PROCESSOPS
PREEMPTIVE_OS_SECURITYOPS
PREEMPTIVE_OS_SERVICEOPS
PREEMPTIVE_OS_SQLCLROPS
PREEMPTIVE_OS_WINSOCKOPS
PREEMPTIVE_OS_GETADDRINFO
PREEMPTIVE_OS_WSASETLASTERROR
PREEMPTIVE_OS_FORMATMESSAGE
PREEMPTIVE_OS_REPORTEVENT
PREEMPTIVE_OS_BACKUPREAD
PREEMPTIVE_OS_WAITFORSINGLEOBJECT
PREEMPTIVE_OS_QUERYREGISTRY
PREEMPTIVE_CLOSEBACKUPMEDIA
PREEMPTIVE_CLOSEBACKUPTAPE
PREEMPTIVE_CLOSEBACKUPVDIDEVICE
PREEMPTIVE_OS_VSSOPS
PREEMPTIVE_VSS_CREATESNAPSHOT
PREEMPTIVE_VSS_CREATEVOLUMESNAPSHOT
PREEMPTIVE_DFSADDLINK
PREEMPTIVE_DFSLINKEXISTCHECK
PREEMPTIVE_DFSLINKHEALTHCHECK
PREEMPTIVE_DFSREMOVELINK
PREEMPTIVE_DFSREMOVEROOT
PREEMPTIVE_DFSROOTFOLDERCHECK
PREEMPTIVE_DFSROOTINIT
PREEMPTIVE_DFSROOTSHARECHECK
PREEMPTIVE_OLE_UNINIT
PREEMPTIVE_FSAOLEDB_ABORTTRANSACTION
PREEMPTIVE_FSAOLEDB_COMMITTRANSACTION
PREEMPTIVE_FSAOLEDB_STARTTRANSACTION
PREEMPTIVE_FSRECOVER_UNCONDITIONALUNDO
PREEMPTIVE_SERVER_STARTUP
PREEMPTIVE_SHAREDMEM_GETDATA
PREEMPTIVE_CONSOLEWRITE
PREEMPTIVE_OS_SQMLAUNCH
PREEMPTIVE_TESTING
PREEMPTIVE_SOSHOST
PREEMPTIVE_SOSTESTING
PREEMPTIVE_XETESTING
PREEMPTIVE_SB_STOPENDPOINT
PREEMPTIVE_STARTRM
PREEMPTIVE_GETRMINFO
PREEMPTIVE_SETRMINFO
PREEMPTIVE_ROLLFORWARDREDO
PREEMPTIVE_ROLLFORWARDUNDO
PREEMPTIVE_RESIZELOG
PREEMPTIVE_REENLIST
PREEMPTIVE_TRANSIMPORT
PREEMPTIVE_UNMARSHALPROPAGATIONTOKEN
PREEMPTIVE_CREATEPARAM
PREEMPTIVE_STREAMFCB_RECOVER
PREEMPTIVE_STREAMFCB_CHECKPOINT
PREEMPTIVE_XE_CALLBACKEXECUTE
PREEMPTIVE_XE_DISPATCHER
PREEMPTIVE_XE_ENGINEINIT
PREEMPTIVE_XE_GETTARGETSTATE
PREEMPTIVE_XE_SESSIONCOMMIT
PREEMPTIVE_XE_TARGETFINALIZE
PREEMPTIVE_XE_TARGETINIT
PREEMPTIVE_XE_TIMERRUN
PREEMPTIVE_SNIOPEN
PREEMPTIVE_DEBUG
PREEMPTIVE_MSS_RELEASE
PREEMPTIVE_LOCKMONITOR
PREEMPTIVE_STRESSDRIVER
PREEMPTIVE_ABR
PREEMPTIVE_AUDIT_ACCESS_EVENTLOG
PREEMPTIVE_AUDIT_ACCESS_SECLOG

for example

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
  • Event
  • Memory resource notification
  • Mutex
  • Process
  • Semaphore
  • Thread
  • 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

http://sqlserverpedia.com/wiki/Wait_Types#Explanations_of_SQL_Server_Wait_Types

http://msdn.microsoft.com/en-us/library/ms179984.aspx

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

,st.dbid,st.objectid,qs.total_worker_time,qs.last_worker_time,qp.query_plan,qs.execution_count

FROM sys.dm_exec_query_stats qs

CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle) st

CROSSAPPLYsys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_worker_time  DESC

SELECT scheduler_id
,cpu_id
,status
,runnable_tasks_count
,active_workers_count
,current_tasks_count
,load_factor
,yield_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

@msdn Slava Oks blog

sys.dm_os_schedulers

  1. 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:

select

AVG (runnable_tasks_count)

from

sys.dm_os_schedulers

where

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.

  1. Q. What is affinity of my schedulers to CPUs?

select

      scheduler_id,

      CAST (cpu_id as varbinary) AS scheduler_affinity_mask

from

sys.dm_os_schedulers

  1. Does my machine have either hard or soft NUMA configuration enabled?

select

      CASE count( DISTINCT parent_node_id)

      WHEN 1 THEN ‘NUMA disabled’

      ELSE ‘NUMA enabled’

      END

from

      sys.dm_os_schedulers

where parent_node_id <> 32

  1. 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)

select

AVG (work_queue_count)

from

sys.dm_os_schedulers

where

status = ‘VISIBLE ONLINE’

  1. Q: Is my system I/O bound?

You can answer this question by monitoring length of I/O queues.

select

      pending_disk_io_count

from

      sys.dm_os_schedulers

 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s