Performance Analysis Disk Space
Introduction
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.
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.
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.
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.
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.
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 |
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.
| 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 |
|
| File Grid View | Toggles between Disk Grid view and 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. |
|
Additional Options
- Select any row to set focus to the associated database and log file.
- Right-click to copy the cell/row/all options available.
- Select the + command of any data file to show table and index information. For more information about those metrics, see the Indexes topic.
Exporting and Reporting Options
Exporting Data—The Disk Space grid view is exported through the File menu (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/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 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:
|
||||||||
| 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:
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:
|
||||||||
| 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 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
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.
