0

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)

yagmoth555
  • 16,300
  • 4
  • 26
  • 48
Martijn
  • 125
  • 5

1 Answers1

1

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.

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • 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 08 '10 at 16:33
  • @Martijn - yup, sometimes we have to work under unrealistic constraints. – mfinni Nov 08 '10 at 16:40