Introduction

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

FillFactors.

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.

Create Index

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]

DROP_EXISTING

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.

DBCC DBReindex

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.

PAD_INDEX

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

option:

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.

Analysis tools

SQL Server and Windows both provide an excellent set of tools to monitor IO

performance and to indicate if fillfactor settings could benefit from

optimization.

DBCC Showcontig

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

table:

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.

Result Summary:

Pages Scanned

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

pages.

Extents Scanned

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.

Extent Switches

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.

Scan Density

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

improvement.

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%

Perfmon Counters

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

point:

• If your database is read only (low number of updates), use a fillfactor

  of 100%.

• 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.

Index Maintenance

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).

Summary

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.