Post reply

DB Maintenance

  • May 21, 2018 at 2:06 pm

    #350095

    Hi Guys,

    Part of the Database Maintenance routines (REbuild Index and Update Stats)...which is the best sequence to maintain? I always prefer doing a REbuild / Reorg Index based on the fragmentation level of indexes and followed by an Update Stats. I have seen many people prefer doing the reverse.

    Wanted to seek your understanding and opinion on this.

    Thanks.

  • May 21, 2018 at 3:21 pm

    #1991352

    SQL-DBA-01 - Monday, May 21, 2018 2:06 PM

    Hi Guys,

    Part of the Database Maintenance routines (REbuild Index and Update Stats)...which is the best sequence to maintain? I always prefer doing a REbuild / Reorg Index based on the fragmentation level of indexes and followed by an Update Stats. I have seen many people prefer doing the reverse.

    Wanted to seek your understanding and opinion on this.

    I think the reason you see some of the reverse is because when you rebuild an index, the stats for that index are updated. That doesn't necessarily mean you are doing it in the wrong order though as column stats aren't updated when the index is rebuilt.
    And then there are debates around index maintenance and fragmentation. Some of it depends on how the tables are used, what you are using for fill factors, what the page fullness is, etc. Personally I don't believe there is any one correct answer around index fragmentation that is applicable for all databases. I do think the practice of rebuild when fragmentation is over 30% and reorg when it is under 30% is a bit outdated and again really depends. From a lot of testing, I'm personally not real sure index reorgs do much other than use a lot of resources. And all of that is probably too much to say. But I do think much of it "depends"
    However, understanding the relationship between index maintenance and statistics is important so that you aren't wasting maintenance time, resources and such. Here are a few links with some good information that address exactly what you are wondering about:
    Search Engine Q&A #10: Rebuilding Indexes and Updating Statistics
    Rebuilding Indexes vs. Updating Statistics

    And  it won't let me paste the other link - only deletes one of the others if I do. So: https://blogs.msdn.microsoft.com/psssql/2015/03/06/does-rebuild-index-update-statistics/
    That last link has a quick lists of what stats are updated with what index maintenance.

    Sue

  • May 22, 2018 at 6:48 am

    #1991395

    Sue_H - Monday, May 21, 2018 3:21 PM

    SQL-DBA-01 - Monday, May 21, 2018 2:06 PM

    Hi Guys,

    Part of the Database Maintenance routines (REbuild Index and Update Stats)...which is the best sequence to maintain? I always prefer doing a REbuild / Reorg Index based on the fragmentation level of indexes and followed by an Update Stats. I have seen many people prefer doing the reverse.

    Wanted to seek your understanding and opinion on this.

    I had gone through your response. Thanks for that. But it is not mentioned whether you do a Rebuild Index followed by an Update Stats or Vice Versa.
    Essentially when you have 1000+ databases with many many SQL Instances, it is not possible that one maintain different set of maintenance jobs in all different servers, we need to go by a best practice. Was looking for how other DBAs proactively manage their Index Maintenance / update stats solutions!!

    I think the reason you see some of the reverse is because when you rebuild an index, the stats for that index are updated. That doesn't necessarily mean you are doing it in the wrong order though as column stats aren't updated when the index is rebuilt.
    And then there are debates around index maintenance and fragmentation. Some of it depends on how the tables are used, what you are using for fill factors, what the page fullness is, etc. Personally I don't believe there is any one correct answer around index fragmentation that is applicable for all databases. I do think the practice of rebuild when fragmentation is over 30% and reorg when it is under 30% is a bit outdated and again really depends. From a lot of testing, I'm personally not real sure index reorgs do much other than use a lot of resources. And all of that is probably too much to say. But I do think much of it "depends"
    However, understanding the relationship between index maintenance and statistics is important so that you aren't wasting maintenance time, resources and such. Here are a few links with some good information that address exactly what you are wondering about:
    Search Engine Q&A #10: Rebuilding Indexes and Updating Statistics
    Rebuilding Indexes vs. Updating Statistics

    And  it won't let me paste the other link - only deletes one of the others if I do. So: https://blogs.msdn.microsoft.com/psssql/2015/03/06/does-rebuild-index-update-statistics/
    That last link has a quick lists of what stats are updated with what index maintenance.

    Sue

    Thanks.

  • May 30, 2018 at 6:04 am

    This was removed by the editor as SPAM

  • May 30, 2018 at 12:16 pm

    #1992370

    Lj Burrows - Wednesday, May 30, 2018 6:04 AM

    There are few major difference between Rebuilding and Reorganizing indexes, and Its totally your choice to choose any one. If you have light fragmented indexes, then it's better to reorganize Indexes and if you have heavily fragmented indexes, then it's better to rebuild indexes to save time and resources. Go through the following link to understand better:
    https://www.sqlskills.com/blogs/paul/sqlskills-sql101-rebuild-vs-reorganize/

    The question was not about when to do REBUILD vs REORG.

    The question was, whether it is preferred to do a Update Stats and then doing a REBUILD/REORG or the opposite?

    Thanks.

  • May 30, 2018 at 2:13 pm

    #1992393

    SQL-DBA-01 - Wednesday, May 30, 2018 12:16 PM

    The question was not about when to do REBUILD vs REORG.

    The question was, whether it is preferred to do a Update Stats and then doing a REBUILD/REORG or the opposite?

    If you do a rebuild, then you probably do not want to update statistics afterwards since they are updated with the rebuild.  Updating statistics should probably be based on the number of modifications, and happen more frequently than index maintenance.
    SQL Server's default behavior (at least before 2016 version) would not auto-update statistics frequently enough:
    https://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/
    There is a trace flag setting that can help:
    http://www.sqlservercentral.com/blogs/steve_jones/2012/10/22/trace-flag-2371-and-statistics/
    You can use sys.dm_db_stats_properties to find how many changes were made to data since the last refresh:
    https://www.sqlskills.com/blogs/erin/new-statistics-dmf-in-sql-server-2008r2-sp2/

  • June 3, 2018 at 10:57 am

    #1992763

    SQL-DBA-01 - Wednesday, May 30, 2018 12:16 PM

    The question was not about when to do REBUILD vs REORG.

    Oddly enough, it should be.  Most people that follow supposed "Best Practice" recommendations are perpetuating page splits which perpetuates fragmentation which perpetuates the need for frequent index maintenance.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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