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 we are querying the disk will run for it, but before send the data back there another step. All data need to go to memory first and imagine a scenario that you don’t have space and first need to open space for that data you need. How many threads are using to run this query? How long will take to the application to show that data?
So, in all this process we can see a couple of waits for example, like PAGEIOLATCH_XX , PAGELATCH_XX, ASYNC_NETWORK_IO, CXPACKET and I will write in detail about of each in the next posts.