Check Fragmentation and Fix Fragmentation In SQL Server

The fragmentation is one of the most common issues that the DBAs face while working on the production environment. The fragmentation can play a vital role in query performance. Personally speaking, fragmentation can simply freak out on big tables and can sometimes be hectic to deal with. However, let’s learn about the fragmentation in detail.

Fragmentation: To make the definition much simpler, the fragmentation is something that is broken up. In SQL Server, the fragmentation means that the data stored on the disk is in the form of non-contiguous. Additionally, we have three types of fragmentation that are diversified:

1. Internal Fragmentation: Internal fragmentation happens when the records on the page are stored in a non-contiguous manner. Basically, this is encountered when there are huge DML commands (INSERT, UPDATE, and DELETE) happening on the table and the underlying indexes. As there are more modifications happening on a table, which eventually affects the page to unequally filled. This scenario can cause leveraging issues.

2. External fragmentation: External fragmentation happens when the extents are stored in a non-contagious manner. This is basically on a disk where the table extents are not stored in an ordered format. When the table extent pointers are scattered on the disk from one place to another, there can be degraded in performance due to high disk rotations.

3. Logical Fragmentation: Logical fragmentation happens when every page containing its own logical sequence is disrupted. Every page has a pointer at the leaf level, and they point to the next page. The logical sequence that is out of order fall under this category.

To check the fragmentation on a single database but for all the tables:

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'Your DB Name'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

My DB name here: Distribution

To check the fragmentation on a single database and for a single table:

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N' Your DB Name');
SET @object_id = OBJECT_ID(N'Your Table Name');
IF @object_id IS NULL
BEGIN
PRINT N'Invalid object';
END

ELSE

BEGIN
SELECT IPS.Index_type_desc,
I.NAME,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'DETAILED') AS IPS
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC

END

My DB name here: Distribution

My Table name here: One of the distribution table

From the results, you may notice two important key columns as ‘avg_fragmentation_in_percentage’ and ‘avg_page_space_used_in_percentage.

1. avg_fragmentation_in_percentage: It represents the external fragmentation that we discussed earlier. The lower the value the higher is the performance.

2. avg_page_space_used_in_percentage: It represents the internal fragmentation that we discussed earlier as well. The higher values mean that the pages are more filled at each page level. The higher values mean that it's going to perform better.

Fix the fragmentation:

There are two partitions made while fixing the index fragmentation. The indexes with fragmentation percentage below 30 and the other with a percentage above 30.

If < 30 percent fragmentation levels: Perform a REORG operation on the index to reorder the logical sequence.

Query:

Single index reorg:

ALTER INDEX [your index name] ON [dbo].[your table name] REORGANIZE

Reorg for all indexes in a table:

ALTER INDEX ALL ON [your table name] REORGANIZE

Note: Performing an ALTER INDEX ALL has some conditions behind, wherein it works only on statistics related to the index. Manual and Automatic statistics created are not updated on the table.

If > 30 percent fragmentation levels: Perform a REBUILD operation as the fragmentations are high enough that a REORG operation won’t really help in fixing the issue.

Query:

Single index rebuild:

ALTER INDEX [your index name] ON [dbo].[your table name] REBUILD

Single index rebuild with ONLINE option:

ALTER INDEX [your index name] ON [dbo].[your table name] REBUILD WITH (ONLINE = ON)