Investigating the System Monitor
-
Hi,
I found this to see my Hard disk bottleneck.
In the System Monitor there are five Physical Disk counters for each physical disk contained in the Logical Drive that are key to identifying I/O bottleneck. Each disk should be investigated individually.
1. Avg. Disk Queue Length is the average number of both read and write requests that were queued for the selected disk during the sample interval.
2. Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk.
3. Avg. Disk sec/Write is the average time, in seconds, of a write of data to the disk.
4. Disk Reads/sec is the rate of read operations on the disk.
5. Disk Writes/sec is the rate of write operations on the disk.
First, on the System Monitor properties window, slide the time interval to only include the period of “slow performance.” Then calculate the I/Os per disk as follows.
Q1. System Monitor properties window, slide the time interval to only include the period of “slow performance.”
Where is this ? I can't see anything under System Monitor-Properties?
Q2. How long we should monitor and which value we should consider( average or maximum)? duration set 1:40?
Q3. What are the expected value for RAID 5 System?
AusNetIT Solutions
Web Design | Web Hosting | SEO | IT Support -
HI,
Thanks, But tell me how to setup this Performance Monitor and get the value?
AusNetIT Solutions
Web Design | Web Hosting | SEO | IT Support -
the main disk counters which are of interest are the io completion time and the number of disk io. You wouldn't want to see an io taking more than 10ms with reads being quicker and high performance systems better. I expect a write of 6ms or better on a SAN with reads around 3 ms ( I'm assuming 10k/15k scsi disks ) The number of io that an array can support is dependent upon raid, spindles and controller, this is documented in many sql books, Admin companion, inside books, technet, msdn etc.
I never use disk queues ( the other counters will indicate issues ) Disk idle is better than usage ( subtact from 100 ) but this counter may not work on a san.
Raid 5 has a big performance hit on writes. I have some figures posted
http://sqlblogcasts.com/blogs/grumpyolddba/
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Just to piggy back on Colin's response.
Speaking to MS engineers, they gave me a guidline of 2ms is excellent read times. 10ms is not very great and 15 or above is horrible performance. And again, adding a few ms to each of the read counters is a good guide for write response times.
I do use current disk queue length but you have to work out this counter based on the number of disks you have for the drive.
Batches/sec is also a good counter which I monitor from time to time.
-
you can collect mdf/ldf io sttats using fn_virtualfilestats, search the site or google for info on using this. I think I blogged a whole load of code to enable collecting this data into a table every hour and producing stats. ( see post above for url 
Agreed io times should be minimal on a high performance storage system, however I know not everyone has this luxury , your first step to improve write times is to abolish raid 5 ( and 6 which is worse ) !!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply