Index Rebuild Observations
As part of a series of migration tests carrying out an all index rebuild on a user database has been used to compare relative performance.
As the application is a true 7 x 24, there are no maintenance windows so index rebuilds are very intrusive and elicit user complaints.
For SQL 2000 dbcc dbreindex is used, for SQL 2008 ( and SQL 2005 ) ALTER INDEX ALL xxx REBUILD is used.
It wasn't the times which were particularly of interest but a difference in SQL 2008 between the index rebuild when the database was in Simple Recovery vs Full Recovery.
- For those technical little details all the servers use the same quad intel procs and have 4 sockets. Memory is either 32 or 64 GB, SQL 2000 is x32, SQL 2005/8 is x64 all Enterprise SQL.
- Tests on SQL 2005 were carried out as an afterthought, the hardware did not have the capacity to use the same database. ( The migration is 2000 - 2008 so 2005 was not part of the testing )
- Database files were sized such that there were no autogrow events. Index rebuilds were carried out on a fresh restore each time.
- Clearing cache(s) and/or restarting sql server and/or running tests without restoring the database did not appear to make any difference, although the number of tests like this were limited.
- Servers are all SAN attached although the vendors and configs are different. The same database was used for all sql 2000/8 tests, database converted to sql2008 mode for SQL 2008.
- The index rebuild code is in a sp_ stored procedure in master.
Anyway for SQL 2000 the index rebuild time was approx 14 minutes regardless of database recovery model.
SQL 2008 managed approx 4 mins in simple recovery and 9 mins in full recovery.
The tests were repeated up to 80 times each.
The calls to rebuild each index are built within a while loop; rewriting the proc to use a cursor reduced the 9 mins to 8 mins 30 sec on SQL 2008, this was not tried on SQL 2000.
A brief set of tests on SQL 2005 showed the same type of variance between recovery models..
Although I was not particularly looking for differences concerning parallelism I did run a few tests with it on and off.
These were not so scientific, however with SQL 2000 there didn't appear to be a significant difference, maybe 10%, but my tests with SQL 2008 showed that with parallelism off the index rebuilds took three times as long.
One last observation was that on average running the SQL 2008 index rebuild as a job took 1 minute longer than calling the procedure from a query window.