Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Who Cares about FillFactor?

By Gregory Jackson,

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.

Total article views: 24965 | Views in the last 30 days: 24
 
Related Articles
FORUM

fillfactor

fillfactor

FORUM

Indexes & FillFactor

I am not a DBA but play one at work and not very well. I have a very large DB (1 billion rows) i...

ARTICLE

The FILL FACTOR

Will the FILLFACTOR Index option help to boost my database performance?

FORUM

Changing FILLFACTOR by Updating sys.indexes

Is is possible to update the FILLFACTOR in the sys.indexes table so that the next rebuild will use t...

ARTICLE

Stairway to SQL Server Indexes: Level 4, Pages and Extents

We've now seen how indexed and non-indexed tables perform in queries, and established "logical reads...

Tags
performance tuning    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones