The first post about waits on SQL Server was regarding what SQL Server Wait means, a brief explanation and the concept might be difficult to catch at first. When the task needs to wait for a resource, it is placed on a list until the resource is available.
SQL Server keeps detailed internal records of how long the tasks spend wainting and the types of resources they are waiting. Wait statistics information can be useful resource for troubleshooting performance problems. I like to start with wait stats information to understand the environment I’m working.
To see the Wait stats details, you can query the following system views:
sys.dm_os_waiting_tasks -> Returns information about the wait queue
of tasks that are waiting on some resource
sys_dm_os_wait_stats -> Returns information about all the waits
encountered by threads that executed.
You can use this aggregated view to diagnose performance issues with
SQL Server and also with specific queries and batches.
I have scripts on Git I gatter on internet to query wait stats in a easy way to read.
Let’s talk about why we have to wait and how to understand the wait types.
Paul Randal in his post Wait statistics, or please tell me where it hurts said:
A thread is using the CPU (called RUNNING) until it needs to wait for a resource. It then moves to an unordered list of threads that are SUSPENDED. In the meantime, the next thread on the FIFO (first-in-first-out) queue of threads waiting for the CPU (called being RUNNABLE) is given the CPU and becomes RUNNING. If a thread on the SUSPENDED list is notified that it’s resource is available, it becomes RUNNABLE and is put on the bottom of the RUNNABLE queue. Threads continue this clockwise movement from RUNNING to SUSPENDED to RUNNABLE to RUNNING again until the task is completed
That’s explain a lot, because the SQL Server threads doesn’t run all in the same time. A good example is when our query is doing physical reads. The IO subsystem is the slowest part of our resources and probably will take some time if the query is reading gigabytes of data.
After the CPU request the data from the disk, the disk will run for it, but before send the data back. All data need to go to memory first and that may don’t have the necessary space. The thread is going to wait until some resources been released first. There are many scenarios, for example, how many threads are running this query? How long will take to the application to show that data?
So, every time a thread needs to wait for a resource it will increase a wait time type, such as PAGEIOLATCH_XX , PAGELATCH_XX, ASYNC_NETWORK_IO, CXPACKET, RESOURCE_SEMAPHORE. I will talk more about waits in the next posts.