Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Mirror server hardisk I/O utlisiation 100% Expand / Collapse
Author
Message
Posted Monday, December 03, 2012 7:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 4:47 AM
Points: 171, Visits: 600
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
Post #1391933
Posted Tuesday, December 04, 2012 5:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 4:47 AM
Points: 171, Visits: 600
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_pct rn ,
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.11 3
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
Post #1392403
Posted Tuesday, December 04, 2012 2:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 4:47 AM
Points: 171, Visits: 600
Anyone have any suggestions? What other things I may check to see what the bottleneck is?

Thanks
Post #1392701
Posted Wednesday, December 05, 2012 12:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:19 AM
Points: 231, Visits: 736
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
Post #1392815
Posted Wednesday, December 05, 2012 2:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 4:47 AM
Points: 171, Visits: 600
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)
Post #1392843
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse