This blog is part of the blog series named SQL Server Monitoring.
Purpose
To get a baseline of the index locking that takes place.
Sources
This feature queries the DMV sys.dm_db_index_operational_stats which returns information about locking and latching for each partition of a table or index.
Report
The report has 2 line charts in the upper half showing the number of locks and the time of lock wait in Ms over time, where the table in the lower half shows the lock data per database and with the possibility to drill down to schema, object and index level.
Model
The model for the reports uses the fact [Index operational] and the dimensions [Date], [Time] and [Index].
It is the same model that are used by the Index leaf 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.
