Can you explain the concept of index fragmentation in a database and how it affects performance?

Index fragmentation in SQL databases is a situation where the logical order of data in the index does not match their physical placement on the disk. This occurs due to frequent insert, update, and delete operations, which lead to gaps and free spaces within index pages.

Types of fragmentation:

• Internal fragmentation — when there is a lot of free space inside index pages, reducing data storage density.
• External fragmentation — when sequential index pages are not physically adjacent, which worsens sequential read speed.

Impact on performance:

• Increased input/output operations, as data is read from different locations on the disk.
• Slower query execution, especially when scanning large ranges using the index.

To reduce fragmentation, reorganization or rebuilding of indexes is used, which improves data access speed.