Statistics and Index Rebuilds

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719736

    Comments posted to this topic are about the item Statistics and Index Rebuilds

  • HappyGeek

    SSCoach

    Points: 18681

    Good question thanks Steve.

    ...

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    HappyGeek (6/22/2016)


    Good question thanks Steve.

    +1

  • Ed Wagner

    SSC Guru

    Points: 286982

    I thought they had changed it for 2014, but I was wrong. Good question.

  • George Vobr

    SSCrazy Eights

    Points: 9195

    Thanks Steve. It's interesting, but not exactly an easy question. 😉 Here is another link to a clear and concise explanation:

    https://blogs.msdn.microsoft.com/psssql/2015/03/06/does-rebuild-index-update-statistics/

  • sknox

    SSChampion

    Points: 12292

    D'oh! Need to think before I answer.

    OF COURSE it does a full scan. SQL Server already has to do a full scan to rebuild the index. Since that's the expensive part, why not use that work for as much as possible, including updating the statistics for the index?

  • TomThomson

    SSC Guru

    Points: 104773

    Not a very good question, because the answer (at least for SQL Server 2012, SQL server 2014, and SQL Server 2016) depends on whether the index is partitioned or not. Partitioned indexes get statistics calculated on rebuild using the default sampling rate. This is documented clearly in BOL (including, for 2016, on the page referenced in the explanation - and on the v=110 version of that page for 2012).

    Tom

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719736

    TomThomson (6/23/2016)


    Not a very good question, because the answer (at least for SQL Server 2012, SQL server 2014, and SQL Server 2016) depends on whether the index is partitioned or not. Partitioned indexes get statistics calculated on rebuild using the default sampling rate. This is documented clearly in BOL (including, for 2016, on the page referenced in the explanation - and on the v=110 version of that page for 2012).

    I'm torn here. If I write "for a table", I'd assume this is a non-partitioned table. That's what the majority of tables are, and for most installations, partitioning isn't possible. I would always expect this to be a default choice. If the table is partitioned, then I'd mentioned "for a partitioned table".

    I think writing "non-partitioned table" potentially confuses or gives something away. Would every question need to state partitioned or non-partitioned? It seems strange to me to do this, but I can see your point. If there are partitioned indexes, then that's a change.

    I've added that here, but I'm torn on whether we should ask for specific versions, and specific things like non partitioned in descriptors. I'm worried we'll end up with too many specifics that confuse issues.

    I suppose we could just try to ensure each question has DDL, but that seems excessive.

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

    Good Question. I did a lot of research before answering to this question (which I never do mostly). Perhaps, scored 1 mark. Typically, I found that this is the normal behavior of any update stats w.e of Rebuild Index. No special. But I was thinking if there is any relationship with sampling rate or something else most exclusively with this SQL Edition (2014/2016).

    Thanks.

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

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