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

sql-server indexing maintenance
thiswayup picture thiswayup · Feb 24, 2009 · Viewed 20k times · Source

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.

Answer

Chris picture Chris · Feb 24, 2009

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.

Related questions

How do I monitor and find unused indexes in sql database

I would like to monitor index usage for an sql database, in order to find unused indexes and then drop them. How can I monitor index usage most efficiently? And which scripts could be useful? (I'm aware of this question …

Read More
What do Clustered and Non clustered index actually mean?

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was : A clustered index is a special type …

Read More
List of all index & index columns in SQL Server DB

How do I get a list of all index & index columns in SQL Server 2005+? The closest I could get is: select s.name, t.name, i.name, c.name from sys.tables t inner join sys.schemas s on …

Read More