Get To Know How SQL Server Manages Disk I/O

Stephen West ~ Published: January 2nd, 2019 ~ SQL ~ 4 Minutes Reading

SQL users often face the issue of slow SQL Server database, which is caused by I/O related bottlenecks in SQL Server. I/O subsystem is a major element of the performance of SQL Server because the pages keep moving in and out of buffer pool. Similarly, tempDB and SQL transaction logs also cause I/O traffic. Therefore, users have to ensure proper performance of I/O subsystem. In this write-up, we will describe how SQL Server manages disk I/O to identify and avoid bottlenecks.

Using Performance Monitor

Performance Monitor is a popular way to check how much load the I/O subsystem is having. For this, set any of the following performance counters:
PhysicalDisk Object: Avg. Disk Queue Length: This counter indicates average number of queued read and write requests of the chosen physical disk. The more the value, the more disk operation is in queue.

Avg. Disk Sec/Read and Avg. Disk Sec/Write: This counter displays the average time taken to read/ write data from/ to disk. Any higher value than 20 ms requires attention.

Physical Disk: %Disk Time It shows the ratio of elapsed time when the disk drive is engaged with read or write requests. Its standard value is less than 50 percent.

Disk Reads/Sec and Disk Writes/Sec: These counters show the rate of read and write operations on I/O disk, which should not be more than 85 percent of the disk capacity.
The disk capacity can be determined by gradually accelerating the load of the system. A good method to do so is to use SQLIO.

Dynamic Management Views

Some Dynamic Management Views (DMVs) can also be used to check I/O bottlenecks:

Sometimes, users try to access a page to read and write, but that is not available in the buffer pool at that time. This results in the occurrence of an I/O latch wait. Based on the type of request that has been made, it gives rise to waits on PAGEIOLATCH_EX or PAGEIOLATCH_SH. These waits also indicate I/O bottleneck. Find out about the latch wait statistics by sys.dm_os_wait_stats DMV. Also, compare waiting_task_counts and wait_time_ms values of normal time during poor performance to find out I/O problems.

Use these DMVs to find out the pending I/O requests and disks responsible for the bottleneck.

Perform Best Practices of Disk Fragmentation and Disk Configuration

Check disk configuration and disk fragmentation to manage disk I/O.
(i)Disk Fragmentation:
Since NTFS file fragmentation decreases the disk performance, disk defragmentation should be done on a regular basis. At the same time, a policy regarding the defragmentation process should exist.

In certain situations, a SAN performs worse when the defragmentation is enabled, so SANs need to be handled individually, like one case at a time. If there is fragmentation in Index, NTFS I/O utilization also becomes high. However, in case of SANs that offers better performance on random I/Os, it will have different impact.

(ii)Disk Configuration:
Some approaches about disk configuration should be performed for the proper management of disk I/O. For example, log files should be kept separately from the data files on a physical disk.

In database data files that are being used heavily, the I/O profiles are random. At the same time, the I/O profiles are sequential in case of all database log files. The only exception is the transactions that require rollback. Use internal disks only for database log files, as these disks perform best in case of sequential I/O, but badly for random I/O.

All the data of the database and their log files should have their own data packs. It is advised to place the database log files on two internal disk drives configured as RAID 1, for the most comprehensive performance. Keep the database data file on a SAN system and make sure that it gets accessed only by SQL Server as well as receive controlled query and report. Any kind of Ad-hoc access must not be allowed.

One every possible instance, enable write caching and also, protect the cache power defect and all other possible failures.

For OLTP system, keep OLAP and OLTP environments separate to avoid bottleneck. Avoid unnecessary I/Os by creating indexes where necessary.

Final Words

Since SQL Server bottleneck can be avoided by disk I/O management, people often want to know how SQL Server manages disk I/O. This post discussed various techniques of disk I/O management that can be done on SQL Server. Users can easily follow the processes mentioned here for smooth performance of SQL Server.