Friday , December 2 2022

SQL Server Disk Latency

There are actually several ways to control disk latency in SQL Server. But in this article, I will be explaining the most frequently used DMV (Dynamic Management View).

Microsoft announced in 2005 that a DMV called sys.dm_io_virtual_file_stats was available. SQL Server records data on execution plans, resource usage, physical and logical uses of indexes since the last boot.

We can get information about the status of the system by querying them. The dm_io_virtual_file_stats function is one of them and returns I/O usage statistics of data and log files of databases.

Can elaborate on this DMV using the query below.

The query output is as follows.

The column named “Latency Desc” will help us to make sense of the query result. Subject to the following values of course.

The thing to note here is actually the values you see above 101 ms. If you see between these values, it means that there is a disk-based bottleneck, no matter how good you can do at the database level.

In our example, we can easily see that the disk latency of tempdb data and log files is problematic. This data is reset when SQL Server is restarted.

In SQL Server, it is important to have the database files on different physical disks as much as possible. The script below shows the traffic on the drives where the database files are located.

As a result of this query, if most of the traffic is flowing on certain drives, it will be beneficial to move some files there to other drives.

About Çağlar Özenç

Leave a Reply

Your email address will not be published. Required fields are marked *