Thursday, March 5, 2015

Performance Monitor tool to identify bottleneck.

Disk I/O
To measure disk I/O activity, you can use the following counters:  
       
       1. PhysicalDisk\Avg. Disk Queue Length
Threshold: Should not be higher than the number of spindles plus two.
Significance: This counter indicates the average number of both read and writes requests that were queued for the selected disk during the sample interval
2. PhysicalDisk\Avg. Disk Read Queue Length
Threshold: Should be less than two.
Significance: This counter indicates the average number of read requests that were queued for the selected disk during the sample interval.
3. PhysicalDisk\Avg. Disk Write Queue Length
Threshold: Should be less than two.

Significance: This counter indicates the average number of write requests that were queued for the selected disk during the sample interval.
 4. PhysicalDisk\Avg. Disk sec/Read
Threshold: No specific value.
      Less than 10 ms – very good
      Between 10-20 ms – okay
      Between 20-50 ms – slow, needs attention
      Greater than 50 ms – Serious I/O bottleneck
Sgnificance: This counter indicates the average time, in seconds, of a read of data from the disk.
 5. PhysicalDisk\Avg. Disk sec/Transfer
Threshold: Should not be more than 18 milliseconds.
Significance: This counter indicates the time, in seconds, of the average disk transfer. This may indicate a large amount of disk fragmentation, slow disks, or disk failures. Multiply the values of the Physical Disk\Avg. Disk sec/Transfer and Memory\Pages/sec counters. If the product of these counters exceeds 0.1, paging is taking more than 10 percent of disk access time, so you need more RAM.

 6. PhysicalDisk\Disk Writes/sec
Threshold: Depends on manufacturer's specification.
Significance: This counter indicates the rate of write operations on the disk.
 7. Physical Disk: %Disk Time
Threshold:  Greater than 50 percent, it represents an I/O bottleneck
Significance: Represents the percentage of elapsed time that the selected disk drive was busy servicing read or write requests.
  8. Physical Disk\Avg. Disk Reads/Sec  and Physical Disk\Avg. Disk Writes/Sec
Threshold: It should be less than 85% of the disk capacity
Significance: It represents the rate of read operations on the disk.
When using above counters, you may need to adjust the values for RAID configurations using the following formulas.
Raid 0 -- I/Os per disk = (reads + writes) / number of disks
Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2
Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks
Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks
For example, you have a RAID-1 system with two physical disks with the following values of the counters.
Disk Reads/sec            80
Disk Writes/sec           70
Avg. Disk Queue Length    5
In that case, you are encountering (80 + (2 * 70))/2 = 110 I/Os per disk and your disk queue length = 5/2 = 2.5 which indicates a border line I/O bottleneck.

Disk I/O issues can be minimized by having larger and sequential I/O activities.    To achieve than you should consider partitioning.    Partitioning can be done in many ways – Database partitioning, Tempdb partitioning, Table partitioning, index partitioning.    At a minimum, it is highly recommended to partition your database in such a way that clustered index, non-clustered index, and transaction logs are each on a separate physical drive and spread across multiple logical filegroups/files within that drive.    

No comments: