Database Reference
In-Depth Information
FILLFACTOR and PAD_INDEX
Every index in SQL Server has a
FILLFACTOR
option, which allows you to reserve some space on the leaf-level index
data pages. Setting
FILLFACTOR
to something less than 100, which is default value, increases the chances that data
pages will have enough free space to accommodate the newly inserted or updated data rows without a page split
involved. That option can be set on both the server and individual index levels. SQL Server uses the server level
FILLFACTOR
when the index does not have
FILLFACTOR
explicitly specified.
SQL Server maintains
FILLFACTOR
only when creating or rebuilding the index. It still fills pages up to 100 percent
during normal workload, splitting pages when needed.
Another important factor to keep in mind is that by reducing
FILLFACTOR
, you decrease external index
fragmentation and the number of page splits by increasing internal index fragmentation. The index will have more
data pages, which will negatively affect the performance of scan operations. Moreover, SQL Server will use more
memory in buffer pool to accommodate the increased number of index pages.
There is no recommended setting for
FILLFACTOR
. You need to fine-tune it by gradually decreasing its value and
monitoring how it affects fragmentation with the
sys.dm_db_index_physical_stats
function. You can start with
FILLFACTOR = 100
, and decrease it by 5 percent by rebuilding the index with a new
FILLFACTOR
until you find the
optimal value that has the lowest degree of both internal and external fragmentation.
It is recommended that you keep
FILLFACTOR = 100
with indexes that have ever-increasing key values. All inserts
into those indexes come at the end of the index, and existing data pages do not benefit from the reserved free space
unless you are updating data and increasing row size afterwards.
Finally, there is another index option,
PAD_INDEX
, which controls whether
FILLFACTOR
is maintained in non-leaf
index pages. It is OFF by default and rarely needs to be enabled.
Index Maintenance
SQL Server supports two methods of index maintenance that reduce the fragmentation: index reorganize and index rebuild.
Index reorganize
, which is often called index defragmentation, reorders leaf-level data pages into their logical
order. This is an online operation, which can be interrupted at any time without forgoing the operation's progress up
to the point of interruption. You can reorganize indexes with the
ALTER INDEX REORGANIZE
command.
An
index rebuild
operation, which can be done with the
ALTER INDEX REBUILD
command, removes external
fragmentation by creating another index as a replacement of the old, fragmented one. By default, this is an offline
operation, and SQL Server acquires and holds shared (S) table lock for the duration of the operation, which prevents
any data modifications of the table.
■
Note
We will discuss lock types and their compatibility in greater detail in Chapter 17, “Lock types.”
The Enterprise edition of SQL Server can perform an online index rebuild. This operation uses row-versioning
under the hood, and it allows other sessions to modify data while the index rebuild is still in process.
■
Online index rebuild acquires schema modification (SCH-m) lock during the final phase of execution. even
though this lock is held for a very short time, it can increase locking and blocking in very active OLtp systems. SQL
Server 2014 introduces the concept of low-priority locks, which can be used to improve system concurrency during
online index rebuild operations. We will discuss low-priority locks in detail in Chapter 23, “Schema Locks.”
Note
Index rebuild achieves better results than index reorganize, although it is an
all or nothing
operation; that is, SQL
Server rolls back the entire operation if the index rebuild is interrupted. You should also have enough free space in the
database to accommodate another copy of the data generated during the index rebuild stage.
