Documentation forSQL Sentry

SQL Sentry Performance Analysis Disk Activity

Applies to: SQL Server, SSAS*, VMware*, and Windows* targets.

Introduction

Note:  *The Disk Activity and Disk Space tabs are focused on database files. For Windows it applies to VMware and Hyper-V hosts for vDisks and VHDX files, respectively. The tabs will only display for SSAS, VMware, and Windows when the applicable files are available for monitoring.
Additional Information: If the Disk Activity tab or the Disk Space tab does not display, there could be an issue with WMI on the system you're monitoring. For more information about troubleshooting WMI on your system, see the WMI Troubleshooting MSDN article.

Disk bottlenecks represent one of the most common sources of performance problems for Windows and SQL Server. The Disk Activity tab provides a patented graphical disk analysis system that breaks down disk activity and latency at the controller, physical disk, and file level, highlighting bottlenecks at any point in a disk system.

Target Example View
SQL Server SQL Sentry Disk Activity for SQL Server
VMware Disk Activity tab for VMware
Windows Disk Activity tab for Windows & Hyper-V

The top pane of the display shows a graphical representation of the entire disk system as Windows sees it. The middle pane displays activity metrics in either a hierarchical disk system format or database/virtual hard disk/ virtual machine disk list format. The bottom pane displays graphs showing read and write latency, IOPS and throughput for the disk or file chosen in the top pane, or middle pane.

Target Example View
SQL Server Disk Activity tab SQL Sentry
VMware Disk Activity tab VMware
Windows Disk Activity tab Windows & Hyper-V

Disk Activity Modes

The Disk Activity tab has two modes, Real Time and History. The active mode is controlled with the auto-refresh toolbar button.

Select the auto-refresh Play button to enter the Real Time mode.

Disk Activity tab Auto Refresh toolbar button

The System box represents the underlying storage in your monitored environment. The Controller box(es) display the controller in your environment. A controller works as an interface between the motherboard and other components, and makes sense of the signals going to, and coming from the CPU. 

SQL Server
  • The top pipe represents Read activity between the System and Controller.
  • The bottom pipe represents Write activity between the System and Controller.
Disk Activity tab SQL Sentry pipe example
VMware
  • The top pipe represents Read activity between the System and the Datastore.
  • The bottom pipe represents Write activity between the System and the Datastore.
Disk Activity tab VMware pipe example
Windows
  • The top pipe represents Read activity between the System and the Controller.
  • The bottom pipe represents Write activity between the System and the Controller.
Disk Activity tab Windows & Hyper-V pipe example

Data for the last collected sample is shown, and moving segmented lines indicates the I/O direction (read or write) and latency for each disk, file, and controller or datastore.

  • The top pipe represents Read activity.
  • The bottom pipe represents Write activity.

Disk Activity tab Read and Write Activity

The thickness and color of the displayed segmented line indicates the amount of latency being experienced. As latency increases, the lines grow in width and changes through green, olive, orange, and red to give a quick visual indication of the decreased performance.

Selecting any file displays a tooltip that shows the numerical values for the reads and writes hitting a file. The amount of overall latency being experienced by the disk is distinguished from the amount of latency for individual files. The overall disk latency is represented with the horizontal pipe connecting all the individual files, while the small vertical pipes represent latency at the file level.

Note:  A disk appearing red indicates a suspect offset setting. This may affect IO performance.

Disk Activity tab red disk icon

The following table lists the color of the segmented line and the corresponding range of latency values. All latency values are listed in milliseconds (ms).

Color Range

Color Range Read Latency Write Latency
Green < 10 ms < 10 ms
Greenish-Yellow to Orangish-Yellow 10-20 ms 10-20 ms
Orange to Reddish-Orange 21-30 ms 21-40 ms
Red > 30 ms > 40 ms

Read and Write Latency Color Gradients

Disk Activity Read and Write latency color gradients

Select the auto-refresh Pause button to enter History mode. Aggregate disk metrics are displayed for the selected date range, and the solid lines indicate latency. History mode enables you to see how your disk system performs over any period.

Disk Activity tab History mode toolbar buttons

Note:  In history mode the pipes display as solid lines representing latency at the selected time frame.

Toolbar

Disk Activity tab toolbar

The following toolbar options are available when auto-refresh is paused:

View Description
Disk Grid View Toggles between showing the File Grid view and the Disk Grid view. Note:  When auto-refresh is paused, these viewing options are available.
File Grid View Toggles between showing the Disk Grid view and the File Grid view. Note:  When auto-refresh is paused, these viewing options are available.
Show Empty Disks Controls whether disks without any SQL Server database files are displayed.
Order By Activity Sorts physical disks by activity in descending order.
Sum Displays the total IO information in your environment. Note:  When auto-refresh is paused, these viewing options are available.
Average Displays the Average IO information in your environment. Note:  When auto-refresh is paused, these viewing options are available.

Context Menus

Left-click—Sets the focus to any file. Other files for the same database are highlighted along with the corresponding grid row.

Note:  This is applicable to SQL Server, VMware, and Windows targets.

Disk Activity tab left click context menu options

Hover over any graphical icon to display more detailed IO information.

Disk Activity tab SQL Sentry tooltip

Right-click—Run a Quick Trace or a Quick Report.

Note:  Quick Trace is only available for SQL Server targets.

Disk Activity tab right click context menu options

Note:  The following context menu options are available for VMware and Windows targets.

Jump to VM Disk Activity — Opens the Disk Activity tab for the selected SQL Server.

Stop watching— Stops the monitoring of a selected file.

Disk Activity tab VMware and Windows & Hyper-V context menu

Watch— Begins monitoring a selected file.

Disk Activity tab VMware and Windows & Hyper-V context menu

Exporting and Reporting Options

Exporting Data—Export the Disk Activity grid through the File menu (File > Export Data).

Disk Activity tab select File > Export Data to export data

Quick Report—Run a Quick Report from the right-click context menu of any file to access database level I/O metrics in a report format. Run a Quick Report while in Real Time mode for the last 10 minutes of data.

Disk Activity tab Quick Trace context menu

Disk Activity Metrics

The following table lists descriptions of the metrics displayed on the Disk Activity screen. For more information about interpreting latency values, see the Disk I/O: Read latency by physical disk article.

Note:  The sample collection frequency for disk counters is 20 seconds. While in Real Time mode, the Disk Activity screen updates according to this sample interval. A custom sample interval is specified. This may be useful if you're monitoring a server with many database files per database, because this can lead to increased SQL Sentry database growth and transaction log activity.
Name Description
Virtual Machine

The virtual machine that the file is associated with.

Note:  This column appears for targets that are also Hyper-V Hosts, and the column populates for files directly associated with virtual machines on the host (e.g., .vhdx files, mounted .iso files).

Read Bytes

Real Time

The average number of byte reads per second over the last polling interval.

History

Average Mode—The average number of byte reads per second over the selected range.

Sum Mode—The total number of byte reads for the selected range.

% Read Bytes

Real Time

The percentage of byte reads from this file in relation to other files belonging to the target over the last sample polling interval.

History

Average Mode—The average percentage of byte reads from this file in relation to other files belonging to the target over the selected historical range.

Sum Mode—n/a

Reads

Real Time

The number of read operations per second over the last sample polling interval.

History

Average Mode—The average number of read operations per second over the selected range.

Sum Mode—The total number read operations occurring in the selected range.

ms/Read

Real Time

Milliseconds per read. The average time in milliseconds each physical disk read took over the last sample polling interval.

History

Average Mode—The average time in milliseconds each physical disk read took over the selected time range.

Sum Mode—n/a

Write Bytes

Real Time

The average number of bytes written per second over the last polling sample interval.

History

Average Mode—The average number of bytes written per second over the selected range.

Sum Mode—The total number of bytes written for the selected range.

% Write Bytes

Real Time

The percentage of bytes written to this file in relation to other files belonging to the target over the last sample polling interval.

History

Average Mode—The average percentage of bytes written to this file in relation to other files belonging to the target over the selected historical range.

Sum Mode—n/a

Writes

Real Time

The number of write operations per second over the last sample polling interval.

History

Average Mode—The average number of write operations per second over the selected range.

Sum Mode—The total number write operations occurring in the selected range.

ms/Write

Real Time

Milliseconds per write. The average time in milliseconds each physical write took over the last sample polling interval.

History

Average Mode—The average time in milliseconds each physical disk write took over the selected time range.

Sum Mode—n/a

Other Read Bytes

The Other designation is assigned to files that aren't part of the SQL Server installation.

Real Time

The average number of byte reads per second over the selected range, involving files that aren't part of the SQL Server installation.

History

Average Mode—The average number of byte reads per second over the selected range.

Sum Mode—The total number of byte reads for the selected range.

Other Write Bytes

The Other designation is assigned to files that aren't part of the SQL Server installation.

Real Time

The average number of byte writes per second over the selected range, involving files that aren't part of the SQL Server installation.

History

Average Mode—The average number of byte writes per second over the selected range.

Sum Mode—The total number of byte writes for the selected range.

Total VLFs See the Total VLFs topic.

TempDB Example

The Disk Activity analysis feature can also be used to monitor the activity for TempDB. The example below shows TempDB and TempDB Log on their own respective drives and how they appear on the Disk Activity tab.

SQL Sentry Disk Activity Analysis for TempDB

Troubleshooting

Mount Points

If mounted drives are not showing up under Disk Activity or Disk Space, it is likely related to the required ports or DCOM permissions. 

Additional Information: See the following support KB article for more information, Port Requirements for Mount Point Support.