Documentation forSQL Sentry

Performance Analysis Disk Space

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 targets it applies to VMware and Hyper-V hosts for vDisks and VHDX files, respectively. The tabs will only display for SSAS, VMware, and Windows targets 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.

The Disk Space tab contains information about disk space utilization within your environment, broken down by database and file. Use the Disk Space tab to identify disk capacity issues, understand where various database files reside on the disk system, and to determine whether available disk space is optimally used.

The Disk Space tab has two distinct areas. The top pane is a graphical representation of the database layout at the disk level. Data files are represented with a unique solid color, and each associated log file is represented with the same color with a line overlay. The solid grey areas represent the empty space per disk. The colors are repeated for each file in the Grid View found in the bottom pane. Selecting a file in either pane highlights the associated representation in the other pane.

Disk Space tab

Using Disk Space

Disk Space Modes

The Disk Space tab has two modes, Real Time and History. The active mode is controlled by the Auto-Refresh toolbar button. Select the auto-refresh Play button to enter the Real Time mode. Data for the last collected sample is shown.

Disk Space tab Real-time toolbar

Select the auto-refresh Pause button to enter the History mode. History mode displays the average disk space information over the specified time range. Change the time range you're viewing by using the Start and End times and selecting Go in the toolbar.

Disk Space tab History mode toolbar

Database Space Usage Band

When index collection is enabled, index information is viewable by drilling into the database level in the Grid View. When a database is selected in the grid, a band displaying the space usage of that database's indexes appears between the disk layout and the grid. Select a database in the Grid View to open it's corresponding Database Space Usage Band

Disk Space tab Disk Space Usage Band

Note:  Each box represents an index within the selected database. The size of each box illustrates the total amount of space used in relation to the other indexes within that database. Right click any index in the Database Space Usage Band to select the Jump to Indexes context menu option.

Disk Space tab Jump to Indexes

Storage Unit Scale

Use the Storage Unit scale to adjust the relative size of disk space in MB/GB and the corresponding graph that displays in the Disk Space tab. Adjusting the scale to a different value changes the corresponding graph to match that value.

Disk Space Memory Scale Display Disk Space data in the following increments: 

MB GB TB
1 1 1
5 5 5
10 10 10
50 50 50
100 100 100
500 500 500
  Note:  The default display for the Disk Space tab is 10GB.

Toolbar

Once the Disk Space tab is in History mode the Disk/Database Grid View button becomes available in the toolbar. Selecting File Grid View removes the groupings, and can be helpful when you're interested in viewing and sorting data across the entire disk subsystem.

SQL Sentry toolbar

Toolbar Button Description Image
Disk Grid View Toggles between File Grid view and Disk Grid view. Note:  When auto-refresh is paused these viewing options are available Show Disk Grid View toolbar button
File Grid View Toggles between Disk Grid view and File Grid view. Note:  When auto-refresh is paused these viewing options are available Show File Grid View toolbar button
Show Empty Disks Controls whether disks without any SQL Server database files are displayed. Show Empty Disks toolbar button

Additional Options

  • Select any row to set focus to the associated database and log file. Disk Space select a row to set focus
  • Right-click to copy the cell/row/all options available. Disk Space tab Copy context menu option
  • Select the + command of any data file to show table and index information. For more information about those metrics, see the Indexes topic. Disk Space tab expansion button

Exporting and Reporting Options

Exporting Data—The Disk Space grid view is exported through the File menu (File > Export Data).

Disk Space tab File > Export Data

Quick Report—Run a Quick Report to access database level size metrics in report format from the right-click context menu of any file. Run a Quick Report in Real Time mode for the last seven days of data.

Disk Space tab Quick Report

Disk/File Space Reports—The Disk/File Space reports provide disk and database file level metrics. Access them through the Reports menu (Reports > Performance Analysis > Disk/File Space). Disk Space tab Reports > Performance Analysis > Disk/File Space

Disk Space Metrics

Name Description
Virtual Machine

The virtual machine that the file's associated with.

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

SQL Server The file that's associated with the SQL Server.
Database The file that's associated with the database.
File The name of the file.
Filegroup

The filegroup of the associated file.   

Additional Information: For more information about files and filegroups within the SQL Server, see the Files and Filegroups Architecture article.

Type Specifies the type of the file being either data or transaction log.
Size (MB) The size in (MB) of the selected file.
Used (MB)

The amount of the disk space that's allocated for the file and is in use.

Additional Information: For more information about page types and data files, see the Understanding Pages and Extents topic.

Used % The percentage of disk space that's allocated for the file and is in use.
Auto-growth

Indicates the auto-growth properties of the selected file. If the setting is defined as a percentage, that percentage displays along with the estimated size in megabytes of the next file growth. If file growth is defined as an absolute value, that size (MB) displays.

Warning:  

  • A pink highlight indicates that the auto-growth amount is greater than the remaining free space.
  • A yellow highlight indicates that auto-growth is set as a percent.
  • A tan highlight indicates that auto-growth is less than 10 MB or greater than one GB.
Max File Size (MB) The maximum file size allowed for this data or log file.
Total VLFs

The total number of virtual log files (VLFs) that compose the transaction log. Each physical transaction log is made up of a several smaller VLFs. When the transaction log grows, by a manual action, or because of an auto-growth, the number of virtual log files that compose the transaction log increase.

The actual number of corresponding VLFs that are added during any log growth is dependent upon the size of the growth. See the following table:

Auto-growth size VLFs created
< 64 MB Four
>= 64 MB and < One GB Eight
>= One GB 16

If the transaction log grows frequently in small increments, you may see many VLFs. A log file containing many VLFs decreases database performance.

Additional Information: For more information about transaction log architecture, see the Transaction Log Physical Architecture topic. 

Warning:  

  • A tan highlight indicates that Total VLFs are greater than 100.
  • A pink highlight indicates that Total VLFs are greater than 300.
Active VLFs The number of virtual log files (VLFs) containing log records that are still needed. There are several criteria for defining when a log record is needed. For more information, see the TechNet topic. A log record that's still needed is defined as an active log record, and any VLF containing at least one active log record is an active VLF.
Min VLF Size (MB) The size (MB) of the smallest existing VLF.
Max VLF Size (MB) The size (MB) of the largest existing VLF.
Avg VLF Size (MB) The average size (MB) of those VLFs that compose the log.
Auto-growth VLF count

The number of VLFs that's created during the next auto-growth event. When the transaction log grows, by either a manual action, or because of an auto-growth, the number of virtual log files that compose the transaction log also increases. This number is dependent upon the size of the growth. See the following table:

Auto-growth size VLFs created
< 64 MB Four
>= 64 MB and < one GB Eight
>= one GB 16
Auto-growth VLF Size (MB)

The size (MB) of each VLF that's created during the next auto-growth event, given the current auto-growth settings.

Last Backup Time

The last recorded backup time for the associated file.

Warning:  A pink highlight indicates that there are no recent transaction log backups.

Last Backup Type

The last recorded backup type for the associated file.

Log Reuse Wait

This indicates what the transaction log is waiting on, in regards to re-using its space.

File Path The full file path of the data or log file.

Disk Forecasting

See the Forecasting article for a complete guide to the storage forecasting feature.

TempDB Example

The Disk Space analysis feature can also be used to monitor the number of files and size of TempDB. The example below shows TempDB and TempDB Log on their own respective drives and how they appear on the Disk Space tab.

SQL Sentry Disk Space Analysis for TempDB