The Forgotten Fill factor Optimize Performance Minimize Maintenance Reduce Problems
About Me • SQL Server MCT • Member of the Belgian Microsoft Extended Expert Team
What is a Fillfactor Optional INT value Specifies empty pages in an Index Server-wide default is 0
Visual GoodFill Factor Bad Fill Factor
Advise or Help? • The result of this is: • Try and Error • “Forget” it • “Default” it • “Maintain” it • No/Bad Indexes Some exception examples: Very good whitepaper by Ken Lassesen Good blog post by Pinal Dave
Why should we care? • Increased Read Times • Increased Write Times • Increased CPU usage • Index becomes useless • Full Table Scans • Index Maintenance comes under pressure • Bad balance between main resources • ! Compression Increases the problem !
Let’s focus on Read Time • Serial Read times: 0,5 ms • Random Read times: Between 3,5 and 15 ms • 1 non serial read, delays read speed with 13% • 98 * 0,5 + 2*3,5 = 56 ms vs. 50 ms. • (98 serial reads, move to the disjoint one+ move back) • Less important with SSD’s!
Reads - Let’s use some Math I Cost of Fragmentation (100 Pages) Cost of higher Fill Factor (100 Pages)
Let’s Focus on Write Times • Similar behavior – Depends on Buffer • Time to write to one extent : 0,3 ms • Sequential write is 70% faster then Random in Throughput • Impact is even bigger on SSD’s • The larger the subset, the bigger the impact
Real Life Examples (Only) Fill Factor Tuning No tuning (Only) Fill Factor Tuning
Prerequisites • We will focus on Read times • Focus on OLTP Databases • All other parameters will benefit as well • But increase is more difficult to calculate • Is to dependent from incalculable parameters • Focus • Highest possible Fill factor • Lowest possible Fragmentation • Highest possible Page fill ratio • Acceptable Maintenance
Narrow down the problem indexes • Only tables that are large enough (> 8MB) • Skip all Indexes where first Key is Monotonically increasing • Focus on: • Default Fill Factor • High Fragmentation • Average Fragmentation, High Page fill • Low Fragmentation, Low Page fill • Fill Factor < 100, Read Only Partition
The Easy ones…Single Key Indexes - Monotonic • Monotonic Increasing Keys • Identity • Timestamp • Rowversion • Careful with • Date • Only if not assigned by code, but assigned by function • Rows containing extendable data types • If these fields are updated and become larger, a page split will occur • Fill Factor should always be 100% • Empty pages will never be used
The others…Will Have Fragmentation And will need maintenance!
First Actions (Read as Quick-Fix) • Use Table Partitioning where possible • Enterprise & • Not every partition needs the same fill factor • single_partition_rebuild_index_option • Offline! • Use filegroup/Database growth as initial guess • Backup’s will give you an initial figure • You will need exact figures later on though • Narrow down to the problem Indexes • Write a sys. Query to find: • Schema Name; Table Name; Index Name • Key size, Index Size • Fragmentation, Page Space Usage • Current Fill Factor • ! Partition !
Calculate possible Fill Factors • What’s needed • Key Size • How • FLOOR(8060 / Key Size) = # Possible values • Calculates the array with possible FF Values • Example • Int key (4 bytes) => 2015 possible FF values • Only 100 are available for usage
Key Indexes – (Semi)-Sequential • Because they sometimes behave predictable • We can still use statistics • Calculate possible fill factors • Estimate the fragmentation likeliness • Plot the possible fill factors vs. the Fragmentation likeliness • Achieve low fragmentation without wasting to much space.
Key Indexes – (Semi)-Sequential • Sequential, but non unique • Semi-Sequential, but unique • Semi-Sequential, Non unique • Key Features • High Selectivity. • Gets inserted out of order with small derivations. Or has multiple entries for the same key. • Behaves predictable (Gaussian)
Calculate Max Possibility of fragmentation • Non unique sequential • Sample the Key values • Select Count(Key),Key From Table Group by Key Order by Count(Key) Desc • Returns the maximum possible occurrence (Collisons) • Unique Semi-sequential • Do we have a sequential Key? • Use it to find the max out of sequence key values (Read Fragmentation) • Else • Do we have the out of sequence probability ratio? • Can be used as initial growth ratio • Treat it as random
Simpel SequentialCalculations • (8060)/([KeySize]) = #Entries/page • Be carefull with • nullabledatatypes • Expandable Datatypes • [KeySize]*[MaxOccurence] = MaxEntriesPerKey • ([KeySize]*[MaxOccurence])*(1-MaxFragmentation)=BestEntriesKey
Indexes – Random / Chaos • Now it gets interesting • We need Index data, to tune the index itself • Key Indicators (no points for guessing) • The current fill factor • Capability to cope with randomness & growth • Current Index Page fill ratio • Current page usage • Effectiveness of growth prediction • Current index Fragmentation level • Indication of the real randomness & Growth • Current Table Growth ratio / Maintenance interval • Amount of Rows as this influences the growth ratio • Data type of the first column of the Index • Example GUID vs. Int
Never Forget • Page fill ratio • Indication of effectiveness • Fill Factor improves Insert speeds • But • Is badly used if the forseen space isn’t used! • If badly used will • Increase read times • Increase storage usage • Decrease performance • Optimise for insert • Be carefull with rebuilds • Partition • Optimise for read