Mirror server hardisk I/O utlisiation 100%

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Mirror server hardisk I/O utlisiation 100%

Mirror server hardisk I/O utlisiation 100%

Rate Topic

Display Mode

Topic Options

Author

Message

bugg

Posted Monday, December 03, 2012 7:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 123, Visits: 402

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

bugg

Posted Tuesday, December 04, 2012 5:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 123, Visits: 402

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

bugg

Posted Tuesday, December 04, 2012 2:18 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 123, Visits: 402

Anyone have any suggestions? What other things I may check to see what the bottleneck is?

Thanks

Post #1392701

ESAT ERKEC

Posted Wednesday, December 05, 2012 12:53 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 1:46 AM
Points: 222, Visits: 707

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

bugg

Posted Wednesday, December 05, 2012 2:06 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 123, Visits: 402

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 »

Permissions