Understanding index maintenance

Over time, indexes get fragmented due to write (UPDATE, DELETE, and INSERT) operations on the table on which indexes exist, which causes read operations to slow down as the SQL server now must scan a greater number of pages in the index due to the fragmentation. Therefore, we must keep track of the indexes used and the fragmentation in the indexes and should address the fragmentation to keep indexes healthy.

Now, this brings us to the topic of identifying fragmentation in indexes and to address fragmentation in indexes to keep them healthy. So, let’s discuss index health and defragmenting the index next.

Index health

To identify fragmented indexes, we must check all the indexes where the average fragmentation is more than zero. To get the list of fragmented indexes, use the following standard query, which uses the catalog views to read the system data:

SELECT OBJECT_NAME(IND.OBJECT_ID) AS [Table Name],
IND.NAME AS [Index Name], PS...