Rebuild Index over large table
-
Does doing a DBCC INDEXDEFRAG or DBCC REDINDEX or ALTER INDEX REBUILD cause transaction log entries or use temporary disk space during the execution? I just want to make sure if I execute this on a large table (aprox 15 million records) that the server will not crash. I am not sure what goes on behind the covers. Any precautions/guidelines should I be aware of before I execute these? Thanks for the feedback...Richard
-
Pretty sure these are logged ops. Indexdefrag afaik is not a single transaction though - so the log can be truncated/backed up during the operation. The other 2 on the other hand are more likely to disrupt you.
Advise a quick look at Books Online under dbcc indexdefrag:
- Unlike DBCC DBREINDEX (or the index building operation in general), DBCC INDEXDEFRAG is an online operation. It does not hold locks long term and thus will not block running queries or updates. A relatively unfragmented index can be defragmented faster than a new index can be built because the time to defragment is related to the amount of fragmentation. A very fragmented index might take considerably longer to defragment than to rebuild. In addition, the defragmentation is always fully logged, regardless of the database recovery model setting (see ALTER DATABASE). The defragmentation of a very fragmented index can generate more log than even a fully logged index creation. The defragmentation, however, is performed as a series of short transactions and thus does not require a large log if log backups are taken frequently or if the recovery model setting is SIMPLE.
-
Richard
I think I read somewhere that reindexing a table will cause the transaction log to grow by up to 1.25 (or was it 1.5?) times the size of the table. Check out this link for some tips on reindexing:
http://www.sql-server-performance.com/rebuilding_indexes.asp
John
-
Aye, it even says so in books online.
The important part tho is that the log can be truncated/backed up at frequent intervals during the process with defrag. My understanding is that reindex is a single transaction (either you have an index or you don't) therefore the log will grow (and be active and therefore untruncable) to a large degree.
-
Log growth is very high while Indexdefrag process running compared to reindex...
You can try changing the recovery model to bulk_logged before index defrag and can change back to Full once it is done...
MohammedU
Microsoft SQL Server MVP -
What are the performance and logging implications if the index were to be deleted and recreated - as opposed to defragging? I know that access to the table may be locked during the process, but is there anywhere that can show the pros and cons of performing this with relation to the other?
Another point to think about as your table is large - can it be split so as to keep index rebuilding to the last N days . Ie use views when reading data to concatenante the tables , yet only perform updates, inserts to a chronological table . Just a thought as you would not need to continually reindex the entire data set , only the current stuff that gets changed often (all depends on your application and data workflow)
** What you see, Depends on what you Thought, Before, You looked! **
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply