How to detect fragmented Indexes and rebuild or re organize to de fragment indexes.?

Problem: How to detect fragmented Indexes and rebuild or re organize to de fragment indexes.?

Solution: Reorganize and Rebuild Indexes

When there are inserts/updates happening on a table page splits are roughly done by SQL Server, the logical order of the index may not match the physical order of the index. This can cause Indexes to be fragmented.

for remediation this the indexes which are built needs to be rebuild or reorganized.

Rebuild is the same as dropping and creating the index by doing so the disk can get rid of the free spaces in between the pages.

Reorganizing is a low maintenance system resources. It de-fragments the leaf level of clustered and non-clustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

The following index related dynamic management view gives the information related to the fragmentation, to know more about this please go through the article Detect index fragementation.

After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.

avg_fragmentation_in_percent value	Corrective statement
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

Example :

Example 1:

In this example, I would like to show how we can re-build an index.

Before doing index fragmentation I chose top two average fragmentation percentages:

table_name	index_name	index_type_desc	index_level	avg_fragmentation_in_percent	avg_page_space_used_in_percent	page_count
Address	ui_uAddress	NONCLUSTERED INDEX	1	100	36.98048	5
Address	ui_uSubject	NONCLUSTERED INDEX	1	80	37.0126	5

Below is the image of the indexes on the table.

I am using rebuild partition all as both the indexes on the table have fragmented more than 80 percent. And when all is specified it rebuilds all the indexes(drops and creates)

ALTER INDEX [ui_uAddress] ON [dbo].[Address] REBUILD PARTITION = ALL

                WITH (

                                                PAD_INDEX = OFF,

                                                STATISTICS_NORECOMPUTE = OFF,

                                                SORT_IN_TEMPDB = OFF,

                                                IGNORE_DUP_KEY = OFF,

                                                ONLINE = ON,

                                                ALLOW_ROW_LOCKS = ON,

                                                ALLOW_PAGE_LOCKS = ON

                                                )

GO

And this is how the fragmentation looks when I rebuild the indexes on the table.

table_name	index_name	index_type_desc	index_level	avg_fragmentation_in_percent	avg_page_space_used_in_percent	page_count
Address	ui_uAddress	NONCLUSTERED INDEX	0	1.639344	NULL	1220
Address	ui_uSubject	NONCLUSTERED INDEX	0	0.607112	NULL	1153

Example 2:

I would like to show how we can improve the indexes with re organize.

Before:

table_name	index_name	index_type_desc	index_level	avg_fragmentation_in_percent	avg_page_space_used_in_percent	page_count
Employees	AK_Name_PhoneNumber	NONCLUSTERED INDEX	1	25	73.98073	24
Employees	ix_name_phonenumber	NONCLUSTERED INDEX	1	18.75	66.70991	16

  

To Re-orgranize the index the with lob_compaction means it compacts (LOB) data types: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can reduce the data size on disk.

ALTER INDEX [ix_name_phonenumber] ON [dbo].[Employees] REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

ALTER INDEX [AK_name_phonenumber] ON [dbo].[Employees] REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

After:

table_name	index_name	index_type_desc	index_level	avg_fragmentation_in_percent	avg_page_space_used_in_percent	page_count
Employees	AK_Name_PhoneNumber	NONCLUSTERED INDEX	0	0.354758	NULL	4792
Employees	ix_name_phonenumber	NONCLUSTERED INDEX	0	0.277701	NULL	3601

To rebuild, re-organize indexes from SQL server management studio, you can go through the index and right click on the rebuild/ re organize.