Post reply

last time index was re-org or rebuilt?

  • September 5, 2022 at 1:17 pm

    #4084042

    i'm unable to find such script online.  i found some that had last stats updated but it's not the same thing i'm looking for.

    any ideas/suggestions?  thx

  • September 5, 2022 at 4:22 pm

    #4084092

    Unless you have a system, such as Ola Hallengren's index maintenance solution, there's nothing in SQL Server that logs this information.  And, no... even though index REBUILDs update stats, I wouldn't use those dates for this because stats should be updated more than you're doing rebuilds and they can also occur spontaneously when the system thinks they need an update.

    I also would stop using REORGANIZE until you've done a bit of a study on what it actually does and how it can and frequently does perpetuate fragmentation, not to mention how it blows out the log file.

    Also, if you're rebuilding indexes that have a "0" Fill Factor, you are causing some massive blocking issues on the proverbial "morning after".  Fragmentation is caused by page splits which is caused by out-of-order inserts and "ExpAnsive" updates.  Those need some room to occur without page splits.  Rebuilding indexes to "0", which is the same as a 100% Fill Factor in all other aspects, removes all such space and guarantees page-splits when out-of-order inserts and "ExpAnsive" updates occur.

    There's a whole lot more to it but let me summarize by saying that it's better to do no index maintenance than it is to do it wrong.  If you're using REORGANIZE in general or REBUILDing indexes with the default Fill Factor of "0", you're doing it wrong.  What people have been citing as "Best Practice" Index Maintenance is not only wrong, but it's harmful.  They're so bad that they're predominately  responsible for what people perceive to a fragmentation problem with Random GUIDs.

    Spend a very worthwhile 90 minutes to watch the following 'tube.  Although I focus on Random GUIDs, it's only because they're the "poster child" for fragmentation problems.  There's also a ton of other information about how those nice, ever-increasing-keyed indexes are fragmenting and why it's actually stupid to lower the Fill Factor on those.  It also destroys the myth of GUID fragmentation and lays waste to what people have mistakenly adopted as "Best Practice" Index Maintenance.

    https://www.youtube.com/watch?v=rvZwMNJxqVo

    Trust me... this isn't just about GUIDs and I can just about guarantee you've not seen a presentation on index maintenance quite like this one.

    --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)

  • September 5, 2022 at 8:37 pm

    #4084148

    This isn't tracked, nor is it something I think you should care about. If the index is not performing, and you need to update, most of the time this is a stats update needed. If you have fragmentation, then perhaps you want to rebuild an index, but you ought to look at these:

    If you have a maintenance job, then looking at this history and logging tells you something.

  • September 5, 2022 at 9:24 pm

    #4084151

    Knowing when the last index maintenance is nice to know... In conjunction with the two types of fragmentation, it helps alert you to which indexes might need some help in the form of fixing things like "ExpAnsive" updates and maybe even what "fragmentation type" of index you may be dealing with.

    Also, if you think that logical fragmentation doesn't matter at all, do a test.  Build a new test database.  Build a large clustered index on it.  Rebuild it, which will move it to all new pages on the file.  Then run a large, multi-row test (against a lot of rows)  Follow that with a DBCC SHRINK on the database, which will invert the index, and then run the same large multi-row performance test you did before.  You'll be amazed, even on SSDs.

    Even without such extreme fragmentation, large multi-row queries can have a problem even when there are just a couple of points of fragmentation cause by "interleaving of extents".  Yeah... I did that experiment, as well.  I wish they had left the actual data that they had in the old DBCC ShowContig tool because it can see that type of fragmentation, as well.

    --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 4 posts - 1 through 3 (of 3 total)

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