Post reply

Duplicate Statistics

  • September 25, 2017 at 10:49 am

    #349349

    Hi All,

    I've been going through my production DBs and, while I don't have any duplicate indexes, I have noticed lots of duplicate statistics. That is to say there is a statistic created (auto-created stat) on a given column in a table and an index on that column also exists.

    I suspect that this scenario manifests itself when there is no index on a given column and SQL Server needs stats on the column and auto-creates them. Some time later an index is created on that same column. SQL Server doesn't then remove the now duplicate, auto-created statistics.

    I'm thinking that these sort of duplicate statistics could be dropped since stats exist as part of the index. Is this correct?

    Thanks,

    Peter

  • September 25, 2017 at 10:59 am

    #1960810

    If you're adding an index to a table, regen the statistics anyways and if they are unneeded, (and they aren't created manually by you) they should go away I think.

  • September 25, 2017 at 11:35 am

    #1960814

    AZ Pete - Monday, September 25, 2017 10:49 AM

    Hi All,

    I've been going through my production DBs and, while I don't have any duplicate indexes, I have noticed lots of duplicate statistics. That is to say there is a statistic created (auto-created stat) on a given column in a table and an index on that column also exists.

    I suspect that this scenario manifests itself when there is no index on a given column and SQL Server needs stats on the column and auto-creates them. Some time later an index is created on that same column. SQL Server doesn't then remove the now duplicate, auto-created statistics.

    I'm thinking that these sort of duplicate statistics could be dropped since stats exist as part of the index. Is this correct?

    Thanks,

    Peter

    I believe you are correct on both. The common scenario for the duplicates is auto creation of a statistic and then an index is created afterwards.
    You would want to delete the statistic - the duplicates could potentially lead to a sub-optimal query plan.

    Sue

  • September 25, 2017 at 12:08 pm

    #1960826

    I thought that the duplicate stats do not automatically go away?  Here's an article that suggests even automatically created stats will stay when they are duplicated by an index's stats later:
    https://mattsql.wordpress.com/2013/08/28/duplicate-statistics/
    You probably should remove them manually if you find them.

Viewing 4 posts - 1 through 3 (of 3 total)

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