Hey guys, In this blog I am going to explain you about Fragmentation and its types and how to detect fragmentation and fix it.

Fragmentation -

SQL Server index fragmentation is a common source of database performance degradation. Fragmentation occurs when there is a lot of empty space on a data page (internal fragmentation) or when the logical order of pages in the index doesn’t match the physical order of pages in the data file (external fragmentation).

Types of SQL Server Index fragmentation –

A.       Internal Fragmentation

Internal fragmentation occurs when data pages have too much free space. This extra space is introduced through a few different avenues.

·       SQL Server stores data on 8KB pages. So, when you insert less than 8KB of data into a table, you’re left with blank space on the page.

·       Conversely, if you insert more data than the page has space for, the excess is sent to another page. It’s unlikely that the additional data will perfectly fill the subsequent pages, so you are, again, left with blank space on a page.

·       Blank space on a data page also occurs when data is deleted from a table. 

Internal fragmentation primarily causes performance issues when SQL Server does an index scan. Performance slows when SQL Server has to scan many partially filled pages to find the data it’s looking for.

B.       External Fragmentation

External fragmentation is a result of data pages being out of order. This is caused by inserting or updating data to full leaf pages. When data is added to a full page, SQL Server creates a page split to accommodate the extra data, and the new page is separated from the original page.

External fragmentation causes performance issues by increasing random I/O. When pages are not sequential, SQL Server has to read data from multiple locations, which is more time-consuming than reading in order.

Ø How to detect fragmentation –

use AdventureWorks2019

SELECT

  I.index_id, I.name,

  DM.avg_fragmentation_in_percent

FROM

  Sys.dm_db_index_physical_stats(db_id(),

  NULL, NULL, NULL, DEFAULT) DM

JOIN sys.Indexes I

ON I.object_id=DM.object_id

AND I.Index_id=DM.index_id

order by avg_fragmentation_in_percent desc

 

Ø How to fix SQL Server Index Fragmentation -

Because preventing fragmentation 100 percent of the time is impossible, it’s important to know how to fix SQL Server index fragmentation if performance is suffering.

The best place to start is using the sys.dm_db_index_physical_stats DMF to analyze the fragmentation level of your indexes. Once you know how extensive the index fragmentation is, you can plot your plan of attack with one of three solutions: rebuild the index, reorganize the index, or do nothing.

Rebuild: Rebuild indexes when fragmentation reaches greater than 30 percent and page_count is greater than 1000

USE AdventureWorks2019;

GO

ALTER INDEX PK_Employee_BusinessEntityID ON 

HumanResources.Employee

REBUILD;

GO

------------------------------------------------------

Reorganize: Reorganize indexes with between 11-30 percent fragmentation and page_count is greater than 1000

 

USE AdventureWorks2019;  

GO 

-- Reorganize all indexes on the Table

 

ALTER INDEX ALL ON HumanResources.Employee 

REORGANIZE;  

GO

 

------------------------------------------------------

Ignore: Fragmentation levels of 10 percent or less should not pose a performance problem, so you don’t need to do anything.