SQL Sentry Performance Analysis Disk Activity

Introduction

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
VMware
Windows

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
VMware
Windows

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.

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.
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.
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.

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.

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.

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

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.

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.

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

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

Watch— Begins monitoring a selected file.

Exporting and Reporting Options

Exporting Data—Export the Disk Activity grid through the File menu (File > 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 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.

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.

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.