• Adam Thurgar

Pad Index

Most DBA's are familiar with the index option of fill factor. It tells the Database Engine how full it should make the leaf level of each index page during index creation or alteration. This avoids page splits occurring which is an expensive IO operation and can cause extensive locking and potential blocking. For insert intensive tables the fillfactor can make a big difference in performance of OLTP tables/applications.

But not many DBA's use or understand the pad_index index option.

If pad_index is set to ON (default is OFF), this specifies the percentage of free space that is specified by FILLFACTOR is applied to the intermediate-level pages of the index.

The intermediate-level pages are filled to near capacity, leaving enough space for at least one row of the maximum size the index can have, given the set of keys on the intermediate pages.

If the intermediate-level pages start splitting then this can cause extensive performance issues (IO, blocking and disk space).

Once again this can have a significant impact on insert intensive tables.

Therefore for these tables you need to consider the use of pad_index on the indexes.

One of the limitations of pad_index is that it uses the fillfactor percentage, this may be too low for what the intermediate-level pages actually require.

This can lead to wasted space in the upper levels of the index structure.


Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t