What is index fragmentation in sql server

Problem

While indexes can speed up execution of queries several fold as they can make the querying process faster, there is overhead associated with them.

They consume additional disk space and require additional time to update themselves whenever data is updated, deleted or appended in a table.

Rebuild index sql server

Also when you perform any data modification operations (INSERT, UPDATE, or DELETE statements) index fragmentation may occur and the information in the index can get scattered in the database. Fragmented index data can cause SQL Server to perform unnecessary data reads and switching across different pages, so query performance against a heavily fragmented table can be very poor.

In this article I am going to write about fragmentation and different queries to determine the level of fragmentation.

Solution

When indexes are first built, little or no fragmentation should exist. Over time, as data is inserted, updated, and deleted, fragmentation levels on the underlying indexes may begin to rise.

So let’s see how it happens.

When a page of

how to check fragmentation in sql server

how to check fragmentation percentage in sql server