what can cause high buffer i/O on my client's server?

Question

I've got a client's SQL Server with high Buffer I/O.

I have checked long running queries, added new indexes, and there are no locks on the server. Is it a disk issue?

Here are the detail of the waits on the Activity Monitor:

Answer 1

First try checking if your disk latency is within threshold..Below are some metrics.. you may need to see if these are applicable for you

Average Disk sec/Read value

Average Disk sec/Write

You can check this link to configure Perfmon:SQL Server disk performance metrics – Part 1 – the most important disk performance metrics

This link also has tools to stress test your IO and get a report..

How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem

If your disk IO is within latency, try finding the queries which are causing High IO, One of the causes for high disk IO might be inappropriate indexes, you may need to add right indexes,so that SQL won't read unnecessary data

Also you might have less memory,since having less memory causes SQL to flush buffer pool cache .This can cause IO issues as well

DMV to check queries causing high IO

SELECT TOP 25 cp.usecounts AS [execution_count]
      ,qs.total_worker_time AS CPU
      ,qs.total_elapsed_time AS ELAPSED_TIME
      ,qs.total_logical_reads AS LOGICAL_READS
      ,qs.total_logical_writes AS LOGICAL_WRITES
      ,qs.total_physical_reads AS PHYSICAL_READS 
      ,SUBSTRING(text, 
                   CASE WHEN statement_start_offset = 0 
                          OR statement_start_offset IS NULL  
                           THEN 1  
                           ELSE statement_start_offset/2 + 1 END, 
                   CASE WHEN statement_end_offset = 0 
                          OR statement_end_offset = -1  
                          OR statement_end_offset IS NULL  
                           THEN LEN(text)  
                           ELSE statement_end_offset/2 END - 
                     CASE WHEN statement_start_offset = 0 
                            OR statement_start_offset IS NULL 
                             THEN 1  
                             ELSE statement_start_offset/2  END + 1 
                  )  AS [Statement]        
FROM sys.dm_exec_query_stats qs  
   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle 
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
ORDER BY qs.total_logical_reads DESC;