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

Question

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 1

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.

Solution 2

As people have mentioned here, your indexes do not automatically rebuild. This is quite a big problem in SQL Server, as your indexes will fragment over time. Your could find your indexes are 95% plus fragmented, affecting query performance badly.

Here is a simple query to check fragmentation on your existing indexes:

DECLARE @DBNAME VARCHAR(130);
SET @DBNAME = 'MYDBNAME';

DECLARE @DBID INT;
SET @DBID = DB_ID(@DBNAME);

SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count 
INTO #indexDefragList
FROM
sys.dm_db_index_physical_stats 
(@DBID, NULL, NULL , NULL, N'Limited')
WHERE
index_id > 0
OPTION (MaxDop 1);

SELECT
i.[name] as indexname,
d.fragmentation,
d.page_count
FROM
#indexDefragList d
INNER JOIN sys.indexes i
ON d.objectid = i.object_id
ORDER BY 
d.fragmentation DESC

DROP TABLE #indexDefragList

This will return a list of all indexes in your current DB with their fragmentation in %.

You can easily build a script to automatically rebuild or reorganise them. There is a great article from SQLFool on this including a complete pre-made script.

Solution 3

To expand on what Chris has said:

With regard to statistics, columns not covered by an index will not have their statistics updated by rebuilding all indexes. They may periodically be updated by SQL Server however you may need to do this yourself with the UPDATE STATISTICS statement.

SQL Server 2005 determines whether to update statistics automatically based on changes to column modification counters (colmodctrs).

A statistics object is considered out of date in the following cases:

1.The table size has gone from 0 to >0 rows.

2.The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then.

3.The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered

You may find the following reference regarding statistics of use:

http://blogs.technet.com/rob/archive/2008/05/16/sql-server-statistics.aspx

Hope this helps but feel free to pose further queries.

Cheers, John

Solution 4

As @Chris noted, indexes are not rebuilt automatically in any SQL Server version. Proper index maintenance is often missing at sites with no dedicated DBA role, and often when databases are simply moved from development into production (along with Transaction Log maintenance).

SQL Server 2005+ has the ability to do online index reorganises and offline full rebuilds.

View more solutions

Comments

[Felipe Sabino]

I see that your answer is from 2009, is this still valid for newer versions of sql server?

[Chris]

AFAIK this is still valid, although there may be better options to help you set this type of maintenance job up. Perhaps check over at dba.stackexchange.com?

[Fer]

thanks for the script. But i had to replace OBJECT_ID to object_id and d.objectid to d.objectID. I guess that is because of case sensitivity of lower case chars according to my current encoding.