The best way to reindex 500GB database in SQL Server 2008

  • Is it by using Maintenance plan?
  • Would I face performance problems while reindexing?

Actually - reindexing WILL lead you to to performance issues. It very depends what you want to reindex and which server edition you have.

If you have an Enterprise+ edition - you can reindex all the non-clustered indexes as ONLINE operation

SELECT @sql = @sql + N'ALTER INDEX ['+i.name+N'] ON ['+s.name+N'].['+t.name+N'] REBUILD WITH (ONLINE = ON);'+NCHAR(13)+NCHAR(10)
FROM sys.indexes i
JOIN sys.tables t
    ON i.object_id = t.object_id
JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE i.type = 2
PRINT @sql

same you can do with the clustered indexes when them does not contain BLOB columns - just replace i.type = 2 to i.type = 1

for reindexing specially clustered index of Very Big Tables with BLOB fields - you may choose three options -

  1. or rebuild clustered index offline
  2. or by moving all the table's data into the new table (painful)
  3. using 'large values out of row' option ON, make sure that all the LOB IS off-row (update table set LOB=LOB --do not use this - it is just an examlpe), rebuild the clust. index offline relatively fast and after it - reorganize it with lob compaction - which is an online operation by default
| improve this answer | |
  • If you decide to move forward with rebuilding indexes online, make sure you have enough room on the data drive, as you database will grow accordingly. – datagod Dec 19 '11 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy