A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. 

Database indexing highly depends on the index fragmentation. 

Index fragmentation refers to the condition where the logical order of pages in an index does not match the physical order on disk. This can lead to slower performance, especially for large indexes or queries that scan ranges of data. 

📊 How Fragmentation Affects Performance 

• More disk I/O to read index pages. 

• Slower range scans and seek operations. 

• Poor cache utilization (because of wasted space). 

• Especially noticeable on large tables and during backups/rebuilds. 

To overcome this fragmentation, we have an SQL job with the following details: 

Job: Index Maintenace Plan.rebuild index 

Job Frequency: Each Friday at 2AM 

Job History: 

This job works on the below maintenance plan: 

Sample code within maintenance plan: 

ALTER INDEX [index_name] ON [dbo].[table_name] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) 

In Simphony application, we do not see the fragmentation above the threshold generally, FRIDAY one time job run is doing the work well.