SQL Server Monitoring – Table size

This blog is part of the blog series named SQL Server Monitoring.

Purpose

To get a baseline of the size of the tables in the databases.

Sources

This feature queries the DMV sys.dm_db_partition_stats which returns page and row-count information for the objects queried.

Report

The report has in the upper part 2 line charts, the left showing the number of rows in the tables, and the right showing the data size, the index size, the reserved space and the unused space in KB, where the table in the lower part shows the size per database and with the possibility to drill down to schema and object level.

Model

The model for the report uses the fact [Table size] and the dimensions [Date] and [Object].

Configuration

The feature 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'”

ValueDescription
SYSTEM_DATABASESAll system databases (master, msdb, model, and tempdb)
USER_DATABASESAll user databases
ALL_DATABASESAll databases
Db1The database Db1
Db1, Db2The databases Db1 and Db2
USER_DATABASES, -Db1All user databases, except Db1
%Db%All databases that have “Db” in the name
%Db%, -Db1All 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 23:00 and 24:00.

Retention

The default retention for the table dbo.fhsmTableSize is 60 days.