Okay I've done a bit of digging and its more than likely the mirrored db that is causing the high I/O disk activity. Below are the stats ive collected
Info gathered from dm server stats
db ,file_type ,io_mb ,io_stall_s ,io_stall_pctrn ,
DB_Mirror ,data ,919078.42 ,2765993.62 ,83.74 ,1
DB_Mirror ,log ,58954.74 ,291365.28,8.82 ,2
tempdb ,data ,11838.19 ,135767.98,4.113
audit ,data ,24905.06 ,47932.41,1.45 ,4
-- Average stalls per read, per write, and per total input/output
-- for each database file.
DB , io_stall_read_ms ,num_of_reads ,avg_read_stall_ms, io_stall_write_ms , num_of_writes ,avg_write_stall_ms ,io_stalls , total_io , avg_io_stall_ms
tempdb , 1404523 ,97861 ,14.4 ,134363460,99434 ,1351.3 ,135767983 , 197295 ,688.1 ,
audit , 47643279 ,336573 ,141.6 ,289131 ,18395 ,15.7 , 47932410 , 354968 ,135.0
tempdb , 175 ,86 ,2.0 ,832179 ,7247 , 114.8 , 832354 , 7333 , 113.5
DB_DEV , 20520495 ,516235 ,39.8 ,465210 ,21856 ,21.3 , 20985705 , 538091 ,39.0 ,
DB_Mirror , 33367858 ,2718983 ,12.3, 2732894677 ,99223544 , 27.5 , 2766262535 , 101942527 ,27.1
There doesn't seem to be any pending I/O requests by file.
Info from Perfmon counters
These don’t seem to say the disk is over loaded? As there are below 20ms
•Avg. Disk sec/write: Avergae = 0.015 -> 15ms
•Avg. Disk sec/read: Avergae = 0.004 -> 4ms
•Avg. Disk sec/read: Transfer = 0.005 -> 5ms
% Disk Time: Average = 84.793 ms
Average Disk queue length = 1.150-> 115.0ms
The Resource monitor shows sqlserv.exe total(Read & writes) as (B/sec) 466,828 to the DB_mirror.mdf
I did discover that Tempdb, All Data files and Log files are all on the same hard drive which will impact performance .
Do think moving these to separate hard drives will help? and would it help more if they were solid state hard drives?
Does anyone know other things i can look at to determine the disk I/O bottlenecks?
Thanks