Disk

This resource contains monitors specific to the current status of the disk. The icons of the listed performance counters in the Statistic Name column change colors to reflect the listed counter's current state. Clicking any performance counter in the Statistic Name column takes you to the Performance Counter Details page for that performance counter.

If the value of a listed performance counter crosses the Warning threshold, the chart for the listed counter displays a yellow background. If the value of a listed monitor crosses the Critical threshold, the chart for the listed counter displays a red background. Hovering over any time period within the chart displays detailed information about that time period in the tooltip. Hovering over a counter gives you detailed information about that performance counter.

 

Counter Expert Knowledge

Average Disk sec/Read

This performance counter shows the average time, in seconds, of a read of data from the disk.

The returned value indicates the average time of read data from the disk. 4-8ms is ideal. The returned value is considered acceptable up to 20ms. Any higher value needs further investigation.

Potential Issue: If a value greater than 15-20ms is reported, this may indicate disk bottlenecks.

Resolution: Increase the number of hard disks.

Average Disk sec/Write

This performance counter shows the average time, in seconds, of a write of data to the disk.

The returned value indicates the average time of write data from the disk. 4-8ms is ideal. The returned value is considered acceptable up to 20ms. Any higher value needs further investigation.

Potential Issue: Values greater than 15-20ms nay indicate disk bottlenecks.

Resolution: Increase the number of hard disks.

Forwarded Records/Batch Requests

This performance counter identifies the use of a pointer which has been created when variable length columns have caused a row to move to a new page in a heap.

Potential Issue: Rows with Varchar columns can experience expansion when Varchar values are updated with a longer string. In the case where the row cannot fit in the existing page, the row migrates and access to the row traverses a pointer. This only occurs on heaps (tables without clustered indexes).

Resolution: Evaluate clustered indexes for heap tables. In cases where clustered indexes cannot be used, drop non-clustered indexes, build a clustered index to Reorg pages and rows, drop the clustered index, then recreate non-clustered indexes.

Forwarded Records/sec

This performance counter returns the number of records fetched through forwarded record pointers.

Tables without a clustered index. If you start with a short row, and update the row creating a wider row, the row may not fit on the data page. A pointer is put in its location and the row is forwarded to another page.

Potential Issue: Look at the code to determine where the short row is inserted followed by an update.

Resolution: Can be avoided by:

  1. Using default values so that an update does not result in a longer row that is the root cause of forwarded records.
  2. Use Char instead of Varchar. This fixes the length so that an update does not result in a longer row.

Physical Disk Time

This performance counter returns the ratio of elapsed time when the disk drive was busy with read or write requests.

This performance counter is deceptive because it makes no accommodation for multiple spindles. The more spindles (i.e. physical hard disks) you have, the higher the percentile values can go. If these spindles are shared across LUNs or other services, you may have high numbers on this counter without any correlation to SQL Server activity. The value for this counter should be below 50%. 

Potential Issue: If this performance counter sustains an average above 70%, you may have contention with your drive or RAM.

Resolution: You should increase number of hard drives used by SQL server.

Zooming

You can have the chart show a predetermined time period of data by clicking on any one of the three Zoom buttons in the head of the Zoom column. Alternatively, you can have the chart show a specific date range by dragging the mouse over an area of the chart. The time and date displayed at the top of the resource shows the amount of data currently loaded into the chart. This can be changed by clicking Edit from within the resource.

The Value from Last Poll column shows the most recent data available for the listed statistic.