Statistics Outdated

  • Comments posted to this topic are about the item Statistics Outdated

  • Just wondering what these stats would be used for.
    Cos if stats are outdated then that implies they are not being used, since queries would start the process of autorecomputing stats, if it is enabled, and  the stats are used in that query.
    So this could be a good script in finding stats that need to be dropped.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • If statistics are out of date, that does NOT mean that they aren't being used, that typically means that the table they are on is large enough that SQL Server's built in rule about only autoupdating statistics after 20% modifications doesn't kick in frequently enough.  If a table has 10,000,000 rows in it, autoupdate of statistics won't happen until after 2,000,000 rows have been inserted/updated.
    https://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/

    There were some changes to this behavior introduced in SQL Server 2016:
    https://www.brentozar.com/archive/2016/03/changes-to-auto-update-stats-thresholds-in-sql-server-2016/

  • I think this is only applicable to SQL Server 2008 R2 and above.
    sys.dm_db_stats_properties
    is not on my SQL Server 2008 instances

  • for older versions, you'd probably have to look at sys.sysindexes and the STATS_DATE function to see out of date stats, maybe something like this:
    SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS TableSchema, OBJECT_NAME(i.OBJECT_ID) AS TableName, o.type_desc AS ObjectType, i.[name] AS StatisticsName, STATS_DATE(i.OBJECT_ID, i.index_id) AS StatisticsUpdateDate, si.rowmodctr AS RecordsModified, si.rowcnt AS TotalRecords, i.has_filter
    FROM sys.indexes i
      INNER JOIN sys.objects o ON i.OBJECT_ID=o.OBJECT_ID
      INNER JOIN sys.sysindexes si ON i.OBJECT_ID=si.id AND i.index_id=si.indid
    WHERE o.TYPE <> 'S'
      AND STATS_DATE(i.OBJECT_ID, i.index_id) IS NOT NULL
      AND si.rowmodctr > 0
    UNION ALL
    SELECT OBJECT_SCHEMA_NAME(o.OBJECT_ID) AS TableSchema, OBJECT_NAME(o.OBJECT_ID) AS TableName, o.type_desc AS ObjectType, s.name AS StatisticsName, STATS_DATE(o.OBJECT_ID, s.stats_id) AS statisticsUpdateDate, si.rowmodctr AS RecordsModified, ir.rowcnt AS TotalRecords, s.has_filter
    FROM sys.stats s
      INNER JOIN sys.objects o ON s.OBJECT_ID=o.OBJECT_ID
      INNER JOIN sys.sysindexes si ON s.OBJECT_ID=si.id AND s.stats_id= si.indid
      INNER JOIN (SELECT id,rowcnt FROM sys.sysindexes WHERE indid IN (0,1)) IR ON IR.id=o.OBJECT_ID
    WHERE o.TYPE <> 'S'
      AND (s.auto_created=1 OR s.user_created=1)AND STATS_DATE(o.OBJECT_ID, s.stats_id) IS NOT NULL
      AND si.rowmodctr > 0
    ORDER BY RecordsModified DESC

  • Chris Harshman - Friday, June 8, 2018 10:15 AM

    for older versions, you'd probably have to look at sys.sysindexes and the STATS_DATE function to see out of date stats, maybe something like this:
    SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS TableSchema, OBJECT_NAME(i.OBJECT_ID) AS TableName, o.type_desc AS ObjectType, i.[name] AS StatisticsName, STATS_DATE(i.OBJECT_ID, i.index_id) AS StatisticsUpdateDate, si.rowmodctr AS RecordsModified, si.rowcnt AS TotalRecords, i.has_filter
    FROM sys.indexes i
      INNER JOIN sys.objects o ON i.OBJECT_ID=o.OBJECT_ID
      INNER JOIN sys.sysindexes si ON i.OBJECT_ID=si.id AND i.index_id=si.indid
    WHERE o.TYPE <> 'S'
      AND STATS_DATE(i.OBJECT_ID, i.index_id) IS NOT NULL
      AND si.rowmodctr > 0
    UNION ALL
    SELECT OBJECT_SCHEMA_NAME(o.OBJECT_ID) AS TableSchema, OBJECT_NAME(o.OBJECT_ID) AS TableName, o.type_desc AS ObjectType, s.name AS StatisticsName, STATS_DATE(o.OBJECT_ID, s.stats_id) AS statisticsUpdateDate, si.rowmodctr AS RecordsModified, ir.rowcnt AS TotalRecords, s.has_filter
    FROM sys.stats s
      INNER JOIN sys.objects o ON s.OBJECT_ID=o.OBJECT_ID
      INNER JOIN sys.sysindexes si ON s.OBJECT_ID=si.id AND s.stats_id= si.indid
      INNER JOIN (SELECT id,rowcnt FROM sys.sysindexes WHERE indid IN (0,1)) IR ON IR.id=o.OBJECT_ID
    WHERE o.TYPE <> 'S'
      AND (s.auto_created=1 OR s.user_created=1)AND STATS_DATE(o.OBJECT_ID, s.stats_id) IS NOT NULL
      AND si.rowmodctr > 0
    ORDER BY RecordsModified DESC

    Thanks very much for the update. This works fine on my 2008 instance.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply