This blog is part of the blog series named SQL Server Monitoring.
Purpose
To get a baseline of the index nonleaf and leaf operations that takes place.
Sources
This feature queries the DMV sys.dm_db_index_operational_stats which returns information about lower-level I/O and access method activity for each partition of a table or index.
Report
The report is divided into 3 parts.
The upper part are showing 2 line charts which shows index operations over time, where the left line chart shows nonleaf data and the right line chart shows leaf data.
The center and lower parts shows 2 tables, where the center table shows nonleaf data, and the lower table show leaf data. The data are shown per database, and with the possibility to drill down to schema, object and index level.
Model
The model for the report uses the fact [Index operational] and the dimensions [Date], [Time] and [Index].
It is the same model that are used by the Lock statistics report.
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 every 4 hours (14.400 sec.) between 06:00 and 24:00.
Retention
The default retention for the tables dbo.fhsmIndexOperational and dbo.fhsmIndexOperationalReport are 90 days.
