December 3, 2012 at 7:17 am
Hi,
Should my mirror server hard disk I/O utilization be between 90% and 100%. ?
I understand its the mirror server and the hard disk is constantly getting written to but should it be this high?
Thanks
December 4, 2012 at 5:56 am
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
December 4, 2012 at 2:18 pm
Anyone have any suggestions? What other things I may check to see what the bottleneck is?
Thanks
December 5, 2012 at 12:53 am
Task Manager -> Resource Monitor ->Disk-> Filter the sql server.exe and then look at the ratio can you see mdf and ldf file of mirror db....
for windows 2008 server
December 5, 2012 at 2:06 am
ESAT ERKEC (12/5/2012)
Task Manager -> Resource Monitor ->Disk-> Filter the sql server.exe and then look at the ratio can you see mdf and ldf file of mirror db....for windows 2008 server
Yep had a look at that sqlservr.exe is at the top writing to the mirrorDB.mdf 400,000 - 1,296,531 Write(B/Sec)
Followed by the log file writes around 24,104 (B/Sec)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply