Watch your Fill Factor

I was asked to troubleshoot an issue today with a 3rd party web application that was throwing some SQL Timeout exceptions while attempting to insert data into a database.

With the exception of long running queries, most timeout errors are caused by a few issues:

• Runaway transaction logs
• High fill factors
• Missing clustered indexes

While I could probably write entire articles on each of these, I'm going to concentrate on the fill factors.

First, it's important to know that SQL Server itself does not have any timeouts; rather timeouts are implemented by the data provider (the .NET SQL providers) or the client (query analyzer, etc).

So what exactly is the fill factor? The fill factor is a parameter you set on indexes (both clustered and non-clustered); it controls the percentge of free space to leave on each page to leave room for future growth. Lower fill factors leads to less page splits. So why is this important? Well, page splits are expensive - they take a lot of CPU and disk resources to complete. So if you have a busy DB (lots of inserts into a table), and a high fill factor, then SQL server will have to constantly split pages to accomodate the growth, which might take longer then your timeout, resulting in a timeout error!

According to MSDN documentation (http://msdn2.microsoft.com/en-us/library/aa933139(SQL.80).aspx):

"Fill factor value is a percentage from 0 to 100 that specifies how much to fill the data pages after the index is created. A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space. This setting is more appropriate when there will be changes to the data in the table."

In my particular case, I was able to resolve the timeout issue by changing the fill factor from 90% to 40%. Why 40% you might ask? The customer did not have overly large amounts of storage and decreasing the fill factor increases storage requirements - I could have set this to 0 but I didn't want to have an out-of-space condition to deal with, so 40% seemed reasonable to me.

Setting the fill factor via script is relatively easy and involves dropping the constraint and re-adding it with the new fill factor.

ALTER TABLE dbo.MyTableName
DROP CONSTRAINT PK_MyTableName
GO
ALTER TABLE dbo.MyTableName ADD CONSTRAINT
PK_MyTableName PRIMARY KEY CLUSTERED (
MyTableNameID
) WITH FILLFACTOR = 40 ON [PRIMARY]

Just something to watch out for when designing your DB's!