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
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 -