Tip: Check Your Fill Factor

Posted: January 22, 2009 in Sqlserver


Really good article on fill factor from
Tip: Check Your Fill Factor
Say you have a glass completely filled with water, and you try to put more water in that glass. What happens? The water overflows.
It’s like that with SQL Server. Whenever a new row is added to a full index page, SQL Server moves about half the rows to a new page to make room for the new one. This is known as a page split. Page splits make room for new records, but they also take time and are very resource intensive. And they can cause fragmentation, which may adversely affect I/O operations. So how can you avoid them?
To prevent such situations, you must proactively determine the fill-factor value. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder for future growth. For example, configuring a fill-factor value of 60 means that 40 percent of each leaf-level page will be left empty to provide for index expansion as data is added to the underlying table.
The default fill-factor value is always 0, which is OK for the majority of situations. Basically, a fill factor of 0 means that the leaf level is filled almost to capacity, but some space is left for at least one additional index row. (Note that a fill factor of 0 and 100 are similar.)
You can set the fill-factor value for individual indexes during a CREATE INDEX or ALTER INDEX statement, or you can configure this value directly at the server level so that any new indexes created will use the default.
The following example sets the fill-factor value at the server level to 70 percent, meaning you will have 30 percent free space for future expansion. Of course, you must carefully test this option before implementing it in a production environment.

 USE Master; GO SP_Configure 'show advanced options',1; GO SP_Configure 'Fill Factor', 70; GO --You must restart SQL Server Engine for changes to take effect.

What if you want to configure the fill factor at the individual index level? Assume you are building the following table and you would like to create a unique index on the column called Col_A with a fill-factor value of 70. The command would look like this:

 --Create an Item table USE Item_DB; GO CREATE TABLE ITEM (Col_A Varchar(100),Col_b Varchar(200)); GO;  --Create a unique index on colum Col_A of Item table with a Fill Factor value of 70 CREATE UNIQUE INDEX AK_Index ON Item (Col_A) WITH (FillFactor = 70); GO
How do you identify the fill factor for each index? You can query sys.Indexes to get the fill-factor value for all the indexes in a database, like so:

 USE Item_DB; GO SELECT Fill_Factor FROM Sys.Indexes WHERE Object_id=object_id('item') AND name IS NOT NULL; GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s