Post reply

How to determine when a statistic was last used by the optimizer? Cleaning-up Auto-Generated Stats

  • August 25, 2015 at 11:12 am

    #303662

    Is there a DMV or similar in SQL 2012, or SQL 2008, that shows when a statistic was last used by the optimizer? I would like to cleanup some of the auto-generated stats, assuming it's possible to do so. In particular I'm looking to drop those statistics that were created by one-off queries, data loads, etc, and are now doing nothing but adding to the execution time of Update Statistics jobs.

    Thanks, Dave

  • August 25, 2015 at 11:23 am

    #1822280

    The following will return last update date for both table and index statistics.

    SELECT 'TABLE' object_type, object_name(object_id)object_name, name AS stats_name,

    STATS_DATE(object_id, stats_id) AS statistics_update_date

    FROM sys.stats

    UNION ALL

    SELECT 'INDEX' object_type, object_name(object_id)object_name, name AS stats_name,

    STATS_DATE(object_id, index_id) AS statistics_update_date

    FROM sys.indexes;

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • August 25, 2015 at 11:40 am

    #1822287

    Isn't that only showing the date a statistic was last updated, not when it was last used by the optimizer? The update could be from an auto-update or from the "update statistics" command.

  • August 25, 2015 at 12:47 pm

    #1822299

    Correct.

    There's no DMV which records usage, short of running every query with a traceflag on, it's very, very hard to tell that the optimiser found stats interesting.

    You could, if you don't mind a small, short-live performance degradation, delete all auto-created stats and SQL will recreate any which it needs.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 25, 2015 at 1:31 pm

    #1822309

    I'm considering just dropping them and then doing a better job monitoring how quickly they appear. It only became an issue because we consolidated two servers and now Update Statistics is taking over one day to run, with much of the time spent on auto generated stats. The server will be going live in a few weeks.

    Thanks for the suggestions.

    Dave

  • August 25, 2015 at 1:40 pm

    #1822313

    Maybe have your maintenance job leverage sys.dm_db_index_usage_stats to only update statistics on tables and indexes that received heavy updates since the last time the job ran. For example large but relatively static reference or archival table won't need statistics refreshed anywhere near as often as a medium sized transactional table that receives constant inserts and updates.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • August 25, 2015 at 1:51 pm

    #1822314

    Thanks. That's a good idea. I believe there may be a few fairly large static tables in two of the larger databases on the server. I could create a monthly job to hit only those tables. The largest DB is 2TB and is loaded/updated daily by a vendor, so I'll call them to find the static tables.

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

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