Information About Waits Stats in SQL Server SOS or SQL Server Scheduler Related Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to SOS related waits or SQL Server Scheduler related waits.

SOS_CALLBACK_REMOVAL
Occurs while performing synchronization on a callback list in order to remove a callback. It is not expected for this counter to change after server initialization is completed.

SOS_DISPATCHER_MUTEX
Occurs during internal synchronization of the dispatcher pool. This includes when the pool is being adjusted.

SOS_LOCALALLOCATORLIST
Occurs during internal synchronization in the SQL Server memory manager.

SOS_MEMORY_USAGE_ADJUSTMENT
Occurs when memory usage is being adjusted among pools.

SOS_OBJECT_STORE_DESTROY_MUTEX
Occurs during internal synchronization in memory pools when destroying objects from the pool.

SOS_PHYS_PAGE_CACHE
Accounts for the time a thread waits to acquire the mutex it must acquire before it allocates physical pages or before it returns those pages to the operating system. Waits on this type only appear if the instance of SQL Server uses AWE memory.

SOS_PROCESS_AFFINITY_MUTEX
Occurs during synchronizing of access to process affinity settings.

SOS_RESERVEDMEMBLOCKLIST
Occurs during internal synchronization in the SQL Server memory manager.

SOS_SCHEDULER_YIELD
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.

SOS_SMALL_PAGE_ALLOC
Occurs during the allocation and freeing of memory that is managed by some memory objects.

SOS_STACKSTORE_INIT_MUTEX
Occurs during synchronization of internal store initialization.

SOS_SYNC_TASK_ENQUEUE_EVENT
Occurs when a task is started in a synchronous manner. Most tasks in SQL Server are started in an asynchronous manner, in which control returns to the starter immediately after the task request has been placed on the work queue.

SOS_VIRTUALMEMORY_LOW
Occurs when a memory allocation waits for a resource manager to free up virtual memory.

SOSHOST_EVENT
Occurs when a hosted component, such as CLR, waits on a SQL Server event synchronization object.

SOSHOST_INTERNAL
Occurs during synchronization of memory manager callbacks used by hosted components, such as CLR.

SOSHOST_MUTEX
Occurs when a hosted component, such as CLR, waits on a SQL Server mutex synchronization object.

SOSHOST_RWLOCK
Occurs when a hosted component, such as CLR, waits on a SQL Server reader-writer synchronization object.

SOSHOST_SEMAPHORE
Occurs when a hosted component, such as CLR, waits on a SQL Server semaphore synchronization object.

SOSHOST_SLEEP
Occurs when a hosted task sleeps while waiting for a generic event to occur. Hosted tasks are used by hosted components such as CLR.

SOSHOST_TRACELOCK
Occurs during synchronization of access to trace streams.

SOSHOST_WAITFORDONE
Occurs when a hosted component, such as CLR, waits for a task to complete.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

Your email address will not be published. Required fields are marked *