SQL Server Monitoring – Index Physical

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 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”

SYSTEM_DATABASESAll system databases (master, msdb, model, and tempdb)
USER_DATABASESAll user databases
ALL_DATABASESAll databases
Db1The database Db1
Db1, Db2The databases Db1 and Db2
USER_DATABASES, -Db1All user databases, except Db1
%Db%All databases that have “Db” in the name
%Db%, -Db1All 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.