Several different methods can be used to track I/O performance to determine if there is an I/O bottleneck. Tools are available at both the storage level and the virtual level to aid in performance.
In every SQL Server environment will exist at least two options to monitor I/O bottlenecks: SQL Server DMVs and Performance Monitor counters.
Performance Monitor is a Microsoft Windows tool found in the Control Panel under Administrative Tools. It lets you capture statistics about SQL Server, memory usage, I/O usage, and much more.
You can run the tool interactively using the GUI or set it up to collect information behind the scenes to review the data later.
In this study case, we collected some counter using the Perfmon, and as per the image below, you can see that for writes operation on average is 0.157 sec which is similar to what SQL Server is reporting 177ms or 0.177 sec.
This tool allowed us to tell that the drive D: does not present the same performance compared to drive G: and I: on the client’s SQL Server environment.
CrystalDiskMark is an open-source disk drive benchmark tool widely used in Windows platforms. It allows you to measure read and write performance. You can select the number of test runs, desired file size, and which logical drive you are testing.
Using this benchmark tool, we were able to confirm, as you can see from the disk speed tests below, that drive D is about 10X slower for reading and 34x slower for writing than the other drives.
- SEQ Q1M Q8T1 – lots of long, sequential operations. For SQL Server, this is somewhat akin to doing backups or doing table scans of perfectly defragmented data, like a data warehouse.
- RND 4K Q32T16 – tiny random operations, but many done at a time. This is somewhat akin to an active OLTP server or a TempDB drive. (This is what matter for application performance)
Drive D test 1: 70% Read, 30% write, File size 64GB
Drive D test 2: 70% Read, 30% write, File size 64GB
Drive D test 3: 70% Read, 30% write, File size 64GB
Drive D test 4: 70% Read, 30% write, File size 64GB
For reference, see below the results for drive G: and I:. Here Q32T16 is above 300mb/s.
Drive G test 34: 70% Read, 30% write, File size 64GB
Drive I test 34: 70% Read, 30% write, File size 64GB
A more in-depth report was sent to the client, which the storage team used to solve the drive D latency.
As expected, from the SQL Server perspective, everything was correct.
In 99% of cases, SQL Server bottlenecking on Disk! You must pay attention to the read and write I/O latencies for your SQL Server environment’s critical components.
Understand how your SQL Server infrastructure stacks up to vendor recommendations, with industry best practices and guidance from a team of experts.