When it comes to SQL Server performance, disk I/O is often one of the most critical and overlooked components. While CPU and memory get much of the spotlight, the underlying storage subsystem plays a foundational role in how efficiently SQL Server can read and write data. Slow or misconfigured disk I/O can lead to query bottlenecks, long transaction times, and degraded user experience—especially under heavy workloads.
This guide helps DBAs understand how SQL Server interacts with storage, how to monitor disk I/O, and how to optimize it for better performance. From key metrics to practical tuning tips, you will gain the insights needed to keep your SQL Server running smoothly at the storage layer.
Figure-1: Disk I/O Performance Analysis
Symptoms of Slow Disk I/O
Typically, disk I/O issues manifested as high amount of I/O wait types, poor performance, excessive disk latency, I/O warning are SQL Server error logs. Your SQL Server also logs the query wait time in various wait types such as
- PAGEIOLATCH_SH
- PAGEIOLATCH_EX
- WRITELOG
- IO_COMPLETION
If these waits exceed 10-15 ms on a consistent basis, then I/O is considered a bottleneck.
Importance of Disk I/O Performance Analysis
SQL Server often demands high I/O throughput, but since disk operations are much slower than memory or CPU, any I/O bottleneck can significantly impact overall performance. Identifying and resolving issues in the I/O path is crucial for maintaining a responsive and efficient SQL Server environment.
Disk performance is not just about the physical disk—it involves the entire I/O subsystem, including controllers and system buses. Common signs of disk I/O issues include slow query response times, timeouts, and overall system lag, making it essential for DBAs to monitor and tune disk-related metrics effectively.
Disk I/O Performance Counters
PhysicalDisk counters reflect activity on the actual physical disk, while LogicalDisk counters track activity on individual partitions created within that disk. For example, if a single physical disk is split into two partitions like D: and E:, LogicalDisk counters can help monitor each partition separately. However, since performance bottlenecks occur at the physical disk level, it is generally more effective to focus on PhysicalDisk counters for identifying I/O issues.
Figure-2: Disk I/O Counters in Perfmon
Disk Transfers/Sec
Disk Transfers/sec measures the rate of read/write operations on the disk.
- For mechanical hard disk can do 180 disk transfers/second for sequential I/O (IOPS), and 100 disk transfers/second for random I/O. For random, it is lower as more disk arm and head movements are involved.
- For SSD, it is around 5,000 IOPS to 500,000 IOPS.
Disk Bytes/Sec
It is the rate at which bytes are transferred to or from the disk during read/write operations. A typical disk spinning at 7200 RPM can transfer about 1000MB per second. For SSD, transfer rate is much higher.
Avg. Disk Sec/Read
Average Disk sec/Read is the average time in seconds needed to read data from disk. The recommended values for Disk sec/Read are:
| Value (ms) |
Performance |
| < 8 |
Excellent |
| 8 – 12 |
OK |
| 12 – 20 |
Fair |
| > 20 |
Bad |
Values consistently greater than 20ms indicates poor performance.
Avg. Disk Sec/Write
Average Disk sec/Write is the average time in seconds needed to write data to disk. The recommended values for Disk sec/Write are:
| Value (ms) |
Performance |
| < 1 |
Excellent |
| 1 – 2 |
OK |
| 2 – 4 |
Fair |
| > 4 |
Bad |
Disk read and write speeds usually differ, and consistently high values for Average Disk sec/Read or Average Disk sec/Write indicate a potential bottleneck. If the issue affects all disks, it likely points to disk communication channels; if it's limited to one disk, the disk itself may be the problem—monitoring both can help identify the need for disk controller cache reconfiguration or optimization.
Buffer Manager Page Reads/Writes
When diagnosing Disk I/O issues, tracking pages moving in and out of the buffer manager helps to determine if the I/O activity is internal to SQL Server. So, monitor this key metric also.
Resolutions of Slow Disk I/O
Define the Normal
Define normal of your workload and monitor the disk utilization trend over the period. You can use DBA Dash for monitor all the above counters and defining the normal. In case you see any deviation from normal, act accordingly.
Query Optimization
Find out your top resource intensive queries and optimize them. Use sp_BlitzCache for this purpose.
Choose the Appropriate Disk Subsystems
Picking the right kind of disks subsystems for your workload. As discussed earlier, mechanical disk subsystem has 100 to 180 IOPS where as SSD's IOPS starts from 5,000. So, it is better to use SSD disk subsystem for resource intensive workload.
Select the Appropriate RAID
- RAID 0 - This configuration comes without fault tolerance. It should only be used when data reliability is not critical, as failure of any disk results in total data loss—making them unsuitable for database files. Since, it has less overhead for read/write, so you may use it for tempdb.
- RAID 1 - It offers high fault tolerance by mirroring data to a separate disk and is ideal for smaller and critical files like transaction logs, OS files, and SQL Server system databases such as master and msdb.
- RAID 5 - It has decent fault tolerance with efficient use of disk space, but its write performance degrades significantly during a disk failure. It is best suited for read-heavy workloads, not for write-intensive operations like transaction logs, though data files with infrequent writes can be placed on RAID 5.
- RAID 1+0 (RAID 10) provides excellent fault tolerance and high performance by mirroring each data disk, making it ideal for write-heavy workloads and performance-critical systems. Although more costly than RAID 5 due to its higher disk requirements, it offers superior read and write efficiency.
Disk Settings
Famous SQL Server Specialist Alexander Arvidsson has below guidelines for disk settings.
- Double check your partitions alignment with the underlying stripe size. Though Windows 2008 and above itself (usually) takes care of this.
- Format your partitions to 64KB NTFS Allocation Size for SQL Server data, logs, and TempDB.
- Turn on Instant File Initialization. Otherwise, every time a file is created it needs to be filled with zeroes. Which creates a huge pressures on the storage system.
- Disk layout:
- C: – Operating system only
- D: – SQL Server installation
- E: – Data files
- F: – Log files
- S: – SQL Server system databases & backup directory
- T: – TempDB
This gives a good spread of I/O over multiple disks. Always use SSDs for all databases, and allocate fastest storage for the TempDB.
Creating Multiple Files and Filegroups
For better performance and recoverability, follow these:
- Use the primary filegroup only for system objects and
- Place all user objects in secondary filegroups.
This separation helps ensure easier recovery in case of corruption, especially if the primary data file and log files remain intact. Moreover, putting different filegroups will increase performance.
Placing Log Files and Data Files Separately
Transaction log activity is mostly sequential write I/O, so isolating it from random I/O (like data file access) can enhance performance. However, if you put all log files in a single disk, it can again create random I/O. It is better to place mission-critical log files to dedicated storage paths.
Most of the time accessing data from a hard disk is spent on the physical movement of the disk head, so separating transaction log files from data files reduces disk head movement and improves performance. Even with SSDs, if you isolate the data file from log file, it will enhance the performance. Moreover, a workload with multiple databases, separate the transaction log files from each other to improve the performance. Always try to isolate the highest I/O files from other high I/O files. This will reduce contention on the disks and enhance performance.
Adding System Memory
When physical memory is low, the system relies more on the disk, leading to excessive paging and disk usage. Addressing memory bottlenecks can help reduce this dependency and improve overall performance.
Final Words
Understanding and optimizing disk I/O is critical for maintaining SQL Server performance, especially as storage remains one of the slowest components in the hardware stack. By analyzing disk metrics, selecting the right RAID configuration, isolating high I/O files, and separating log and data files strategically, DBAs can significantly reduce contention and enhance throughput. Effective disk I/O management not only improves query responsiveness but also contributes to overall system stability and scalability.
References
Going Further
If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too!
Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.
Let’s make your SQL Server to take your business Challenge!
For any queries, mail to mamehedi.hasan[at]gmail.com.