reindex database in sql server 2008

Question

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

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

Answer 1

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

DECLARE @sql NVARCHAR(MAX) = N''
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

• datagod: 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.