Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2013
    Posts
    3

    Unanswered: Advice on Index Maintenance procedure

    I manage an OLTP database that needs an index maintenance plan designed. The requirements are that it be automated as much as possible, have no impact on batch processing by the ERP application and run at least bi-weekly.

    The biggest challenge I am faced with is the amount of logging generated by index maintenance - in testing, it can fill up the drive containing the transaction logs which are being backed up every 30 minutes.

    The database is using the full recovery model. From the research I have done, these are the options I have come up with:

    1) Increase the size of the log partition
    2) Backup logs more frequently (~15 minutes)
    3) Use a different recovery model on the DB during maintenance

    I am looking for helpful tips or experience to point me in the right direction, especially directions that I haven't considered.

    Thanks in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You're not just re-indexing every index in every table, are you?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2013
    Location
    perth australia
    Posts
    24
    good point blindman. I used the 3 guidelines to determines indexes to rebuild -

    1) index is >30% avg fragmentation
    2) index >1000 pages
    3) look at index usage stats (user seeks/scans); if rarely used no point rebuilding it.

    You could also break-up the index rebuild into multiple maintenance plans instead of doing all in one hit to alleviate the tlog growth.

    Thong

  4. #4
    Join Date
    Apr 2013
    Posts
    3
    Quote Originally Posted by blindman View Post
    You're not just re-indexing every index in every table, are you?
    No, I am using a script which ignores indexes under 1000 pages, reorgs indexes between 30% and 60% fragmentation and rebuilds indexes >60%.

  5. #5
    Join Date
    Apr 2013
    Posts
    3
    Quote Originally Posted by wthong View Post
    good point blindman. I used the 3 guidelines to determines indexes to rebuild -

    1) index is >30% avg fragmentation
    2) index >1000 pages
    3) look at index usage stats (user seeks/scans); if rarely used no point rebuilding it.

    You could also break-up the index rebuild into multiple maintenance plans instead of doing all in one hit to alleviate the tlog growth.

    Thong
    Haven't thought to add #3, makes a lot of sense. Also could split the re-indexing into multiple passes, that would help as well.

    Thanks!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I set up a process that tracks when a table was last reindexed and how long it took to do it.
    I can then allocate a window of reindexing time and my code will reindex as many tables as it can during that time, starting with those that require the most time.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •