How to check fragmentation in sql server

In this article, we will learn how to identify and resolve Index Fragmentation in SQL Server. Index fragmentation identification and index maintenance are important parts of the database maintenance task. Microsoft SQL Server keeps updating the index statistics with the Insert, Update or Delete activity over the table.

The index fragmentation is the index performance value in percentage, which can be fetched by SQL Server DMV. According to the index performance value, users can take the indexes in maintenance by revising the fragmentation percentage with the help of Rebuild or Reorganize operation.

Fragmentation percentage in sql server

Why the Index Fragmentation percentage varies?

Index Fragmentation percentage varies when the logical page orders don’t coordinate with the physical page order in the page allocation of an index. With the data modification in the table, information can be resized on the data page.

The page was top full before the update operation on the table. However, free space could be found on the data page with an update operation on the table. Users can observe the disturbing page order

check fragmentation on a table sql server

how to check fragmentation in sql server