sql-serverindexingmaintenance-plan

Do all SQL server versions rebuild indexes automatically or have a default rebuild criteria?


Do all SQL server versions rebuild indexes automatically or have a default rebuild criteria? I understand statistics are rebuilt automatically but not sure if indexes do as well.


Solution

  • Rebuilding of indexes is not supported automatically in any version of Microsoft SQL Server - the reason being is that rebuilding indexes can potentially be very expensive operations, and so need careful scheduling and planning.

    In many environments special scripts will be written to handle this, for example:

    http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx

    Note that whilst SQL can automatically update statistics for you in many cases there is a performance gain to be had by managing these more carefully as well.