This blog is part of the blog series named SQL Server Monitoring.
Purpose
To show the age of the index statistics when they were last captured.
Sources
This feature queries the DMV sys.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 sys.dm_db_incremental_stats_properties is available (SQL2014+) the incremental statistics will also be collected.
Report
The report has 2 tables where 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), and the lower table shows the statistics for indexes and partitions that are enabled for incremental statistics.
Model
The model for the report uses the facts [Statistics age], [Statistics age incremental] and [Table size] and the dimensions [Index], [Index partition], [Object] and [Object partition].
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.
