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

Comments

  • Anonymous
    December 15, 2014
    Great article, check this great guide on identifying manually update-able stats: sqlturbo.com/finding-good-stats-candidates-for-manual-updating

  • Anonymous
    April 01, 2015
    Interesting aspect. I have a few comments: The script shows only index statistics. The columns not belonging to an index as well the columns statistics for views are not shown (see sys.stats & sys.stats_columns vs. sys.index_columns). I was wondering how reliable is the updated “percentage”, especially when dealing with repetitive batch updates that modify multiple times the same record. If I understood the definition of rowmodctr correctly, the value is incremented each time an update (insert, update, delete) occurs on the respective index.  This means that in some cases following will hold: UpdatedRowCount >TableRowCount. Colmodctr, the column modifiered Counter, stored for each column would be a more appropriate value for such an analysis, allowing to explore also the columns not used in indexes. Unfortunately its values are not available to users; they are only available to the query processor. “Percentage” is a misleading name as long it looks like a simple ratio. The above query can be considerable simplified with the help of a CROSS APPLY: select OBJECT_NAME(id) as TableName , s.name as StatsName , s.auto_created , i.rowmodctr as UpdatedRowCount , ps.TableRowCount , case ps.TableRowCount when 0 then 0 else i.rowmodctr*1./ps.TableRowCount end as UpdatedPercentage , 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     cross apply (     select SUM(row_count) TableRowCount     from sys.dm_db_partition_stats     where object_id=i.id and (index_id=0 or index_id=1)    ) ps order by UpdatedPercentage desc