How to Find Unused Statistics

  • Article

As you know, statistics are used for row estimation during query plan generation phase. So they are really important for query performance. If the “Auto Create Statistics” database option is “On”, for every column in the where clause or join, statistics are automatically created by the database engine.

These auto created statistics might not be used in the following days. So, how can we find these unused statistics?

As you know, if “Auto Update Statistics” database option is set to “On”, statistics are automatically updated if %20+500 rows changed threshold is reached and related statistics are used. So, the first rule for automatic update operation is %20+500 row changes threshold (the thresholds are tracked in sysindexes.rowmodctr column) and the second rule is that the related statistics must have been used in order to be updated.

The script below searches all statistics and finds the number of changes and last update time for all statistics. If the number of changes is higher than the threshold but the statistic is not updated, that means this statistic is not used since last update time (or Auto Update Statistics option is set to OFF).

select TableName, StatsName, auto_created, UpdatedRowCount, TableRowCount

      , case TableRowCount when 0 then 0 else UpdatedRowCount*1./TableRowCount end as UpdatedPercentage

      , StatsLastUpdatedTime

from(

select OBJECT_NAME(id) as TableName

      ,s.name as StatsName

      ,s.auto_created

      ,rowmodctr as UpdatedRowCount

      ,(select SUM(row_count) from sys.dm_db_partition_stats where object_id=i.id and (index_id=0 or index_id=1)) as TableRowCount

      ,STATS_DATE(i.id,i.indid) as StatsLastUpdatedTime

from sysindexes i

left join sys.stats s on s.object_id=i.id and s.stats_id=i.indid

)xx

order by (case TableRowCount when 0 then 0 else UpdatedRowCount*1./TableRowCount end) desc

image