SQL Sentry Windows 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
|
|
VMware
|
|
Windows
|
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.
Note: A disk appearing red indicates a suspect offset setting. This may affect IO performance.
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.
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.
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 TimeThe average number of byte reads per second over the last polling interval. HistoryAverage 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 TimeThe percentage of byte reads from this file in relation to other files belonging to the target over the last sample polling interval. HistoryAverage 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 TimeThe number of read operations per second over the last sample polling interval. HistoryAverage 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 TimeMilliseconds per read. The average time in milliseconds each physical disk read took over the last sample polling interval. HistoryAverage Mode—The average time in milliseconds each physical disk read took over the selected time range. Sum Mode—n/a |
Write Bytes |
Real TimeThe average number of bytes written per second over the last polling sample interval. HistoryAverage 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 TimeThe percentage of bytes written to this file in relation to other files belonging to the target over the last sample polling interval. HistoryAverage 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 TimeThe number of write operations per second over the last sample polling interval. HistoryAverage 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 TimeMilliseconds per write. The average time in milliseconds each physical write took over the last sample polling interval. HistoryAverage 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 TimeThe average number of byte reads per second over the selected range, involving files that aren't part of the SQL Server installation. HistoryAverage 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 TimeThe average number of byte writes per second over the selected range, involving files that aren't part of the SQL Server installation. HistoryAverage 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.