SQL Server Monitoring - Index usage

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

Purpose

To get a baseline of the index usage and to see how the usage changes when indexes are added or modified.

Report

The report are split in 3 parts.

In the upper part a line chart showing the number of seeks, updates, lookups and scans over time.

In the middle part a table shows the index usage per database and with the possibility to drill down to schema, object and index level.

In the lower part a table are showing the indexes that has not been used at all when looking at the collected data.

Sources

This feature queries the DMV sys.dm_index_usage_stats which returns counts of different types of index operations and the time each type of operation was last performed.

Model

The model for the report uses the facts [Index usage], [Index not used] and [Table size] and the dimensions [Date], [Time], [Index] and [Object].

Service setup

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

Parameter

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

EXEC fhsmSPControl
	@Type = 'Parameter',
	@Command = 'list',
	@Task = 'fhsmSPIndexUsage',
	@Name = 'Index usage',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Parameter',
	@Command = 'set',
	@Task = 'fhsmSPIndexUsage',
	@Name = 'Index usage',
	@Parameter = '@Databases = ''USER_DATABASES, msdb''';

Schedule

The default schedule for the feature is every 4 hours between 06:00 and 24:00

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'list',
	@Task = 'fhsmSPIndexUsage',
	@Name = 'Index usage',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPIndexUsage',
	@Name = 'Index usage',
	@ScheduleType = 0,
	@Enabled = 1,
	@ExecutionDelaySec = 14400,
	@FromTime = '06:00:00',
	@ToTime = '23:59:59',
	@Monday = 1,
	@Tuesday = 1,
	@Wednesday = 1,
	@Thursday = 1,
	@Friday = 1,
	@Saturday = 1,
	@Sunday = 1;

Retention

The default retention for the table dbo.fhsmIndexUsage is 90 days.

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

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

SQL Server Monitoring – Index usage