22nd/24-week wait and I/O latency statistics

Hello everyone, welcome back to the 22nd week of Performance tuning training . Last week I talked about the baseline in SQL Server, and today we go ahead and talk about waiting and I/O latency statistics in SQL Server. When I perform a SQL Server health check, I always use these 2 dimensions to globally master the health of SQL Server.

Wait statistics (wait Statistics)

Every time you execute a query in SQL Server, the query waits. This looks bleak for the first time, but there is a very good reason to wait in SQL Server. Each time a query waits, SQL Server tracks these waits through so-called wait statistics (wait Statistics) . Before we discuss the wait statistics itself. I would like to introduce why the query will always wait during execution. The concept of waiting is mainly based on 2 principles:

• non-synchronous resource waits ( Asynchronous Resource waiting)
• Cooperative scheduling (cooperative scheduling)

Let's take a look at these 2 details. Each time a query waits for a resource that is currently unavailable-for example, a page that is not cached by the cache pool, or a lock that cannot be obtained because of another incompatible lock-the query goes into the so-called suspend (Suspended) state in SQL Server. The query waits in the pending state until the resource becomes available.

When the resource becomes available, the query enters the so-called executable (Runnable) state, Waits again, knowing that the CPU becomes available. When the CPU is available, the query finally goes into the run (Running) state, and execution to the resource becomes unavailable again. When this occurs, the query enters the pending (Suspended) state again. Shows the query life cycle.

In addition, the query waits for a cooperative dispatch (cooperative scheduling) implemented by SQL Server in Sqlos (SQL Server operating system). SQL Server dispatches its threads by using a specific WIN32 API feature. Cooperative scheduling means that when a query itself exceeds the amount of nearly 4ms (quantum) , it withdraws from the CPU. Because of this implementation, queries in SQL Server always wait: Once a resource is not yet available, or the query has exceeded its amount-the query enters the pending (Suspended) state and Waits.

Each time a wait condition occurs, the wait time is automatically tracked by SQL Server by waiting for statistics (wait Statistics) . SQL Server reports this information through the DMV sys.dm_os_wait_stats . Each row returned through this DMV represents a specific wait in SQL Server-the so-called wait type. By evaluating wait statistics, SQL Server tells you what is the most prominent wait type. You can then focus on the wait type and find out the root cause of the problem, and why the wait time is so high for this wait type.

I/O latency statistics (I/O Latency Statistics)

in addition to waiting for statistics, another very important thing is that SQL Server also reports I/O latency statistics ( I/O Latency Statistics). With these delay times it is easy to find out which file of your SQL Server instance has a delay time. SQL Server reports this information through DMF sys.dm_io_virtual_file_stats . You can pass in database_id and file_id. If you provide null values for these 2 values, you will get the latency statistics for all query-related files in the SQL Server instance (data and logs).

The most important for this DMF is the io_stall_read_ms and io_stall_write_ms columns. The cumulative delay in read and write operations to your storage since the last time SQL Server was restarted. If you divide these 2 values by the Num_of_read and num_of_writes columns, you get the average latency for disk reads and writes from the SQL Server perspective. This is very handy for troubleshooting your storage subsystem.

If this DMF reports very high latency, you should not simply run to the storage vendor and buy faster storage. The first step is always to think about why you have such a high delay time. When I use this DMF on different systems, tempdb always reports a high latency. But that doesn't mean you need to move tempdb to faster storage, such as SSD drives. The first step is always to think about your specific database "Why" you have such a high delay time. If it's tempdb, you can try to minimize the use of tempdb-for example, by applying a reasonable indexing strategy to get rid of the sorting and hashing operators in the execution plan, and the 2 operators will spread to tempdb.

Wait for statistics and I/O latency statistics Straight Report your symptoms, your task is to identify the intrinsic root cause of the performance problem, analyze it, and finally solve it.

Summary

In today's performance Tuning Training we discussed in detail the wait statistics and I/O latency statistics in SQL Server. For performance monitoring and troubleshooting, these 2 Dmvs/dmfs are important because you can find out where SQL Server is currently having performance issues. Next week we'll talk about tempdb in detail, and I'll call it a public restroom for SQL Server. Keep your eye on it!

22nd/24-week wait and I/O latency statistics