Updated August 29, 2021.
To show the latest result captured regarding index defragmentation.
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.
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.
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.
The feature also requires the @Mode parameter which specified how indexes are inspected. The valid values for @Mode are LIMITED, SAMLED and DETAILED.
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”
|SYSTEM_DATABASES||All system databases (master, msdb, model, and tempdb)|
|USER_DATABASES||All user 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|
The default schedule for the feature is once every day between 22:00 and 23:00
The default retention for the table dbo.fhsmIndexPhysical is 90 days.