Disk I/O and PAGEIOLATCH_XX

We have been having CPU issues with one of our servers lately and while we have been looking into this we have also noticed queries running slowly with waits of PAGEIOLATCH_XX. In particular, a reindex job is seemingly always having this wait type.

In response, I have run a collect against sys.dm_io_virtual_file_stats and then broken this down into time chunk and worked out the average stall per operation. While there are spikes mostly, the disk seems to have a value of regularly under 20 ms. From what I remember, 20 ms is the recommended value(?).

Further to this I have run Glenn Barry's script:

select db_name(database_id) as DatabaseName, file_id
,io_stall_read_ms
,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms
,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads +
num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null) --where db_name(database_id) = 'tempdb'
order by [DatabaseName] desc'

Which calculates the average I/O stall also and this also confirms stalls less than 20 ms.

I have also looked in the following to see if any pending tasks are taking longer than recommended, but this isn't throwing up any pending I/O operations taking regularly longer than 20 ms.

SELECT db_name(database_id) as 'Database',
file_name(file_id) as 'File',
io_stall,
io_pending_ms_ticks
FROM sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,
 sys.dm_io_pending_io_requests as iopior
WHERE iovfs.file_handle = iopior.io_handle

My question now is: If the issue is not disk related, why am I seeing lots of PAGEIOLATCH_XX waits? In particular, why is the reindex running extremely slowly with this wait type?

Could this be related to CPU pressure?

==============

PAGEIOLATCH_XX waits are logged by SQL Server when it is waiting for data to be read from the disk. Index maintenance is a notoriously intensive operation and because of this it should be performed at your quietest times to avoid any impact on production.

You mention you have queries that are causing the same waits. If this is at the same time as the index maintenance then that is not that odd but if it's happening at other times it could be down to memory pressure (not enough room in RAM to store pages so they need to be read from the disk again), large scans or it could even indicate there is a potential problem with your disks. More investigation is needed to rule each of these out.

StackExchange Database Administrators Q#103305, answer score: 4