Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have rather big tables (500 mils rows) where we expect frequent inserts. The tables have approx. 2 indexes each. The problem is that we are afraid that, when the index is recalculating the whole table is locked and all queries will time out (during the time of recalculating).

Is there a way how to minimize the occurrence of recalculating the index? For example let the nodes to be bigger when they are created, so more added pointers will fit in the index without the need of the rebalancing the index?

We have SQL Server 2008 R2.

share|improve this question
By recalculating I mean "splitting" the old node to new nodes due to to the fact, that the the old node (or the whole branch) is too small to accomodate the new record. Maybe "recalculating" is not the correct term. – Ondra Peterka Oct 9 '13 at 11:49

I asume by "recalculating" you are referring to completely rebuilding an index. SQL Server will never completely rebuild the indexes unless you explicitly tell it to, so you will not see random pauses in performance due to it deciding to kick off a rebuild.

Each index does increase the amount of work that needs to be done for a given INSERT or UPDATE operation because a small portion of the index needs to be updated but this is a small portion: something of the order of up-to-10 pages affected for 500M rows (assuming the index is over a small datatype such as an INT, DATETIME or UUID - an index over a long string type for instance will require a little more work as less entries fit in one page, but it will still be similarly little work relative to the whole index's size). During these small updates due to INSERT and UPDATE operations there are no huge locks unless a table lock was required for something else in the transaction (and even if there are, the smallness of the operation per row means you'd only see the lock having significant effect if acting on many rows).

Rebuilding a whole index very rarely needs to be done, and will only be done if you request it explicitly. You can reduce the affect of index rebuilds if you have Enterprise Edition by using online index builds - there will still be a time were the table is locked as the newly generated index is attached to the table but this will be small compared to the time for the whole index build.

share|improve this answer

Your Answer


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.