This blog is part of the blog series named SQL Server Monitoring.
Purpose
To get a baseline of the size of disks, database files and filegroups, and use it with regards of capacity planning.
Sources
This feature queries the DMV sys.allocation_units, sys.database_files and sys.dm_os_volume_stats which returns data for data allocation, database files and disks.
Report
The report is divided into 4 parts.
The first part shows 4 line charts, the first is showing used and total size for disk, while the 2 next is showing the allocated size versus the used size for respectively Data and Log files, and the last is showing the number of VLFs (Virtual Log Files) per database.
The second part is a table showing data for disks and with the possibility to drill down to database and database file level.
The third part is a table showing the size per database, and with the possibility to drill down to type (data and log) and logical file level.
The forth part is a table showing data and used space at database, filegroup, schema and object level.
VLFs
VLFs (Virtual Log Files) should be low as it can impact performance regarding backup and restore as well as the SQL engine will taker to startup if the number are excessive.
In the article “How High Virtual Log File (VLF) Count Kills SQL Performance” by Mark Varnas the recommendations are:
- When VLF under 100 – you can ignore.
- When between 100 – 200 – you can ignore, but better to fix.
- When above 400 – it’s getting urgent, so fix it.
- When above 600 – slowdowns are happening, but it’s not easy to diagnose these. Fix.
- When above 5000, fix now.
Model
The model for the report uses the facts [Allocation units], [Database size], [Disk size] and [VLF size], and the dimensions [Date], [Database file], [Disk], [File group] and [Index].
Configuration
The feature is part of the Capacity service, which also includes Partitioned indexes and Table size, and the Capacity service accept the parameter @Databases and the syntax is the same that is used in the SQL Server Index and Statistics Management solution made by Ola Hallengren.
The default parameter for the feature is “@Databases = ‘USER_DATABASES, msdb, tempdb'”
| Value | Description |
|---|---|
| SYSTEM_DATABASES | All system databases (master, msdb, model, and tempdb) |
| USER_DATABASES | All user databases |
| ALL_DATABASES | All databases |
| Db1 | The database Db1 |
| Db1, Db2 | The databases Db1 and Db2 |
| USER_DATABASES, -Db1 | All user databases, except Db1 |
| %Db% | All databases that have “Db” in the name |
| %Db%, -Db1 | All databases that have “Db” in the name, except Db1 |
| ALL_DATABASES, -%Db% | All databases that do not have “Db” in the name |
Scheduling
The default schedule for the feature is once every day between 07:00 and 08:00.
Retention
The default retention for the tables are:
- dbo.fhsmVLFSize – 60 days
- dbo.fhsmAllocationUnits – 30 days
- dbo.fhsmDatabaseSize – 180 days
- dbo.fhsmDiskSize – 180 days
