The Fill-Factor Truth

Readers and colleagues frequently ask me how I learned everything I know about SQL Server. Some people assume I learned so many details by spending time with members of the Microsoft development team or from internal Microsoft documents describing the specifications for the SQL Server code. But I've learned substantially more than half of the details I know about SQL Server through more than 13 years of working with SQL Server every day—and by being challenged with student questions, which I always strive to answer on my own before going to other sources.

According to an old maxim, when you give someone a fish, you feed him for a day, but when you teach someone to fish, you feed him for a lifetime. Recently, I went on a fishing expedition to uncover an answer to a question that has popped up several times over the years. When I show you the tools I used to carry out my research and explain the meaning of the data the tools provide, you'll be able to add these tools to your own repertoire. And perhaps you can use them to perform your own research and learn how to figure out answers to the hard questions yourself.

Indexes and Fill Factor
The mystery involves SQL Server's fill factor, but before I tell you the exact question I answered, let's explore the concept of fill factor. First, you need to understand the basics of how indexes are organized. SQL Server organizes indexes as B-trees, with one page root as the starting point for index traversal. The root page might have pointers to two or more pages on the next index level, and each of those pages might have pointers to multiple pages at the next level. The last, or bottom, level of an index is the leaf level, which must maintain all the index key values in a sorted sequence. In SQL Server 2000, this sequence can be either ascending or descending order; earlier releases maintain all indexes in ascending order. In a clustered index, the leaf level is the data itself, so SQL Server stores the data in sorted order. In a nonclustered index, the leaf level contains pointers to the data.

The fill factor is a value you can specify when creating an index to tell SQL Server how full you want the index's leaf-level pages to be. You can specify fill factor in a CREATE INDEX statement or when you use a CREATE TABLE or ALTER TABLE statement to define a primary key or unique constraint on a table, because SQL Server automatically builds an index to enforce primary key and unique constraints. Of course, you can also specify a fill factor when you use Enterprise Manager to create an index or add one of these constraints to a table. If you don't specify a fill-factor value, SQL Server uses its serverwide default fill factor. To discover what the default fill factor is, you can run the command

EXEC sp_configure 'fill factor'

If you haven't changed the default value, you'll get a row of output showing that the current run_value (the value SQL Server is currently running with) is 0, as Figure 1, page 30, shows.

A fill factor of 0 means that the index's leaf-level pages will be completely full, but the index's upper levels will leave room for one or two index rows to be added. In fact, you can never explicitly assign a fill factor of 0 when building an index because 0 isn't a valid value for the CREATE INDEX command. The only way to get a fill factor of 0 is to rely on the default, if you haven't changed it. You can change the default fill factor by running the sp_configure command and adding a second parameter to serve as the new default fill factor:

EXEC sp_configure 'fill factor', 80
GO
RECONFIGURE
GO

You must stop and restart the SQL Server service for the change to take effect. The new fill factor will be in effect when you see it in the run-value column.

If you create an index with a fill factor of 80, the leaf-level pages will be 80 percent full and the upper levels of the index will have enough room left on them to allow for one or two more index rows. Note that SQL Server doesn't maintain the original fill-factor value as new rows are added to a table; the fill factor only controls how full your leaf-level pages should be when the index is initially created. SQL Server stores the initial fill factor in the sysindexes system table in a column called OrigFillFactor. If you're executing the command DBCC DBREINDEX, which rebuilds one or all indexes on a table, specifying a fill factor of 0 tells SQL Server to rebuild the indexes with the original fill factor that the index was built with, not necessarily with the default value that you configured for your server.

The fill-factor value can be important in an insert-intensive environment. An index's leaf level must maintain all the index key values in a sorted sequence, so if someone needs to insert a new row into a table, the index key value in that row determines the row's position in the index (or table, if the index is clustered). For example, if you have an index on last name, inserting a row with a last-name value of Marlin requires SQL Server to insert a new index row in the same page with the other names that start with Ma, possibly between Margolin and Martin. If the page where the new row belongs is completely full, SQL Server must split the page and link a new page into the page chain. SQL Server will move approximately half the rows from the original full page to the new page. Not only is page splitting a resource-intensive operation that can slow down the performance of your insert operations, but because the new page probably isn't physically contiguous to the original page, you introduce fragmentation into the index or table. (For a discussion of the various types of fragmentation and how to manage and remove them, see "Keep SQL Server Up and Running," December 2000.)