Post reply

Statistics and Index Rebuilds

  • June 22, 2016 at 8:22 pm

    #100930

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

  • June 22, 2016 at 11:39 pm

    #1886419

    Good question thanks Steve.

    ...

  • June 23, 2016 at 1:34 am

    #1886437

    HappyGeek (6/22/2016)

    Good question thanks Steve.

    +1

  • June 23, 2016 at 5:53 am

    #1886491

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

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • June 23, 2016 at 6:49 am

    #1886512

    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/

  • June 23, 2016 at 7:09 am

    #1886519

    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?

  • June 23, 2016 at 12:28 pm

    #1886601

    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

  • June 25, 2016 at 10:37 am

    #1886897

    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.

  • June 27, 2016 at 11:27 am

    #1887104

    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 8 (of 8 total)

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