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. It's 100% free, no registration required.

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
Ah - you have a rather SMALL table (ONLY 500 million rows) and you assume that will have a problem? Let me ask - what do you think "recaltulating the index" is? I have 180 processes hitting a 9 billion row server on a virtual machine and I have no problems. – TomTom Oct 9 '13 at 9:13
@TomTom ok, I give you that you have bigger tables ;) 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
It is not ;) But sierously- there is a definition of large database since I started with SQL a long time go. It is "larger than you can fit in RAM" with the implicit understanding this is a mid range server (ram WAS cheap and IS cheap). That would today be in the 256gb to 512gb area. My OLD servers have 64gb. That is like 4 years old by now. – TomTom Oct 9 '13 at 13:57
@TomTom RAM is cheap but one has to pay the Enterprise licence for SQL-Server to use more than 64GB. – ypercubeᵀᴹ Oct 9 '13 at 15:27
Yes and no. Yes on SQL Server, no for a caching RAM hard disc. THat immediately gives you gigabytes of cached IO. The 64gb limit is a shame though - but again, that makes 64gb a non small database. 500 million rows..... 64gb... want to make the math? – TomTom Oct 9 '13 at 19:52

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
thanks +1 for the reply. Please see my comment to TomTom above. Recalculationg = splitting. This "splitting" does nto lock the whole table? I thougth it does. And is it really that trivial that the splitting is done in so small amount of time that it is negligible? – Ondra Peterka Oct 9 '13 at 11:54
Yes, for normal operations like that there is no need for a full table lock and even if there is a lock (due to other operations in the transaction) the operation is not long lived. Obviously don't have unnecessary indexes, don't include unnecessary columns in the index you have, and so forth. – David Spillett Oct 9 '13 at 13:00
You can reduce page splits by keeping indexes small (not including unnecessary columns, using small types where possible (INT rather then BIGINT) but don't over-optimise here at the expense of slowing retrieval or requiring extra work in other layers of your app) and by inserting data in index order if possible (though this is usually not something you can control to a practical extent). – David Spillett Oct 9 '13 at 13:00

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.