2.Fragmentation & Maintenance

2.1 What is Fragmentation?

• Caused by DML actions
• Creates empty spaces in the B-Tree known as memory bubbles
• Page splitting can also cause fragmentation when data needs more room than allowed in a single leaf node

2.2 Types of Fragmentation

2.2.1 Internal Fragmentation （memory bubbles)

• Leaf nodes not filled to fullest capacity due to memory bubbles
• Focus on Balance Tree and data inside
• If there is a NCI on a CI, then the memory bubbles on the CI will cascade up through the NCI
• in page, there are have space (only in uodate,insert)
• only can rebuild and reorganize

If a leaf level page in an index is not filled completely or to its capacity, it is called a memory bubble or internal fragmentation. When there are some DML operations on the table with indexes, SQL Server has to split the page or reassign a new page (based on type of DML) to make sure that the data is sorted as per the indexing column, that causes internal fragmentation.

internal

2.2.2 External Fragmentation

• Logical order does not match Physical in HDD
  eg:page 101 and page 201 (the index are sequent)

• Similar to typical Fragmentation in HDD’s

• Causes serious issues due to increase in Fetch operations which must search for data in the HDD

• only can rebuild to solve it

When the logical order of the data doesn't match with physical order of the data. That is the data in the pages are not in sequence. Page split is one of the reasons along with unavailability of immediate page for data load.

external external

2.3 Find Fragmentation Levels

2.3.1 GUI

• Right click and check the properties of index, on the right column there’ll be Fragmentation

2.3.2 DB Console Command

• DBCC showcontig(‘tbl’,Index)
  with all_indexes,tableresults,all_levels
• Find info displayed about the indexes on the table

2.3.3 Dynamic Management Views

• Using a system view that has dynamic values we search for two columns

sys.dm_db_index_physical_stats ( 
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

• Modes : Sample / Detailed

2.4 Maintenance on Indexes

2.4.1 Reorganize

• Easier and less resource heavy
• Fixes the physical ordering of leaf pages and compresses data to fix empty spaces
• If canceled mid way, it will stop and end where it’s at. No rollback is taken
• External Guidelines
  5% - 30%
  ------------------5% space not match
• Internal Guidelines
  95% - 70%
  ------------------95% empty space to regonizae

2.4.2 Rebuild

• Heavier and more resource consuming
• Creates an entirely new Balance Tree structure for the index
• = Dropping old / + New
• If canceled mid way, it will roll back its operations
• External Guidelines
  30% - 100%
• Internal Guidelines
  70% - 0%
  eg:除非万不得已，采用rebuild

reorganize 就是把memeor bubble挤走， rebuild就是把page重新排序

2.5 Additional Options for Rebuild

2.5.1 Fillfactor

• Give the percentage of space to be allocated for data in the leaf pages for the rebuild
• only leaf level

2.5.2 Pad_Index

• Apply the percentage for fill factor to all levels of the Balance Tree

2.5.3 Online

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-2017

• Allows for the data in the underlying table to still be queried and updated as the index is created
• store index in ram?

2.5.4 Statistics_Norecompute

• Keep the original stats
• 没有变化

2.5.6 Sort_In_TempDB

• Create using TempDB

2.6 note

• page density: how fully storage in page
• index depth:The index depth is the number of levels in the tree structure that comprises the index, numbered from 0 at the leaf level (the bottom of the tree, imagining a fir tree) and increasing by 1 up to the root page of the index (the tip of the tree, imagining a fir tree)
  https://www.itprotoday.com/increase-fan-out-reduce-index-depth