The Forgotten Fill factor

1. The Forgotten Fill factor Optimize Performance Minimize Maintenance Reduce Problems

2. About Me • SQL Server MCT • Member of the Belgian Microsoft Extended Expert Team

3. Purpose of thissession

4. What is a Fillfactor Optional INT value Specifies empty pages in an Index Server-wide default is 0

5. Visual GoodFill Factor Bad Fill Factor

6. Impact on Performance

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

8. 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 !

9. Nowlets start the dive to level 400

10. 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!

11. Reads - Let’s use some Math I Cost of Fragmentation (100 Pages) Cost of higher Fill Factor (100 Pages)

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

13. Real Life Examples (Only) Fill Factor Tuning No tuning (Only) Fill Factor Tuning

14. Let’s start the calculations

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

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

17. Identifyyour index Keys per type

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

19. The others…Will Have Fragmentation And will need maintenance!

20. 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 !

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

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

23. 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)

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

25. Simpel SequentialCalculations • (8060)/([KeySize]) = #Entries/page • Be carefull with • nullabledatatypes • Expandable Datatypes • [KeySize]*[MaxOccurence] = MaxEntriesPerKey • ([KeySize]*[MaxOccurence])*(1-MaxFragmentation)=BestEntriesKey

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

27. Calculate max supported growth ratio for a specific fill factor

28. Demo TimeLet’s go Interactive

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

30. Time forDecompression