Wednesday, September 8, 2010

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

2 comments:

  1. What do the following numbers signify
    @Rate= -5.2312 * Power(@keybytes,-0.244)
    @Offset= 1 - 0.2193 * Power(@keybytes, - 0.462)
    and from the second formula
    @Rate=- 5.0189 * Power(@keybytes,-0.218)
    @Offset=1 - 0.9774 * Power(@keybytes, - 0.574)

    Does Key bytes mean number of bytes on the index key?

    ReplyDelete