What is page fragmentation in SQL Server?

In SQL Server, page fragmentation is when data pages are not stored in contiguous order on disk. This happens when data is inserted, updated or deleted from a table, or if the table is dropped and recreated. Fragmentation can cause performance issues because SQL Server has to do extra work to read data that is not stored contiguously.

It’s like a jigsaw puzzle. When the pieces are in order, the puzzle is easy to put together. But if the pieces are all mixed up, it is much harder to put the puzzle together. In the same way, if data pages are not stored in contiguous order, SQL Server has to do extra work to read the data.

Try it yourself, I made 2 examples below with 30 rows each with green, red and yellow blocks. The blocks represent a page. For a query to complete, SQL needs to find the row with 5 yellow blocks. Which figure is easier to find the page?

Preventing Fragmentation

You can use the FILLFACTOR option when creating or altering an index to prevent page fragmentation. This option specifies how full SQL Server should try to make each page when data is inserted.

Choosing the Right Fill Factor

There is no one-size-fits-all answer to choosing a FILLFACTOR. The best FILLFACTOR will depend on the workload and data. A good starting point is to set the FILLFACTOR to 80% or less. This leaves some free space on each page, which can help prevent page fragmentation. The fill factor can be changed later on if you notice that page fragmentation is causing performance issues.

Not sure what a page is? I wrote a deep dive on SQL Pages in 2019 if you are interested in learning more about pages.