Best way to reindex large tables/indexes

Question

Post reply

Best way to reindex large tables/indexes

TroyG

SSCrazy

Points: 2633
More actions
March 25, 2008 at 1:41 pm

#120600

Hi,
I have a couple of tables that are rather large in size (between 30 and 80gb); and in turn their indexes amount to a rather large size also due to their size.
What is the best way to rebuild the indexes on these? They have considerable fragmentation so it needs to be done; but I am just concious of creating a massive transaction log and potentially having the table offline for considerable period of time.
Any feedback on best approaches to working with reindexing on large datasets/index sizes would be hugely appreciated.
Thanks in advance
Troy

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Vivien Xing SSChampion Points: 12297 More actions · Answer 1

Do you have to reindex or DBCC INDEXDEFRAG can be an alternative?

BOL: search Server FAQ INDEXDEFRAG:

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

Plus, when you have to terminate it, the work have done remains.

TroyG SSCrazy Points: 2633 More actions · Answer 2

thanks for the response; I had considered the DBCC INDEXDEFRAG, but wasn't too sure whether the outcome was timely and considerate of disk space/log size

So in terms of speed is this relativly slow, due to it leaving the table available?

And in terms of the transaction log; potentially this will make the log massive?

Thanks again.

Vivien Xing SSChampion Points: 12297 More actions · Answer 3

So in terms of speed is this relativly slow, due to it leaving the table available?

---> No

And in terms of the transaction log; potentially this will make the log massive?

--> Not really

I was looking for this link and finally found it. Hope this helps. http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EJKAC

You may try on a testing environment using a copy of the database and see how it goes.

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 4

Assuming you are using SQL2005, you can rebuild indexes online if you have Enterprise edition. The level of fragmentation should dictate whether you need to rebuild or reorganize. If it is feasible to use bulk logged mode for the duration, that will all but eliminate the transaction log entries.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

DBCC IndexDefrag is deprecated in SQL 2005. Use ALTER INDEX... REORGANIZE instead.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Vivien Xing (3/25/2008)
So in terms of speed is this relativly slow, due to it leaving the table available?
---> No
And in terms of the transaction log; potentially this will make the log massive?
--> Not really
I was looking for this link and finally found it. Hope this helps. http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EJKAC
You may try on a testing environment using a copy of the database and see how it goes.

Now, THAT was very worth the read! Thanks Vivien.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vivien Xing SSChampion Points: 12297 More actions · Answer 7

As it is 2005 already, we should have better options ONLINE index, SORT_IN_TEMPDB and my favorite MAXDOP option.

Online Indexing Operations in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/onlineindex.mspx

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 8

Personally with large tables/indexes I always use a procedure which first determines the fragmentation and when depending on the reuslts either does nothing, Reorganize or Rebuild the index.

BOL contains a sample script if you look under sys.dm_db_index_physical_stats example D.

Another example you find here: http://weblogs.sqlteam.com/tarad/archive/2007/04/17/60176.aspx

While the analysing part can take quite some time, usually the total job takes no more than 10 - 20% compared to a full rebuild of all indexes.

Table partitioning might also be an idea with large tables. Depending on how you're data is used you might only need to defrag a few partions while the rest is ignored.

[font="Verdana"]Markus Bohse[/font]

TroyG SSCrazy Points: 2633 More actions · Answer 9

This is great! Thanks for the thoughts, links and tips. I had just been cautioned about the impact of rebuilding the index as per my above posts; hence my hesitance. But ran with the ALTER INDEX using the ONLINE switch and all was well. Due to our trans log backup routine, nothing blew out, and it all was processed well in time with no undue grief.

We already have an automated routine that runs through and rebuilds or reorganises depending on the specifics of the index; but these ones had been highlighted due to their table/index size and no action undertaken automatically.

Thanks again!!

dbychen SSCommitted Points: 1889 More actions · Answer 10

The indexes you mentioned seem to be created with higher fillfactors. Check these parameters for the indexes. If they are high, you can drop and recreate them with a lower fillfactor value (leaving more space for data growth on the data pages).

If you have a database maintenance window, you may change the database to SIMPLE recovery model and do the index maintenance work without worrying transaction log file. Online indexing is an option, if no maintenance window is available.

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 11

I use a script that starts rebuilding indexes in a period of an hour and a table that keeps track of which indexes has been rebuild. The job is executed every night.

My blog

lklein Ten Centuries Points: 1234 More actions · Answer 12

Would you share that code? We have 2 jobs one that loops through to rebuild indexes and one that's an SSIS package - and does all tables. I've been tasked with getting them to work - and they are both wrong - so I need a better job to do this. I think from what I've read we are lucky they failed - as it would have blown the log file. I've been trying to tell them about backing up the log... thanks...

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

If you want one that's ready made and pretty handy, lookup DBCC SHOWCONTIG and take a look at "Example E"... It even has a place to set a "Max Frag" setting that serves to keep from reindexing static or nearly static tables that simply don't need to be reindexed because few or no changes to the data have been made...

... and get rid of that DTS job that does it... it's unnecessary level of complexity.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lklein Ten Centuries Points: 1234 More actions · Answer 14

I hate to be dense - but where's example E? I ran the showconfig and it's full of cool info...