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
Last activity: Apr 13, 2025, 11:38 PM