SQL Server Monitoring - Partitioned indexes

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.

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.

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.

Model

The model for the report uses the fact [Partitioned indexes] and the dimensions [Date] and [Index].

Service setup

The feature uses the service ‘fhsmSPCapacity’ which can be configured using the stored procedure fhsmSPControl.

Parameter

The feature is part of the Capacity service, which also serves the features Database size 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

EXEC fhsmSPControl
	@Type = 'Parameter',
	@Command = 'list',
	@Task = 'fhsmSPCapacity',
	@Name = 'Capacity',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Parameter',
	@Command = 'set',
	@Task = 'fhsmSPCapacity',
	@Name = 'Capacity',
	@Parameter = '@Databases = ''USER_DATABASES, msdb, tempdb''';

Schedule

The default schedule for the feature is once every day between 07:00 and 08:00.

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'list',
	@Task = 'fhsmSPCapacity',
	@Name = 'Capacity',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPCapacity',
	@Name = 'Capacity',
	@ScheduleType = 0,
	@Enabled = 1,
	@ExecutionDelaySec = 43200,
	@FromTime = '07:00:00',
	@ToTime = '08:00:00',
	@Monday = 1,
	@Tuesday = 1,
	@Wednesday = 1,
	@Thursday = 1,
	@Friday = 1,
	@Saturday = 1,
	@Sunday = 1;

Retention

The default retention for the table dbo.fhsmPartitionedIndexes is 730 days.

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'list',
	@TableName = 'fhsmPartitionedIndexes',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmPartitionedIndexes',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 730;

SQL Server Monitoring – Partitioned indexes