Symptoms : The average wait time for the SQL session is high.
Impact: Medium
This metric represents the average wait time in seconds that SQL reports that is in a wait state. It is an indication of degradation in server performance.
Expected behavior :
There is no standard metric for wait time . You should set your own thresholds after reviewing past behavior.
Threshold settings:
Metrics are set in milliseconds over a specified time span. Default settings are:
| CRITICAL : | 5ms | |
| MEDIUM : | 3ms | |
| LOW : | 1ms | |
| Timespan : | more than 5 minutes |
Possible causes
Poor coding Priority : Medium
Best practices for coding should be strictly enforced, to ensure that all queries optimally consume and then release resources.
Recommended action :
Using AimBetter monitor, identify any queries that have remained open for an inordinate length of time. Recover the query plan, and see whether any improvements are available.
Index missing or corrupted Priority: Medium
Missing or corrupted indexes can lead to queries working inefficiently and consuming resources.
Recommended action :
See our recommendations regarding memory optimization here.
Background
CPU pressure is indicated by increasing signal-wait times and long runnable queues, not by the count of SOS_SCHEDULER_YIELD waits
The most common cause of SOS_SCHEDULER_YIELD waits is queries doing scans of pages that are in memory and aren’t changing, allowing the scanning thread to run until it exhausts its thread quantum. This could be because a query plan is erroneously doing a table scan, or it could be a normal workload. Just like CXPACKET waits, it’s not possible to draw a conclusion that SOS_SCHEDULER_YIELD waits are bad.
Interpreting SOS_SCHEDULER_YIELD waits involves identifying the query that’s producing the SOS_SCHEDULER_YIELD waits and making sure the query plan looks correct (e.g. looking for missing nonclustered index causing an in-memory table scan, missing index, etc.).
When the thread quantum expires, the thread must yield the processor. Other threads on that scheduler have no impact and there is always a context switch when the thread goes to the bottom of the runnable queue, even if it’s the only thread on the scheduler. The thread cannot persist – the context switch forces the registration of a wait type. A non-yielding scheduler message in the error log means that the context switch did not occur (because the thread failed to check whether the quantum had expired).
SOS_SCHEDULER_YIELD waits don’t go onto the waiter list because they always have a zero resource wait component so no resource wait occurs.
Note: If you are running on a VM experiencing delays because of oversubscribed host, this can elevate the number of SOS_SCHEDULER_YIELD waits.
Examples of yields in SQL Server
- while scanning a table
- while performing a sort
- while scanning values of buffers in the buffer pool (to populate the buffer pool free list with free buffers)
- while estimating cardinality while compiling a query plan
- while scanning buffers list for a database
- where the configured maximum number of worker threads is too small for the workload (see THREADPOOL)
- where the server is trying to synchronize multiple threads for a query with a parallel execution plan (see CXPACKET)
And approximately 900 other call stacks from all across SQL Server.
There are several different wait states, summarized below:
Resource waits occur when a worker requests access to a resource that is not available because the resource is being used by some other process or is not yet available. Examples of resource waits are locks, latches, network, and disk I/O waits.
Queue waits
Queue waits occur when a worker is idle, waiting for work to be assigned. Queue waits are most typically seen with tasks waiting for work requests to be placed into a work queue. Queue waits may also periodically become active even if no new packets have been put on the queue.
External waits
External waits occur when a SQL Server worker is waiting for an external event such as an extended stored procedure call or a linked server query to finish. External waits do not always imply that the worker is idle, because the worker may actively be running some external code.
A SQL Server worker thread is not considered to be waiting if any of the following is true:
- A resource becomes available.
- A queue is not empty.
- An external process finishes.
Although the thread is no longer waiting, the thread does not have to start running immediately. This is because such a thread is first put on the queue of runnable workers and must wait for a quantum to run on the scheduler.
Conclusion
Specific types of wait times during query execution can indicate bottlenecks or stall points within the query. Similarly, high wait times or wait counts server-wide can indicate bottlenecks or hot spots in interaction query interactions within the server instance. For example, lock waits indicate data contention by queries; page IO latch waits indicate slow IO response times; page latch update waits indicate incorrect file layout.
