Sample Header Ad - 728x90

Unexplained read latency in SQL Server despite healthy RAID 10 disk and good synthetic benchmarks

0 votes
2 answers
130 views
I've set up a SQL Server instance on a dedicated Windows server. The .mdf data file is placed on a dedicated disk configured as RAID 10, with the RAID controller cache ratio set to 90% write / 10% read. I'm observing unexpectedly high read latency based on metrics reported by both SQL Server (e.g., "Stalls/sec for Read") and Windows Performance Monitor (e.g., "Avg. Disk sec/Read"). SQL Server reports "Stalls/sec for Read" consistently above 20ms Performance Monitor shows "Avg. Disk sec/Read" at about 25ms, with only 5–10 disk reads/sec Write performance, in contrast, is consistently excellent. Latency remains under 1ms, which aligns with the RAID controller's write cache configuration. However, I’ve noticed that approximately once per minute, there's a write spike where Disk Writes/sec temporarily jumps above 5,000. Apart from these brief spikes, write performance is stable and fast. To validate storage performance independently, I used fio to simulate random read load against a 100GB data file. Surprisingly, fio reports read latency of only around 5ms, which is much lower than what SQL Server and Performance Monitor are reporting. Here's the fio command I used: fio --name=rand_read_test --rw=randread --bs=68k --size=1G --numjobs=4 --iodepth=1 --ioengine=windowsaio --direct=1 --time_based --runtime=60s --filename=testfile1g This server also has two other dedicated drives: - One for the .ldf log files - One for tempdb Both of these disks show normal latency and no unusual performance behavior. **My questions:** - What could explain this high read latency reported by SQL Server and Performance Monitor, despite relatively low read IOPS and healthy synthetic benchmarks? - Could the RAID controller's cache configuration be limiting read performance in ways not captured by fio? - Are there SQL Server-specific patterns or behaviors that might cause high logical read latency despite light physical disk usage?
Asked by Hamidreza Shokouhi (101 rep)
Apr 13, 2025, 08:58 PM
Last activity: Apr 13, 2025, 11:38 PM