locked
Rebuilding indexes after modifying the data RRS feed

  • Question

  • Hi everyone!

    Simple question for a database index expert - do I have to rebuild indexes every time I modify the data?

    Thanks in advance,

    Miljan

    Wednesday, October 17, 2012 1:26 PM

Answers

  • Hi,

    Probably better to make sure you have auto update statistics on, and manually update statistics as required.  You'll only need to reorganise/rebuild indexes when they become fragmented.  I guess when you say 'modify' data, it depends exactly what you're doing.

    The easiest option would be to implement this popular utility: http://ola.hallengren.com/



    Thanks, Andrew


    • Edited by Andrew Bainbridge Wednesday, October 17, 2012 1:29 PM
    • Proposed as answer by Sanil Mhatre Wednesday, October 17, 2012 5:19 PM
    • Marked as answer by Maggie Luo Wednesday, October 24, 2012 8:24 AM
    • Unmarked as answer by Miljan Radovic Wednesday, October 24, 2012 8:37 AM
    • Unproposed as answer by Maggie Luo Wednesday, October 24, 2012 4:11 PM
    • Marked as answer by Miljan Radovic Wednesday, October 24, 2012 7:53 PM
    Wednesday, October 17, 2012 1:29 PM
  • Hi,

    no,you can specify the fill factor in index so that remaining speace is used for insert,update, Delete after some time if the fragmentation is more the reorganise or rebuild the index with fill factor

    For more information http://msdn.microsoft.com/en-us/library/ms177459.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. By ganeshk


    Monday, October 22, 2012 6:51 AM
  • Generally NO. Make sure that Auto Update statistics database property is set to ON and as Andrew pointed, take a look at Ola's script.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Monday, October 22, 2012 6:54 AM
  • Hi Miljan,

    Depending on the fragmentation level, you need to rebuild or reorganize the indexes. The more the fragmentation you need to rebuild, if it is less you can reorganize. You can query the sys.dm_db_index_physical_stats DMV to identify the fragmentation. Check the column, avg_fragmentation_in_percent and if it is greater than 30 you can rebuild or else something around 10 to 30 you can reorganize.

    No need to rebuild indexes every time when you modify the data.
    Please refer this post which will help you understand better:
    http://blogs.msdn.com/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx.

    Thanks.


    Maggie Luo

    TechNet Community Support

    • Marked as answer by Miljan Radovic Wednesday, October 24, 2012 7:54 PM
    Wednesday, October 24, 2012 4:26 PM

All replies

  • Hi,

    Probably better to make sure you have auto update statistics on, and manually update statistics as required.  You'll only need to reorganise/rebuild indexes when they become fragmented.  I guess when you say 'modify' data, it depends exactly what you're doing.

    The easiest option would be to implement this popular utility: http://ola.hallengren.com/



    Thanks, Andrew


    • Edited by Andrew Bainbridge Wednesday, October 17, 2012 1:29 PM
    • Proposed as answer by Sanil Mhatre Wednesday, October 17, 2012 5:19 PM
    • Marked as answer by Maggie Luo Wednesday, October 24, 2012 8:24 AM
    • Unmarked as answer by Miljan Radovic Wednesday, October 24, 2012 8:37 AM
    • Unproposed as answer by Maggie Luo Wednesday, October 24, 2012 4:11 PM
    • Marked as answer by Miljan Radovic Wednesday, October 24, 2012 7:53 PM
    Wednesday, October 17, 2012 1:29 PM
  • Hi,

    no,you can specify the fill factor in index so that remaining speace is used for insert,update, Delete after some time if the fragmentation is more the reorganise or rebuild the index with fill factor

    For more information http://msdn.microsoft.com/en-us/library/ms177459.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. By ganeshk


    Monday, October 22, 2012 6:51 AM
  • Generally NO. Make sure that Auto Update statistics database property is set to ON and as Andrew pointed, take a look at Ola's script.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Monday, October 22, 2012 6:54 AM
  • Hi Miljan,

    Depending on the fragmentation level, you need to rebuild or reorganize the indexes. The more the fragmentation you need to rebuild, if it is less you can reorganize. You can query the sys.dm_db_index_physical_stats DMV to identify the fragmentation. Check the column, avg_fragmentation_in_percent and if it is greater than 30 you can rebuild or else something around 10 to 30 you can reorganize.

    No need to rebuild indexes every time when you modify the data.
    Please refer this post which will help you understand better:
    http://blogs.msdn.com/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx.

    Thanks.


    Maggie Luo

    TechNet Community Support

    • Marked as answer by Miljan Radovic Wednesday, October 24, 2012 7:54 PM
    Wednesday, October 24, 2012 4:26 PM