Getting Started with Adobe After Effects - Part 6: Motion Blur
Ask
Ask questions, discuss or help others by answering
Related Posts · View All
SQL Server 145
TSQL 76
SSRS 70
SSIS 67
XML 57

Top Categories · View All
SQL Server 145
TSQL 76
SSRS 70
SSIS 67
XML 57

Need your suggestion / Feedback regarding - automation of database rebuild / reorganize indexes

Dec 10 2012 12:00AM by Krishnrajsinh Rana   

Hello Experts,

I need your suggestion / feedback regarding automation of database rebuild / reorganize indexes.
For the frequently updated database, should i have to manually rebuild / reorganize the indexes as per the Total fragmentation or i have to make it automated ?

Plz put your valuable feedback.

Submitted under: Microsoft SQL Server · DBA ·  ·  · 


Krishnrajsinh Rana
137 · 1% · 362

7 Replies

  • I see no problem in automating such a process. In fact some of our applications do have weekly automated defrag jobs. It is also important to manually review the tables that builds a lot of fragmentation and there are chances that you fix the excessive fragmentation by tweaking the schema.

    I would generate a log as well as report for any automated activity so that I can keep a close watch on what exactly happening.

    commented on Dec 10 2012 12:20PM
    Jacob Sebastian
    1 · 100% · 32235
  • Hi Jacob,

    Thanks for the reply, but i have one doubts as you said -

    there are chances that you fix the excessive fragmentation by tweaking the schema

    I don't understand, i mean what do you mean by "tweaking the schema"?

    Thanks

    commented on Dec 11 2012 12:38AM
    Krishnrajsinh Rana
    137 · 1% · 362
  • Assume that you have a table with a clustered index on a UNIQUEIDENTIFIER column. Now, if the inserts are not generating a sequential GUID, the fragmentation will be too high on this table. So to fix that one might want to generate a sequential GUID (if the application/business logic permits) or decide whether the clustered index on the GUID column is really needed or not. Sometimes we may realize that the clustered index (which is currently too much fragmented) may be rebuilt with a different key (if application/business logic permits) which will result in less fragmentation.

    Sorry for using a confusing sentence, but the above is what I intended to say.

    commented on Dec 11 2012 3:14AM
    Jacob Sebastian
    1 · 100% · 32235
  • OK Got it...

    But i m always try to avoid the use of UNIQUEIDENTIFIER column in the table.(if possible).

    Any ways, As a DBA what are the other things that i have to keep in mind ??

    Thanks again for reply...

    commented on Dec 11 2012 7:10AM
    Krishnrajsinh Rana
    137 · 1% · 362
  • I do not have a ready list, however, I have invited a few DBAs that I know who may want to step in and share their thoughts on this.

    I generally skip tables having less than 1000 pages from the index rebuild/reorganize operation. I also ignore fragmentation up to 10%. Also, you can play (responsibly) with FILLFACTOR to control fragmentation on some cases. I also log the fragmentation information to a table so that I have some history and can look at that later (and probably create some reports that show the the fragmentation trend)

    commented on Dec 11 2012 7:53AM
    Jacob Sebastian
    1 · 100% · 32235
  • Jacob has identified some very helpful criteria for assessing when you should "pull the trigger" on an index rebuild as well as a 'best practices' monitoring suggestion. To add to the reply to your initial question, Krishnrajsinh, the question of automating the rebuild is largely dependent on what you mean by frequently updated database. Is the table updated hourly, or just daily, or otherwise? Further, what is the need for index optimization (is this for OLTP?) In our shop we have some defragmentation jobs that are scheduled to run several times an hour, but that is not 'typical' within the broad scope of our SQL Server instances. So there are scenarios that can justify such an operation in order to keep online services performing optimally, if I am understanding your question. I also would echo Jacob's statement to review the nature of the index(es) themselves to see if they are both necessary and if they are efficient given the structure of the table(s).

    Finally, you may have read through this, but the MSDN article on 'Reorganizing and rebuilding indexes' is not only thorough in its explanation but has some further helpful links in terms of index concepts and best practices:

    http://msdn.microsoft.com/en-us/library/ms189858(v=sql.105).aspx

    commented on Dec 11 2012 8:19AM
    GBurnett
    792 · 0% · 37
  • See http://ola.hallengren.com

    You'll struggle to find a more comprehensive set of maintenance scripts. Index optimisation is one of them, and allows you to set your own thresholds for when to reorg or rebuild. I've created my own scripts in the past and I've even gone so far as to drop and recreate the index in some circumstances.

    Automation is definitely the way to go for this, but as mentioned above, each situation is different so you need to create a schedule that suits your environment.

    Also consider the physical fragmentation of your database. In cases where your database data file has been increased in size, it may be that there are various blocks scattered across the disk (in some SAN or disk configurations this may be the case anyway), in which case you may not see a lot of benefit from rebuilding an index. Index reads may still be producing random IO on the disk(s). Recreating the index, as I mentioned above, can sometimes alleviate this as long as the index is recreated on a single data block allowing sequential IO when it is read from.

    Here's a really good article explaining various fragmentation issues: http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

    commented on Dec 19 2012 9:12AM
    Mike Lewis (@SQLTuna)
    42 · 4% · 1336

Your Reply


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]