Follow

General introduction

To understand what we are talking about, when we talk about waits, then let me illustrate it with an example.

You have prepared for a marathon and you think you can run it in 4 hours. When the start goes, you start running, but at some point you need to stop and have some water.

Each time you stop to drink, you are wasting time on other things than your primary task: to run.

This time, where you stop to wait is monitored, and when you finish your race, then you can see that your time is divided into two, the time you were running and the time you were drinking.

Imagine then, that if the rules are changed, and then when you have been drinking, then there is a queuing system to be allowed to start running again, then you also have to wait for that.

So now, when you are finished, your time is divided into three, the time you were running, the time you were drinking, the time you were waiting to be allowed to wait again.

No doubt that you need to drink and no doubt that there must be a queueing system, but when you drink, it should take as little time as possible, and that also goes for the queuing to be allowed to start again.

That is basically the idea of Wait statistics in Microsoft SQL Server:

  1. We execute a query, and it starts processing.
  2. After some times, it might need to have some data, and therefore we stop and start finding the data (this is were we are drinking water).
  3. After we have found the data, then we move to a queue where we start waiting on being allowed to execute again.

This proces is described in the drawing below:

When you write an SQL statement, then it is put on a processor (i call it a scheduler) and a worker does some work.

At some point we need some ressources to continue execution, and then we are put in the waiting queue until we have the ressources that we need.

Then we are put into the runnable queue, where we awaits that it is our turn to execute again.

If we monitor what we are waiting for, then we might be able to identify problems, because if we can reduce waiting time, then our queries should run faster.

These waiting types have strange words, like (exapmles):

LCK_M_SCH_S
ASYNC_IO_COMPLETION
BACKUPIO
PAGEIOLATCH_SH
BACKUPBUFFER
MSQL_XP
IO_COMPLETION
PAGELATCH_EX

Since wait types are a very important part of Microsoft SQL Server, then we have described the most common ones. the description includes

 

  1. What we think about the wait type
  2. What Microsoft says
  3. What we suggest that you should do about it

PLEASE NOTE!!! Identifying something that we are waiting for for a long time, does not give us the conclusion about what we should do. It only guides us in a direction to do further analysis.

 

 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.