Post reply

Is there an index recreate/reorg date?

  • August 29, 2014 at 1:13 pm

    #289560

    We are trying to determine when an index was last rebuilt/reorganized. 2008 R2 SP2

    Thanks.

  • August 29, 2014 at 1:25 pm

    #1742016

    I'm not aware of any way of querying that info from the meta data.

    Why is the date important ? Are you running any kind of maintenance solution on your indexes ?

  • August 29, 2014 at 1:34 pm

    #1742020

    Best you can do is check stats date update.

    http://qa.sqlservercentral.com/Forums/Topic1115156-391-1.aspx

    However, I'd agree with PearlJammer1, the date isn't important. What's important is are the stats up to date in terms of your data distribution/skew, and what is the fragmentation. No need to build indexes if these are OK. No need to wait to rebuild indexes if they aren't.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 29, 2014 at 1:50 pm

    #1742023

    We have been having issues with our nightly Index rebuild/reorg job. It has been timing out due to various reasons so not all indexes have been looked at. So now we have a lot of indexes at the 80%-90% fragmentation stage. It would be nice to know how long it took to get to that high fragmentation. Maybe we could schedule a little better in the nightly window that we have been given.

  • August 29, 2014 at 2:24 pm

    #1742027

    check out ola hallengrens index maintenance solution. https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    You can target only indexes that are a certain level of fragmentation - this is better than using the standard maintenance plans which rebuild every single index regardless of its level of fragmentation. You could set the script to start only doing those indexes that are over say 80% - the first run may take longer, but as your indexes become less fragmented you can lower the number - say to 70%, then 50% etc etc.

    If the indexes are getting highly fragmented daily you may wish to start leaving some empty space in the index by using the WITH FILLFACTOR = xx if you decide on re-creating them.

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

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