This blog is part of the blog series named SQL Server Monitoring.
Purpose
To get a baseline of the size as well as the ranges used for each of the partitioned indexes in the databases.
Sources
This feature queries the DMV’s sys.dm_db_partition_stats, sys.destination_data_spaces, sys.data_spaces, sys.partition_schemes, sys.partition_functions, and sys.partition_range_values to get the necessary information about the partitions queried.
Report
The report has in the upper part a column chart showing the size of the partitioned indexes per database and over time, where the table in the lower part shows details about each partition for the date selected in the upper column chart.
Model
The model for the report uses the fact [Partitioned indexes] and the dimensions [Date] and [Index].
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'”
| 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 23:00 and 24:00.
Retention
The default retention for the table dbo.fhsmPartitionedIndexes is 730 days.
