Reserving free spaces for indexes

You might reduce performance problems that result from index page splits by reserving free space when you reorganize the indexes.

About this task

When sufficient free space is unavailable in an index space, index page splits can result and performance might suffer.

With the introduction of fast storage hardware that enable fast list prefetch, index organization becomes less important. A disorganized index is likely to have plenty of free space. Therefore, you do not need to reorganize indexes for the purpose of creating free space.

However, free space remains an important consideration whenever you do reorganize your indexes.

To manage free space for indexes, use the following approaches:

• Reserve some free space in cases when reorganizing an index would result in a subsequent increase in index page splits. Reserving free space can reduce the frequency of index page splits.
• Specify no free space in the following cases:
  • A non-clustering index contains a column with a timestamp value that causes the inserts into the index to be in sequence.
  • Inserts are in ascending order by key of the clustering index or they are caused by LOAD RESUME SHRLEVEL NONE and update activity is only on fixed-length columns with non-compressed data.
  • The associated table is read-only, or you do not plan to insert new data or update the existing data in the table.
  • The data is stored on storage hardware that uses fast controllers and solid state disks and can process list prefetch efficiently.