1 / 30

The Forgotten Fill factor

The Forgotten Fill factor. Optimize Performance Minimize Maintenance Reduce Problems. About Me. SQL Server MCT Member of the Belgian Microsoft Extended Expert Team. Purpose of this session. What is a Fillfactor. Optional INT value Specifies empty pages in an Index

hollis
Download Presentation

The Forgotten Fill factor

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related