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.