Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

  • Is it by using Maintenance plan?
  • Would I face performance problems while reindexing?
share|improve this question

migrated from stackoverflow.com Dec 19 '11 at 13:32

This question came from our site for professional and enthusiast programmers.

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.