Thank you for the detailed and methodical description — your testing setup and initial diagnostics are very solid. Based on my experience architecting, deploying, and performance tuning database systems across SQL Server, Oracle, and DB2 on platforms ranging from Windows to Linux, and storage arrays from direct-attached RAID to SAN on PowerFlex and VxBlock, I’d suggest a few key areas to investigate:
RAID Controller Cache Configuration – Write Bias Hurting Reads
Your RAID 10 array is configured with 90% write / 10% read cache allocation. While this is ideal for write-heavy OLTP patterns, it could be contributing significantly to underperformance on reads, particularly if:
The controller lacks sufficient read-ahead caching for random I/O patterns typical of SQL Server page reads.
The read cache is frequently overwritten due to the write-dominant configuration.
The RAID firmware or driver is not aggressively prefetching blocks on random seeks.
Even though fio shows only ~5ms latency, synthetic benchmarks often bypass or isolate caching behavior, and may not mimic SQL Server’s access pattern — particularly non-sequential, buffer pool-driven reads that can lead to cache misses.
Recommendation:
Adjust the cache ratio closer to 50/50 or 70/30 temporarily and monitor if read latency improves.
Update RAID controller firmware and drivers (critical step — known cache issues have been patched by LSI, Dell, and HP in past releases).
Consider enabling read-ahead cache or adaptive read-ahead if supported.
SQL Server's Read Latency ≠ Raw Disk Latency
SQL Server’s "Stalls/sec for Read" is not only a disk latency metric — it measures how long SQL Server waits for data to be read from disk into the buffer pool. High values can occur even under:
Page latch contention (e.g., frequent access to a hot page causing concurrent read waits).
Memory pressure, where pages are frequently flushed from buffer pool and reloaded.
Parallel query plans causing CPU or latch contention.
Slow SMB or HBA stack, even if fio bypasses it (e.g., using direct I/O).
Your IOPS rate is very low (5–10 reads/sec), which suggests that SQL Server is issuing very few physical reads, but each read is taking unusually long to complete — likely due to blocking, scheduling latency, or IO path inefficiencies, rather than true storage IOPS saturation.
Recommendation:
Use sys.dm_io_virtual_file_stats to correlate file-level latency.
Run sys.dm_exec_requests to check for wait types like PAGEIOLATCH_SH, ASYNC_IO_COMPLETION, or WRITELOG — these will indicate where bottlenecks lie.
Examine memory grants and buffer pool turnover using sys.dm_os_memory_clerks.
fio vs. SQL Server Access Patterns – Synthetic Benchmarks Mislead
Your fio test uses:
fio --rw=randread --bs=68k --numjobs=4 --iodepth=1 --direct=1
That means:
Queue depth of 1 (very shallow)
No interaction with SQL Server's memory stack or buffer pool
Bypasses NTFS metadata access patterns
No consideration for SQL Server’s allocation unit boundaries or extent reads
SQL Server reads are not purely random — even for OLTP, they often involve sequential extent reads (64KB blocks, 8 pages), along with page prefetching, index seeks, and lookups, which can be influenced by fragmentation, page splits, or missing statistics.
Recommendation:
Test with tools that mimic SQL Server's real workload, like DiskSpd with similar queue depth, or Replay SQL Profiler/Extended Events traces.
Analyze with SQL Server ReadTrace or Query Store to identify high-read queries and their actual wait times.
Consider Storage Stack Issues Beyond Disk (e.g., StorPort, NTFS, Driver)
You mentioned:
"Write latency is under 1ms, but read latency is >20ms."
That strongly hints the issue lies not in the physical spindles or RAID (RAID 10 with SSDs or fast disks shouldn’t show this imbalance), but in:
Windows StorPort driver inefficiencies for read queues
Antivirus software scanning .mdf files (very common culprit)
Volume misalignment or NTFS fragmentation
Lack of "Large System Cache" setting or improper "Enable write caching on the device" checkbox settings
Recommendation:
Run latencymon or Windows Performance Analyzer to profile disk subsystem latency.
Ensure AV exclusions for .mdf, .ldf, .ndf are in place.
Check whether the NTFS volume has high fragmentation (rare for modern systems, but worth ruling out).
Transient Write Spikes Could Be Indirect Clue
The brief spike to 5,000+ writes/sec once per minute might be:
Checkpoint operations, where dirty pages are flushed to disk.
Lazy writer background activity
Auto-growth events or index maintenance jobs
If SQL Server is flushing dirty pages aggressively, it could be competing with read operations for I/O bandwidth during those periods, even on a RAID 10 set.
Recommendation:
Review SQL Server logs and sys.dm_os_performance_counters for checkpoint/sec or lazy writes/sec.
Check autogrowth settings for your database and tempdb.
Final Thoughts – Synthesizing My Multi-Platform Experience
Across my years working with Oracle (on ASM, Exadata, and RAC), DB2 on z/OS and LUW, and SQL Server on Windows and Linux, one common theme emerges:
“Synthetic I/O testing rarely tells the whole story — true bottlenecks emerge only under realistic, multi-layered workloads.”
In this case, you’ve likely isolated an issue that’s beyond the disk hardware — potentially in the I/O path, memory pressure, RAID controller behavior under mixed workloads, or SQL Server’s own memory/IO strategy.
I’d recommend adjusting the cache ratios, monitoring latch waits, validating your memory grants, and testing under controlled T-SQL workloads to simulate realistic read pressure.
wpr -start diskio -start fileio -start minifilter