0

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.

1
  • 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. Commented Oct 9, 2013 at 11:49

1 Answer 1

5

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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