DBREINDEX 500GB DB

Question

Post reply

DBREINDEX 500GB DB

RSingh-135921

Default port

Points: 1438
More actions
January 16, 2009 at 7:29 am

#196145

Hi,
We are having a 500GB database.
We cleared around 75% of the data from the tables in the database.
I was thinking of running DBREINDEX on the database to get rid of index fragementation that might have occurred because of this "huge" deletion.
Can anyone advise, how long it will take to reindex tables in such a large database?
Regards

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

RSingh (1/16/2009)
Can anyone advise, how long it will take to reindex tables in such a large database?

How long is a piece of string?

It depends on the size of the tables that you're reindexing, the performance of the IO system, other activity in SQL, other activity on the server, etc. There's no way to conclusively say, except to run it and see.

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

RSingh-135921 Default port Points: 1438 More actions · Answer 2

Thanks Gail for the reply.

The largest table occupies around 35 GB after deletion.

Top 20 tables out of total (19000) are around 250 GB in total in size.

I am planning to reindex around midnight when system is least used.

The server has 16 Intel Xeon 3.33 GHz processors.

So any thoughts about the time to dbreindex these 20 tables?

Regards

RSingh-135921 Default port Points: 1438 More actions · Answer 3

Sorry, forgot to mention that the largest table has around 31 million rows and smallest among top 20 has around 2 million.

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

RSingh (1/16/2009)
So any thoughts about the time to dbreindex the?

It depends on the performance of the IO system, other activity in SQL, other activity on the server, etc. There's no way to conclusively say, except to run it and see.

For what it's worth, I can reindex a million row table on my desktop in 14 seconds and a 7 million in 2 minutes.

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

d33m SSChasing Mays Points: 620 More actions · Answer 5

I would strongly suggest not to do a blind reindex. Check and see how fragmented the indexes are and possibly do a defrag instead. If it's 30% fragmented then do a reindex, if it's between 5-30% do a defrag, and if it's below 5% do nothing. You can adjust your thresholds accordingly, but there may be very large tables which are static and barely fragmented. Do some analysis first.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 6

Search for the script on this site that will help you in implementing what tfoley said. If you don't get the script from here then let me know I will provide you the code.

MJ

RSingh-135921 Default port Points: 1438 More actions · Answer 7

Thanks tfoley, MJ.

I found a script in the scripts section of this site which helped me find out the indexes that were fragmented the most.

Then I rebuilt all the ones that had pages > 5000 and fragmentation > 10 %

This helped great deal since the pages were reduced by an astounding 80% or more.

Thanks everyone who replied on the thread.