FRAGMENTATION CONCEPT IN SQL SERVER DBA

Fragmentation:

• Storing data non-contiguously on disk is known as fragmentation.
• We have 2 types of fragmentation.

1. Internal fragmentation
2. External fragmentation

1.Internal Fragmentation:

• When records are stored non-contiguously inside the page, then it is called internal fragmentation.
• In other words, out of 8kb page data, pages left empty not filled properly inside.Due to this 4 pages data stored in 8 pages.This is Internal fragmentation.
• This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE) that are made on the table and therefore, the indexes defined on the table.
• Since these changes are not equally distributed among the rows of the table and indexes, the fullness of each page can vary over time.
• This unused space causes poor use of cache and more I/O, which eventually led to the poor performance of queries.

2.External fragmentation:

• My data earlier stored in one extent,due to shrinking operation or recompilation my required data goes to 10th extent instead of 1st extent.
• While searching it has scan all 10 extent pages.This is called external fragmentation.

FIND THE FRAGMENTATION BY USING COMMANDS:

1.DBCC SHOWCONTIG:It shows you how fragmented a table.

syntax: dbcc showcontig(tablename)

• This feature will be removed in a future version of Microsoft SQL Server.
• Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. 

2.Use sys.dm_db_index_physical_stats instead:
Find the fragmentation by using DMV commands:

select * from sys.dm_db_index_phisical_stats

This DMV will show all indexes information on server including system databases.

As well as other information, there are two important columns for the detection of fragmentation, which are:

1.avg_fragmentation_in_percent: This is a percentage value that represents 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.
2.avg_page_space_used_in_percent: This is a use of the average percentage of pages representing the internal fragmentation.
The higher the value, the better it is. If this value is less than 75%, corrective action must be taken.
Reducing fragmentation:

Reducing fragmentation in a Heap:

• To reduce the fragmentation of a heap, create a clustered index on the table. 
• Creating the clustered index, rearrange records in order, then place the contiguous pages on disk.

Reducing fragmentation in an index: 

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

• The type reorganize defragmentation uses the DBCC INDEXDEFRAG  to reorganize pages leaves the index up.
• This process is similar to a sort of bubble.
• Although the pages are rearranged physically, they may not be contiguous in the data file. 
• This problem can cause interleaved indexes, which need to be rebuilt to store in contiguous pages.
• Then use ALTER INDEX  ALL ON TABLENAME REORGANIZE
• This declaration is the replacement for DBCC INDEXDEFRAG  to reorganize the leaf pages of the index in a logical order.

2.Rebuild:(If avg_fragmentation_in_percent> 30%)

• The rebuild defragmentation type uses the DBCC DBREINDEX command to rebuild the indexes on the tables. The rebuild operation creates new, contiguous pages. 
• SQL Server 2005/2008 allows the option to Rebuild Online, which allows access to the tables before the operation is finished.
• However, choosing to rebuild online requires more resources (disk space, CPU, memory), and may slow performance.
• Then use ALTER INDEX ALL ON TABLENAME REBUILD: This is a replacement for DBCC DBREINDEX to rebuild the index online or offline. 
• In this case, we can also use the drop and recreate method index.