In my career as software engineer and neophyte DBA, I have spent countless
hours performance tuning SQL Server and troubleshooting production bottlenecks.
I have had great success in hunting down deadlock culprits and in achieving
massive performance gains on individual stored procedures. But even in spite of
my successes, I have to admit, I have been overlooking one of the most basic
performance tuning techniques in the book – proper configuration of Index
What is an indexes “FillFactor”
An indexes fillfactor specifies the percentage that the index data pages on
disk are filled when the index is first created. An index fillfactor of 100%
will cause each index data page to be completely filled up. This is ideal from a
disk capacity standpoint as there is no wasted space with data pages that are
not fully allocated. However, this is not ideal from a SQL Server performance
perspective regarding data updates (Inserts, Updates and Deletes). If you create
a clustered index that has a fillfactor of 100%, every time a record is
inserted, deleted or even modified, page splits can occur because there is
likely no room on the existing index data page to write the change. Page splits
increase IO and can dramatically degrade SQL Server performance.
It is very easy to react by simply applying a very low fillfactor of say,
50%, to reduce page splits in a highly transactional system. The problem with
this approach is that by doing this, you have in affect doubled the amount of
data required to read and cache index information for a table. So in improving
write performance, you have potentially degraded read performance. The trick is
to find the right balance between read performance and write performance by
optimizing the fillfactor settings for each index.
Specifying the fillfactor percentage
When creating indexes in SQL Server, by default, if you do not specify an
index fillfactor, the fillfactor will be 0 (effectively the same as 100%). You
can specify an indexes fillfactor percentage in a number of different ways. Some
of these methods include: The Create Index statement, DBCC DBReindex and
database maintenance plans.
The Create Index statement in SQL Server will allow you to specify a
fillfactor setting upon index creation. The following example creates a
clustered index with a fillfactor of 80%.
CREATE CLUSTERED INDEX [ci_Product_ProductId] ON [dbo].[Product]([ProductId]) WITH FILLFACTOR=80 ON [PRIMARY]
An optimization that was newly introduced in SQL Server 2000 is the
DROP_EXISTING option in the CREATE Index statement. As part of database
maintenance, it is fairly common to write scripts that drop all indexes and then
recreate them as a means of reindexing the data and reestablishing fillfactor
settings (hence freeing up page space that has been consumed since specifying
the original fillfactor property).
Reindexing is time consuming and expensive because table locks are held while
indexing is taking place. Furthermore, dropping and recreating a clustered index
forces all the nonclustered indexes for that table to be reindexed as well. The
DROP_EXISTING clause rids us of the need for separate DROP INDEX and CREATE
INDEX pairs. Using the DROP_EXISTING option in the CREATE INDEX statement keeps
the nonclustered indexes from being rebuilt. This reduces the time required to
reindex, reduces IO and reduces unnecessary locks on the table in question.
The DBCC DBReindex command can be used in SQL Server to force Reindexing.
This command can be executed for a single index or for an entire table. This
example reindexes the index named ‘UPKCL_auidind’ in the Authors database and
sets an index fillfactor of 80%:
DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
This example rebuilds all indexes on the Authors table using a fillfactor
value of 70%:
DBCC DBREINDEX (authors, '', 70)
Database Maintenance Plans
In SQL Server, database maintenance plans can be used to manage nightly
backups, integrity checks and other miscellaneous DBA chores. One of the options
available in Database Maintenance Plan creation is ‘Reorganize data and index
pages’. With this option, you can indicate for SQL Server to change the free
space percentage for all index pages. For example, setting the free page
percentage to 10% creates fillfactors of 90% for all indexes in the database.
Although this may not be the best way to optimize all indexes in a system, it
certainly is a start.
Another index setting to be aware of is the ‘Pad_Index’ setting. When
specifying a fillfactor of 90% for an index, only the leaf level pages of the
index are originally created with 10% free space. All non-leaf level pages are
filled to the maximum level. By specifying the PAD_INDEX argument, you ensure
that non-leaf level pages are also padded with the fillfactor setting. This
example creates a clustered index with a fillfactor of 80% using the PAD_INDEX
CREATE CLUSTERED INDEX [ci_Product_ProductId] ON [dbo].[Product]([ProductId]) WITH PAD_INDEX, FILLFACTOR=80 ON [PRIMARY]
Are My Indexes Optimized?
Once you know how an indexes fillfactor can impact your server’s performance,
the next step is to perform analysis on your database to determine if your index
fillfactor settings are properly configured.
SQL Server and Windows both provide an excellent set of tools to monitor IO
performance and to indicate if fillfactor settings could benefit from
The first tool is to have SQL Server show the percentage of contiguous disk
spaced used by individual indexes by using the DBCC Showcontig utility. If
Indexes are written to contiguous pages on disk, then when reading the data, SQL
Server can use random access mode to read the data into memory. If the data is
on non-contiguous pages, SQL Server may have to resort to sequential access to
read the data. Sequential access is much slower than random access for reading
data from disk. DBCC Showcontig can be executed against the entire database, an
entire table within the database, or against a specific index within a table
(See Books On Line For specifics).
The following Example executes DBCC Showcontig for all indexes in the Employees
USE Northwind GO DBCC SHOWCONTIG (Employees) GO
The following script will execute DBCC Showcontig for the index named
‘PK_AKA’ in the AKAs Table:
declare @id int Declare @indexId int Declare @IndexName varchar(128) SET @IndexName = 'PK_AKA' SET @ID = OBJECT_ID('AKAs') select @IndexID = indId from sysindexes where id = @id and name = @indexName dbcc showcontig(@id,@indexID)
The output of DBCC Showcontig provides a plethora of useful information about
the index, including the level of fragmentation for the index data pages, the
size of the index itself (number of pages allocated for it), and the average
percentage each page is full (correlated to the fillfactor settings).
Here is the output of the above script, before and after defragging our indexes
on this database. (Prior to database reorganization, our index fillfactor was
set to 100% although that is not immediately apparent based on this data).
Before Database Reorganization
DBCC SHOWCONTIG scanning 'AKAs' table... Table: 'AKAs' (1977058079); index ID: 1, database ID: 5 TABLE level scan performed. - Pages Scanned................................: 1702 - Extents Scanned..............................: 239 - Extent Switches..............................: 1699 - Avg. Pages per Extent........................: 7.1 - Scan Density [Best Count:Actual Count].......: 12.53% [213:1700] - Logical Scan Fragmentation ..................: 50.00% - Extent Scan Fragmentation ...................: 99.58% - Avg. Bytes Free per Page.....................: 2604.3 - Avg. Page Density (full).....................: 67.82% DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After Database reorganization
DBCC SHOWCONTIG scanning 'AKAs' table... Table: 'AKAs' (1977058079); index ID: 1, database ID: 5 TABLE level scan performed. - Pages Scanned................................: 1281 - Extents Scanned..............................: 161 - Extent Switches..............................: 160 - Avg. Pages per Extent........................: 8.0 - Scan Density [Best Count:Actual Count].......: 100.00% [161:161] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 790.4 - Avg. Page Density (full).....................: 90.23% DBCC execution completed. If DBCC printed error messages, contact your system administrator. The results speak wonders to the importance of index maintenance and proper fillfactor settings.
The pages scanned value indicates the number of pages allocated to the index.
Notice, in our example, that prior to database reorganization, this index
spanned 1,702 pages. After database reorganization, the index only spanned 1,281
The number of extents scanned reports the number of different extents the above
listed indexes are written to on disk. Ideally, this number should be as low as
possible. Prior to our database maintenance, the extents scanned value was 239.
After maintenance was performed, this number dropped to 161. This indicates that
we dramatically reduced the fragmentation of our indexes on disk by moving them
to contiguous disk space.
Number of times the DBCC statement moved from one extent to another while it
traversed the pages of the table or index. Ideally this number should be as
close as possible to the total number of extents scanned. If this number is
higher than the number of extents scanned, it would indicate that the indexes
are fragmented. Notice that, prior to maintenance, our extent switches were
1,699 compared to our extents scanned value of 239 (highly fragmented). After
maintenance, our extent switches equaled 160 where as our extents scanned value
equaled 161 (zero fragmentation).
Avg Pages per Extent
The number of pages per extent in the index page chain. The higher the number
the less fragmented the indexes are. Our statistics show 7.1 compared to 8.0
before and after database maintenance.
This level indicates the level of fragmentation in the index. 100% indicates
100% density, or NO Fragmentation. Notice, again, that prior to maintenance we
were showing 12.53% Density Vs. 100% after maintenance - obviously a huge
Avg Page Density (full)
This number indicates, on average, how full the pages are for the index in
question. In our example, you will notice that prior to defragging our database,
the number was 67.82% and after defragging our database (and setting fillfactor
to 90%), our average page density is 90.23%
Performance Monitor is another invaluable tool for monitoring disk IO and
index issues. Three key counters to watch when analyzing index fillfactor
settings are discussed below:
SQL Server Access Methods: Page Splits\Sec
This counter monitors the number of page splits that are occurring in SQL Server
every second. Obviously, you want this number to be as low as possible. A high
number indicates a potential need for reducing your fillfactor percentages. An
excessively low number could mean that your fillfactor percentages are actually
TOO low. Remember, low fillfactor percentages increase the size of the database,
and increase the amount of data required by the SQL Query engine to read indexes
and table data.
Physical Disk: % Disk Read Time
This counter shows the % of disk time for read operations.
Physical Disk: % Disk Write Time
This counter shows the % of disk time for write operations.
Using the Read and Write time counters in unison will provide a good picture of
how read and write intensive your database is. Remember, in a read only
environment a fillfactor of 100% is probably appropriate, whereas in a write
intensive environment a much lower fillfactor is likely to pay dividends.
Rules of Thumb
So what should your fillfactors be set to? That is a difficult question to
answer. I believe that performance should be tightly monitored in a production
environment and indexes should be fine tuned as performance shows that changes
are necessary. The following numbers are recommended as a general starting
- If your database is read only (low number of updates), use a fillfactor
- If your database is write intensive (writes greatly exceed reads), use a
fill factor somewhere between 50% and 70%.
- If your database is both read and write intensive, start with a
fillfactor of 80% to 90%.
- As you can see, these settings depend on the use of the table. If a
specific table is only used for lookups, then its clustered index can have a
fillfactor of 100% even in an update intensive environment. Furthermore, for
indexes that consume fewer than 100 pages, the fillfactor settings are
likely to show negligible results.
Finally, index fillfactors are not automatically maintained by SQL Server.
This means that when you create the index, the fillfactor setting is honored,
but as data changes in the database, the pages will eventually fill up. I
recommend that you have a maintenance plan that reindexes on a scheduled basis
(nightly or weekly for example, depending on your needs).
When troubleshooting database performance problems, even the most careful
scrutiny of stored procedures, index placement and database blocking can be
overshadowed by incorrect fillfactor settings. Paying attention to this one
simple index configuration option can significantly increase your database
performance by dramatically reducing disk IO.