Waiting statistics in SQL Server (wait Statistics) Introduction

In today's article I would like to discuss in detail the statistics waiting in SQL Server (wait Statistics), and how they can help you immediately why your SQL Server is currently slow. When it comes to performance tuning, statistics waiting is the most important concept for me in SQL Server.

Why do queries wait

Every time you execute 1 queries in SQL Server, the query always needs to wait. What the? Does the query always have to wait? Yes, you did not read it wrong: But when you execute 1 queries, the query always needs to wait. Why queries need to wait because SQL Server is tracked by so-called wait statistics (wait Statistics) . Before I go into the details of wait Statistics, I want to talk about the reasons why queries in SQL Server always need to wait. In SQL Server, there are 2 reasons for a query to occur:

• Resource Waits
• Cooperative scheduling (cooperative scheduling) Waits

Let's look at these 2 types of waiting in detail. Resource waits occur when you wait for an external resource (Resource wait). Here I would like to you some examples. Each time a query requests 1 pages from the cache pool, if the page is not cached, the buffer manager needs to make asynchronous I/O operations to your storage, reading the page from your physical storage to the cache pool. and accessing physical storage can be very slow. For this reason, SQL Server takes away the CPU cycles of your query, and the query waits until the asynchronous I/O operation is complete (other queries from colleagues can use CPU resources more efficiently). Finally your query continues with its execution.

The same thing happens when you need to acquire a lock-when you want to read or modify data. When someone else has acquired an incompatible lock, your query needs to wait until the lock is available. While SQL Server will take away your CPU cycles again, the query needs to wait until the incompatible locks of other queries are released, so the query itself can get the requested lock.

In addition to resource waits, SQL Server queries are also queued for cooperative scheduling (cooperative scheduling) implemented internally by Sqlos (SQL Server operating system (OS)). SQL Server bypasses the Windows system's preemptive schedule (cooperative scheduling), dispatching its thread itself. Because such a design SQL Server will be easier to scale and provide you with better throughput. When 1 queries are actively running on the CPU, SQL Server itself can decide that when 1 queries are removed from the CPU, SQL Server can also decide, so that another 1 queries can actively continue its query execution on that CPU. For this reason, once the query overflows the so-called volume (Quantum), SQL Server takes your query off the CPU.

The amount of time slice that the query can be actively spent on the CPU. In SQL Server this time slice is 4 milliseconds long. This means that once the query completes its work beyond 4 milliseconds, SQL Server will take your query off the CPU. So queries in SQL Server always have to wait. If there is no resource waiting, the overflow will be kicked in and the query will fall asleep on the CPU (going off). Your query will always wait!

Query life cycle

We know that queries in SQL Server always need to wait. Let's take a closer look at it. When you execute 1 queries, the query goes into 3 different states, as shown in:

Let's go over these 3 states in detail. As long as your query is actively running on your CPU, this query is in the RUNNING state. The RUNNING status means that your query is currently doing some work. Entering this state is always your primary goal. When SQL Server takes your query off the CPU, the query is then moved into the SUSPENDED state. The query waits as long as the SUSPENDED state is needed until the requested resource is available (recall the page read from your physical store, or an incompatible lock that cannot be obtained immediately).

when the requested resource is available, SQL Server then moves your query into the runnable state. The runnable state means that your query is ready to proceed, but it needs something else: the CPU running on it. When there is no CPU available now (because other queries are currently in the running state), the query needs to take some time in the runnable state. Finally, when the CPU becomes available, the query moves into the running state, and then all Samsara continues. A simple query can run over hundreds of, or even thousands of, query lifecycles during execution.

Analysis Wait statistics (wait Statistics)

All these state transactions are tracked by SQL Server and are sent to us by waiting for statistical feedback. SQL Server uses the DMV sys.dm_os_wait_stats to disclose these wait statistics (wait Statistics). Each record returned from this DMV is a 1 wait reason in SQL Server. In SQL Server 2014 you have a total of 771 different reasons why the query waits. What the? 771 different reasons? Are you kidding me? That's a lot of! That's right! But generally you just have to deal with some specific wait reasons, because each of us deals with the same performance problems in SQL Server:

• Slow Storage Subsystem
• Bad index Design
• Lock/block Issues
• Inefficient parallel execution plan
• CPU pressure
• ......

When there is a slow SQL Server in front of me, the first step is to look at the details in the wait statistics, because they tell me why SQL Server queries are waiting. But waiting statistics in SQL Server is only a symptom, not the root of the problem itself! Perhaps SQL Server is waiting for statistics to tell you that there have been some blocking scenarios in the past. But maybe you have a bad index design, missing a very important nonclustered index causing the blocking situation? With additional nonclustered indexes you provide SQL Server with additional data access paths, is it easy to resolve your blocking situation? This is just 1 examples of symptoms that are not the root cause (many of them).

Summary

In this article I give you an overview of why queries wait in SQL Server, and how these waits are tracked by waiting for statistics. The most important thing in waiting for statistics is that SQL Server only tells you the symptoms, not the source of the problem. As a troubleshooting person, your job is to read and understand statistics waiting, and finally dig out more information from your SQL Server to find out the underlying source of the problem.

Thanks for your attention!

Waiting statistics in SQL Server (wait Statistics) Introduction