Performance Analysis Disk Space

Applies to: SQL Sentry, BI Sentry*, V Sentry*, and Win Sentry*

Note:  *The Disk Activity and Disk Space tabs are focused on database files. For Win Sentry it applies to VMware and Hyper-V hosts for vDisks and VHDX files, respectively. The tabs will only display for BI Sentry, V Sentry, and Win Sentry when the applicable files are available for monitoring.

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.

SentryOne Disk Space tab

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.

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

SentryOne 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

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

SentryOne Disk Space tab Jump to Indexes

Storage Unit Scale

Use the Storage Unit scale to adjust the relative size of memory 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.

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

MBGBTB
111
555
101010
505050
100100100
500500500


  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.

SentryOne toolbar

Toolbar ButtonDescriptionImage
Disk Grid ViewToggles between File Grid view and Disk Grid view.

Note:  When auto-refresh is paused these viewing options are available
SentryOne Show Disk Grid View toolbar button
File Grid ViewToggles between Disk Grid view and File Grid view.

Note:  When auto-refresh is paused these viewing options are available
SentryOne Show File Grid View toolbar button
Show Empty DisksControls whether disks without any SQL Server database files are displayed.SentryOne Show Empty Disks toolbar button
SumDisplays the total IO information in your environment.

Note:  When auto-refresh is paused these viewing options are available.
SentryOne Sum toolbar button
AverageDisplays the Average IO information in your environment.

Note:  When auto-refresh is paused these viewing options are available.
SentryOne Average toolbar button

Additional Options

  • Select any row to set focus to the associated database and log file.
    SentryOne Disk Space select a row to set focus
  • Right-click to copy the cell/row/all options available.
    SentryOne 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.
    SentryOne 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).

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

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

SentryOne Disk Space tab Reports > Performance Analysis > Disk/File Space

Disk Space Metrics

NameDescription
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 ServerThe file that's associated with the SQL Server.
DatabaseThe file that's associated with the database.
FileThe name of the file.
Filegroup

The filegroup of the associated file.   

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

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

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 sizeVLFs created
< 64 MBFour
>= 64 MB and < One GBEight
>= One GB16

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

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 VLFsThe 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 sizeVLFs created
< 64 MBFour
>= 64 MB and < one GBEight
>= one GB16
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 PathThe full file path of the data or log file.

Disk Forecasting 

Select your desired target from the Navigator pane, and then select the Disk Space tab. Select the desired Logical Drive, and the Disk Forecasting chart appears at the bottom of the screen. On the Disk Space chart a tool tip displays with the Free Space and Forecasted Exhaustion Date.

Note:  The cell under Forecasted Exhaustion will be highlighted orange if the Forecasted Exhaustion date is within 180 days, and it will be red if it is within 90 days.

The Free Space chart displays the following parameters: 

  • Disk Free Space—Amount of available (free) hard drive space on a given disk. 
  • Prediction Interval—An estimated interval that represents the range of values that a future observation will lie within at a specific point in time based on the provided probability. The forecast is generated using a 70% prediction interval, so there is a 70% probability that the actual free disk space on a given date will fall within the forecasted prediction interval.  
  • Forecasted Free Space—Estimated amount of Disk Free Space that will be available for a given disk and date. 

Note:  The forecast displayed is out 180 days, but it can be scheduled for up to 3 years. The length of the Disk Forecasting will impact the storage and accuracy of the data.

Note:  No Disk Space data is generated until 24 hours after a Disk Forecast runs.