Leaving space for forwarded rows and inserts

Setting a reservepagegap value can reduce the frequency of maintenance activities such as running reorg rebuild and re-creating indexes for some tables to maintain high performance. Good performance on data-only-locked tables requires good data clustering on the pages, extents, and allocation units used by the table.

The clustering of data and index pages in physical storage stays high as long as there is space nearby for storing forwarded rows and rows that are inserted in index key order. The reservepagegap space management property is used to reserve empty pages for expansion when additional pages need to be allocated.

Row and page cluster ratios are usually 1.0, or very close to 1.0, immediately after a clustered index is created on a table or immediately after reorg rebuild is run. However, future data modifications can cause row forwarding and can require allocation of additional data and index pages to store inserted rows.

Setting a reserve page gap can reduce storage fragmentation and reduce the frequency with which you need to re-create indexes or run reorg rebuild on the table.