Updated August 29, 2021.
Purpose
To get a baseline of the index operations that takes place.
Sources
This feature queries the DMV sys.dm_db_index_operational_stats which returns lower-level I/O, locking, latching, and access method activity for each partition of a table or index.
Reports
This feature has 3 report pages.
#1 shows information about index leaf page operations.
#2 shows information about index nonleaf page operations.
#3 shows information about page and row locks on the indexes.
#1 – Index leaf page operations
The Index Operational #1 report page has a line and stacked column chart, using the stacked columns to show the number of delete, ghost, insert, page merge and update page operations, and the line to show the number of allocations, that has occurred on leaf pages.
The x-axis for the charts is at the day level.
The report has 4 buttons to select the level where the tooltips are showing data. For this feature Database, Schema, Object and Index can be selected.
When hovering over the chart a tooltip is showing detailed insight by showing the top 5 databases, schemas, objects or indexes (depending on the tooltip button selections) with regards of all operations except allocations, and another showing with regards of allocations only.
Database
Schema
Object
Index
#2 Index nonleaf page operations
The Index Operational #2 report page has a line and stacked column chart, using the stacked columns to show the number of delete, insert, page merge and update page operations, and the line to show the number of allocations, that has occurred on nonleaf pages.
The x-axis for the charts is at the day level.
The report has 4 buttons to select the level where the tooltips are showing data. For this feature Database, Schema, Object and Index can be selected.
When hovering over the chart a tooltip is showing detailed insight by showing the top 5 databases, schemas, objects or indexes (depending on the tooltip button selections) with regards of all operations except allocations, and another showing with regards of allocations only.
Database
Schema
Object
Index
#3 Row and Page locks
The Index Operational #3 report page has 2 line charts, one showing the number of row and page locks, and the other the number of mS that the database engine waited on row and page locks.
The x-axis for the charts is at the day level.
The report has 4 buttons to select the level where the tooltips are showing data. For this feature Database, Schema, Object and Index can be selected.
When hovering over the charts tooltips are showing detailed insight by showing the top 5 databases, schemas, objects or indexes (depending on the tooltip button selections) with regards of lock counts, and lock wait in mS.
Lock count tooltip
Database
Schema
Object
Index
Lock wait in mS tooltip
Database
Schema
Object
Index
Model
The model for the reports uses the fact [Index operational] and the dimensions [Date] and [Index].
The fact view [Index operational] in the database contains all values from the DMV, but as good practice dictates it, it is only the values used that are loaded into Power BI 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.