Simply put, indexes are ordered structures. This ordering allows indexes to be used to quickly search. Any changes to the index must result in the order being maintained. If order is not maintained, searching could not occur quickly, and the index would be both worthless and corrupt. So order in an index must be maintained. This has profound performance implications.

While index ordering enables quick searching, in a high-concurrency insert situation, it can cause severe performance problems. For example, suppose an index is based on an increasing sequence number (for example, 1, 2, 3, and so on), which is commonly called a monotonically increasing value. If a table column contains this sequence number and that column is indexed, the respective index entries will be placed next to each other in an index leaf block. Why? Because an index must maintain sorted order, and this index is based on an ascending sequential number.

The problem can occur when many sessions concurrently insert rows. As each insert statement gets the next sequence number, the index entry will likely be physically placed into the same index leaf block or less likely into the adjacent index leaf block. If the concurrency increases enough, sessions will need to wait a relatively long time for another session to complete its leaf block change. When the session waits, it will post a buffer busy waits event, and the buffer it is waiting on will be an index leaf block. This situation can become extremely serious and a massive performance killer.