Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a fairly large table, which I have to keep up, with some very, very fragmented indices. This seems to be causing problems with performance. I would like to have neat fresh crisp indices on this table. I also have some rather annoying restrictions: 1. At no time can this table become unavailable. 2. I don't have Sql Server enterprise edition (i.e. no online rebuilding of indices)

What is my best course of action? Just try and reorganise them? How long would this roughly take on a table with about 600k rows, on moderate hardware? (answers within an order of magnitude are precise enough)

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Recreate index in off time OR do it without dropping it - SQL Server Enterprise edition ahs this feature, and a table which CAN NOT BECOME UNAVAILABLE needs enterprise edition. Sorry. Otherwise you are basically toast. I cant run a formula 1 race witha street car. I cant fullfill enterprise high end requirements without the proper version.

What is my best course of action? Just try and reorganise them?

Yes. Choose your time wisely (i.e. off).

How long would this roughly take on a table with about 600k rows, on moderate hardware?

Define moderate hardware? 4 cores, 16gb RAM, about 10 high end discs - possibly in the range of minutes (i was merging 650 million rows into a table on a mid range server in 10 minutes).

Note that your definition of "moderate" may vary - but anything that fits into one rack case is moderate for me.

share|improve this answer
1  
'I cant run a formula 1 race witha street car' It's daily practice for me. I just happen to end last on every race. –  Martijn Nov 8 '10 at 16:33
    
@Martijn - yup, sometimes we have to work under unrealistic constraints. –  mfinni Nov 8 '10 at 16:40

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.