Calculating Optimal Fill Factors for SQL Server Indexes

Tonight I gave a presentation on SQL Server to the Bellingham .Net group that was well received.  The PDF of the PPT is at: http://lassesen.com/msdn/ProSQLServerTalk.pdf for those that missed the talk.

 

There are two formulas that I gave which I thought deserved a recap.

The first formula gives the optimal value for a batch setting where you are wishing the maximum throughput to occur for inserts. At the start, the throughput will be very high and then drop off as fragmentation takes over.

 

CREATE FUNCTION [dbo].[Fn_OptimalThroughputFillFactorForRandom]
(
    @KeyBytes float,
    @GrowthPercentage float
)
RETURNS int
AS
BEGIN
    If @KeyBytes < 2
        SET @KeyBytes=2
If @GrowthPercentage > 0.06
        SET @GrowthPercentage = 0.06
If @GrowthPercentage < 0.001
        SET @GrowthPercentage = 0.001

     
    DECLARE @FillFactor float
    DECLARE @Rate float
    DECLARE @Offset float
    Set @Rate=- 5.2312 * Power(@keybytes,-0.244)
    Set @Offset=1 - 0.2193 * Power(@keybytes, - 0.462)
    Set @FillFactor= CEILING(100 * 
(@Rate * @GrowthPercentage + @Offset))
    If @FillFactor < 50
        SET @FillFactor=50
    If @FillFactor > 99
        SET @FillFactor=99
    RETURN @FillFactor
END

The second formula is when you wish to insure the best worst-case performance, typically seen on interactive systems. You want the response to be consistently good. This formula will have lower throughput at the start, but the level of throughput  is maintained relatively consistently until the end of period (when the index is defrag).

Create FUNCTION Fn_OptimalThresholdFillFactorForRandom
(
    @KeyBytes float,
    @GrowthPercentage float
)
RETURNS int
AS
BEGIN
    If @KeyBytes < 2
        SET @KeyBytes=2
If @GrowthPercentage > 0.06
        SET @GrowthPercentage = 0.06
If @GrowthPercentage < 0.001
        SET @GrowthPercentage = 0.001

DECLARE @FillFactor float
    DECLARE @Rate float
    DECLARE @Offset float
    Set @Rate=- 5.0189 * Power(@keybytes,-0.218)
    Set @Offset=1 - 0.9774 * Power(@keybytes, - 0.574)
    Set @FillFactor= CEILING(100 *
(@Rate * @GrowthPercentage + @Offset))
    If @FillFactor < 50
        SET @FillFactor=50
    If @FillFactor > 99
        SET @FillFactor=99

    RETURN @FillFactor 
END
GO

As you will note, the fill-factor should never be set below 50%.