September 19, 2008 at 11:23 am
Hi, Does anyone have any experience running reindex on extremely large tables like 57 million rows? On our test server I run the routine in full recovery mode it takes 2 hours, and if I switch to simple recovery it takes 1 hour. In either case, it uses up all resources on the server. ie nobody can do anything on the server while the job is running. Is there a way to prevent this from happening?
Does the system performance go down that much during a reindex? We have a large enough tempdb, log file and free space on the array.
Unfortunately, when the server drives were configured with only 2, C and D.
thanks,
September 19, 2008 at 11:36 am
how big is your log file, is it havinging to continually grow as the re-index operation is occurring? Also, have you taken a look at the index defrag command? Depending on your level of fragmentation, that may be more efficient...
-Luke
From BOL:
What is the difference between DBCC INDEXDEFRAG and DBCC REINDEX?
Answer:
Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an online operation, so it does not hold long-term locks that can block running queries or updates. Depending on the amount of fragmentation, DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a relatively unfragmented index can be defragmented much faster than a new index can be built. Another advantage is that with DBCC INDEXDEFRAG, the index is always available, unlike DBREINDEX. A large amount of fragmentation can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX, which may or may not outweigh the benefit of the command's online capabilities. DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index.
September 19, 2008 at 11:55 am
When I ran it in Full recovery the log file had to expand 2 x.
I am really concerned about getting this table to perform better but the apparent hanging is more of a concern. The database is up 24x7 and it it actually used 24x7. The table is used by all users for everything.
I will have to restore onto the test database again to get a good benchmark since I ran reindex already.
September 19, 2008 at 1:34 pm
If you were to upgrade to SQL Server 2005\2008 Enterprise Edition - you could then rebuild your index online. Well, actually that depends upon the table and index being rebuilt. There are restrictions concerning LOB objects that could prevent you from rebuilding online - but if you do not have any of those types of columns you would be okay.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply