Updated August 29, 2021.
Purpose
To show the age of the index statistics when they were last captured.
Sources
This feature queries the DMV dm_db_stats_properties which returns the date of the most recent update for statistics on an index plus information for rows sampled, modification counter, …
If the DMV dm_db_incremental_stats_properties is available (SQL2014+) the incremental statistics will also be collected.
Reports
The report page Statistics Age shows 2 tables.
The upper table has one row for each index and how old the statistics are and the number of rows in the tables (using the data collected by the Table size feature).
The lower table shows the statistics for indexes and partitions that are enabled for incremental statistics.
When hovering over the tables a tooltip is showing the statistic age and the number of rows over time.
Model
The model for the report uses the facts [Statistics age], [Statistics age detailed], [Statistics age incremental] and [Table size] and the dimensions [Object], [Index] and [Date].
The fact [Table size] is included in the diagram as the measures [Last Rows] and [Last Rows (SAD)] queries the column ‘Table size'[Rows].
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 once every day between 06:00 and 07:00
Retention
The default retention for the table dbo.fhsmStatisticsAge is 30 days.
