Why you should not WAIT to fix Query WAITS

If you are a SQL Server DBA or a Database enthusiast I am sure you have heard about Wait Statistics.Wait Statistics are the most important concept in SQL Server when it comes to performance troubleshooting.

You may wondering why any query has to wait? But you may be surprised to know every query has to wait! Yes, that is true when you execute a query, the query always has to wait.There are two reasons why a query has to wait

• Resource Waits

A resource wait happens when the query is waiting for a resource such as I/O or may be on some table data which is currently locked by some other process.

• Cooperative Scheduling Waits

SQL Server bypasses the Scheduling of Windows OS to scaling the thread management efficiency. To do this SQL Server manages CPU resources by managing the CPU time by splitting it between queries. SQL Server has a time slice of 4 milliseconds, by doing this SQL Server takes a query off the CPU when it exceeds this value and let another query the ability to use CPU. This is the main reason we said each query has to wait even there are no resource waits.

In a query life cycle of SQL Server it can go in to three states. Which are;

• Running
• Runnable
• Suspended

When a query is actually doing something it is in Running state, if the query in a Resource wait then it is in Suspended state. A query in Runnable state means, it has all the resources it needs to execute but waiting for a CPU thread released from SQL Server.

SQL Server reports all the details about above mentioned query waits through Wait Statistics. SQL Server exposes the Wait Statistics through the Dynamic Management View sys.dm_os_wait_stats. The data returned from this DMV is about all the wait reasons in SQL Server.

Now the interesting part is in SQL Server 2014 we are getting total of 771 different reasons why a query can wait. Yes that is a lot but there are very specific wait types we normally worry about.

There is a very good survey done on what are the most worrying wait types in SQL Server here

However purpose of this article is not to go deep in to wait types. But what is more important is understanding what wait statistics are and when to use them.

When its come to query optimization the first step we can take is have a good look at the wait statistics, because they can tell you why queries in SQL Server were waiting and if there is a problem or not, if there is a problem then you can dig deep and find out whether it is a missing index or blocking issue or something else.

In conclusion wait statistics will only show you the symptoms not the root cause. But when you see the symptoms you must take actions to cure the root cause. This is why you should not wait to fix Query waits. We hope now you have clear idea about Query waits and what wait statistics are. We will talk more about trouble shooting with wait statistics in a future article.