Sql-server – High PageIOLatch_SH Waits with High Drive Idle times

sql serverwindows

We are experiencing high volume of PageIOLatch_SH waits on our database (row counts in the Billions). However it seems that our drive Idle time Percentage hovers around 50-60 percent. CPU usage is nill. The Database Tuning Advisor gives no suggestions for optimization. The query plan (actual) from the single stored procedure used on the database puts the majority of the expense on index seek (yeah I know these should be optimial) operations.

Anyone have suggestions of how to increase throughput?

Best Answer

Is there memory pressure on your server? The buffer pool may be filling up causing a lot of paging out to disk.

SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Buffer Manager'
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

-- Is this a NUMA system?
SELECT
  CASE COUNT(DISTINCT parent_node_id)
     WHEN 1
         THEN 'NUMA disabled'
         ELSE 'NUMA enabled'
  END
FROM sys.dm_os_schedulers
WHERE parent_node_id NOT IN (32, 64);

Also, check the number of rows returned and the hierarchy in your where statements. You may be returning more rows than you require.

Related Question