HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Fill Factor Setting and Performance

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
settingperformancefactorandfill

Problem

From what I've read, typically for indexing the recommendation is to leave the fill factor at 70-80, and the logic behind this seems that new data may be inserted into a table between existing values thus fill factor shouldn't be set as 100. In a hypothetical example of usernames, new names may be added to the name field, so a fill factor of 100 could create a problem because, even though the pages are full, the new data won't be organized appropriately with the other data.

My question involves a table where data are organized by date (clustered index) and ID (non clustered index); each order has a date and an ID thus no "new" orders can occur between existing orders and no new IDs can appear between existing IDs - these happen sequentially. For a table like this, would it be appropriate from a performance perspective to set the fill factor to 100 and reindex at 100, meaning are there performance advantages to having the pages full since no new data will appear between existing data?

Solution

The default fill factor is zero, which is identical to 100. As per the product documentation,


The fill-factor value is a percentage from 1 to 100, and the
server-wide default is 0 which means that the leaf-level pages are
filled to capacity.

Trying to find an opitmal value for fill factor is a well-researched problem. In the described scenario, there is not much to be done unless the table doesn't use default values.

Context

StackExchange Database Administrators Q#50741, answer score: 4

Revisions (0)

No revisions yet.