CONCEPT: MORE INFORMATION ABOUT FRAGMENTATION - MYSQLDOG

To detect The fragmentation two important columns are there.

avg_fragmentation_in_percent: 

• It is a percentage value that mensions external fragmentation.
• For a table and leaf cluster level index pages, this is the logical fragmentation, whereas heap, it is externalfragmentation.
• The higher this value is, the better it is. If this value is greater than 10%, corrective action must be taken.

avg_page_space_used_in_percent: 

• Representing the internal fragmentation, This mension use of the average percentage of pages. 
• The higher the value, the better it is.corrective action must be taken, If this value is less than 75%.

Reducing fragmentation:

Reducing fragmentation in an index: 

Reorganize:(If avg_fragmentation_in_percent> 5% and <30%)

• This is kind of like a bubble.
• The reorganize defragmentation type uses DBCC INDEXDEFRAG to rearrange pages that exit the index.
• Although the pages are physically reorganized, they may not be contiguous in the data file.
• This problem may cause interleaved indexes, which must be rebuilt for storage on contiguous pages.
• Then use ALTER INDEX ALL ON TABLENAME REORGANIZE
• This statement replaces DBCC INDEXDEFRAG to rearrange pages in index sheets in a logical order.

Rebuild:(If avg_fragmentation_in_percent> 30%) :

• However, choosing rebuild online requires more resources (disk space, CPU, memory) and can decrease performance.
• This is a replacement for DBCC DBREINDEX to rebuild the index offline or online.Then use ALTER INDEX ALL ON TABLENAME REBUILD: 
• In this case, we can also use the release method index and recreate.