Updated August 29, 2021.
Purpose
To show the latest result captured regarding index defragmentation.
Sources
This feature queries the DMV dm_db_index_physical_stats which returns size and fragmentation information for the data and indexes in the SQL Server.
Reports
The report page Index Physical shows a table with one or more rows for each index.
For indexes inspected using the LIMITED or SAMPLED mode there will be one row for each index, where if inspected using the DETAILED mode there will be one row for each index level of the indexes inspected.
When hovering over the table a tooltip is showing detailed insight at the index level about fragmentation count and average fragmentation in percentage.
Model
The model for the report uses the facts [Index physical] and [Index physical detailed] and the dimension [Index alloc type].
The fact view [Index physical] 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 feature also requires the @Mode parameter which specified how indexes are inspected. The valid values for @Mode are LIMITED, SAMLED and DETAILED.
Be aware that DETAILED will result in really high IO load on your database server as explained in the article Inside sys.dm_db_index_physical_stats by Paul Randal.
Therefore, in order be able to sue the DETAILED mode sensible the feature also has the parameter option @Object, where you can specify to use DETAILED against one table. E.g. “@Databases = ‘FHSMMonitor’; @Object = ‘dbo.CommandLog’; @Mode = DETAILED”.
The default parameter for the feature is “@Databases = ‘USER_DATABASES, msdb’ ; @Mode = LIMITED”
| 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 once every day between 22:00 and 23:00
Retention
The default retention for the table dbo.fhsmIndexPhysical is 90 days.
