In this article we will talk about sql server wait type wait stats queues and some common wait types.
These are the baseline of the performance tuning. This is not so enough but wait types playing a big role in performance hit on sql server performance. Not every time every request faced performance hit due to wait types but there are lot of times we get performance hit and when we dig into it we get there are some different wait types are culprit which are hitting performance of sql server.
Wait Types: If a request goes to sql server and it converted to a task which means a thread is assigned to it so either your workload is running or waiting, if it is running everyone is happy but if it is waiting sql server records what it is waiting for and that called as wait types.
In Sql server there are so many different wait types. Here we are taking about some common wait types. SQL Server records these wait types and you can extract these information from sql server there are various ways you can capture these information from sql server.
Waiting Queues: The users send a request a sql server and and the request is now waiting and it is waiting for a specific wait type, now sql server will assign that specific wait type for that thread if another request comes in and again it waiting for same wait type, so that threads are waiting in a queues called waiting queues.
Wait Stats: As a particular thread on a particular wait type, sql server will record for how long it is waiting for this is called as wait time. And this information is captured in sql server and it is continuously incremented that called cumulative wait stats.
Wait type in action: In action there are three different states of thread execution:
- Running State: when thread is running in sql server server
- Waiting State: when thread is waiting for the resource for completion their execution.
- Runnable State: when thread is in runnable queue and its waiting for its turn to execute, this work in the phenomena of FIFO (first in first out).
These three different states of thread is cyclic happening in the sql server for every execution.
Below are some common wait types which we are going to discuss:
PAGEIOLATCH: When any thread is waiting for some IO Level bottleneck, physical io then the pageiolatch wait type occur.
CXPACKET: when any piece of code which is running with parallel threads then Parallelism wait type occur.
PAGELATCH: PageLatch is different from PAGEIOLATCH, PageIOLatch like hints IO Bottleneck, Physical IO Bottleneck but Page Latch demonstrate contention in memory with buffer contention on memory latch.
Resource_Semaphore: This wait type is related for memory grant by a thread, how much memory required for execution for specific thread execution.
WRITELOG: When we get any contention with log file. The SQL Server assign WRITELOG wait type for that request.
LCK_*: When any thread is locked by any other thread then LCK_* wait type occur, In LCK_* there are many different wait type that occur on the basis of the locking under the thread.
SOS_SCHEDULER_YIELD: When any thread is waiting for CPU resource for their execution then it comes under SOS_SCHEDULER_YIELD wait type.
With the help of below dmv’s we can extract some useful information which will help to take required action.
sys.dm_os_wait_stats: This dmv gives are all waiting stats in sql server.
sys.dm_os_waiting_tasks: this dynamic management view gives us all waiting task which are waiting under a specific wait type.
sys.dm_exec_query_memory_grants: Its gives us how much memory grants each query has received for its execution and it also tell us it has received or not. How much memory a thread asked for the execution the thread.
DBCC SQLPERF(N’sys.dm_os_wait_stats’, CLEAR); This dbcc statement clear all wait stats.