Index Fragmentation and Page Splits in SQL Server

What is Page Splits in SQL Server?

As it is known, there are 8KB areas called Page in the file base of SQL Server and 8 of them (8 X 8K = 64K) constitute an extent.

Data and Indexes are stored on pages and Read / Write operations take place on extent basis. How many rows will be on each page may vary depending on the length of the row. If a long row is inserted or the current row is updated with too long data, SQL Server can replace existing rows to make changes. If the change does not fit on the page, SQL Server creates a new data page and moves the rows on this new page. This process is called page split.

Page Split operation can sometimes cause performance problems in live systems.

Monitor Page Split on Perfmon

So how do we monitor Page Split on Perfmon? Here, we can capture Page Splits by using Perfmon tool in such performance problems.

Let’s create our sample table.

Now let’s open Perfmon. Then we press the (plus) sign and add Page Splits / sec in SQL Server: Access Methods.

Then we run our INSERT block below.

Let’s turn on the monitor while the insert continues.

This is how we capture the jump of Page Split / sec on Perfmon.

Now let’s observe Index Fragmentation on Index.

What is Index Fragmentation in SQL Server?

We created our index. And for a while, we performed update, delete and insert operations on the table continuously.

Suppose we perform a deletion. We have deleted some data from one page of the index. Then we wanted to insert another record. And since there is not enough space on the page where we deleted the record, sql server wrote this record to a new blank page.

In this way, indexes start to be stored scattered on the disk over time and fragmentation increases. As the fragmentation increases, the performance of the index decreases. You can set a fill factor on the index to prevent the indexes from being fragmented quickly on the tables containing intense Update, Insert and Delete.

Index Fragementation Script in SQL Server

As it seems, the index named CIX_PageSplit_Table_col_1 is fragmented at a rate of 99.98%. This means that our index is now scattered. In other words, Index has reduced its own performance too much.

Let’s observe the difference between statistics used by our queries processed with fragmented index and, healthy indexes.

Here we get the statistics results;

Now let’s prepare a demo from the same table but with the Index not fragmented.

We created our second demo table in the same way and inserted the same data. Now let’s check the Fragmentation rates of the Indexes in these two tables.

As you can see, our first index is fragmented at 99.98%, while our second index is fragmented at 0.44%. Now let’s run the same SELECT operation on both tables and observe the difference in performance.

Statistics:

As you can see, Index exposed to Page Split does 10041 logical Read and index not exposed to Page Split does 5024 logical read.

So, unfragmented index seems almost 2 times faster.

Therefore, in your systems, be sure to check the Fragmentation values ​​of your Indexes and monitor the Page Splits.

Resolve SQL Server Index Fragmentation

In such cases, we must perform Reorganize or Rebuild operations according to the Fragmentation values of Indexes. Since the Fragmentation of our index exposed to Page Split is very high, we will apply REBUILD process.

After REBUILD operation, let’s look at the Fragmentation value of our Index again.

As you can see, our index is fixed. In such cases, we need to apply Index maintenance periodically.

Now, let’s run our SELECT script on both tables again and check the statistics.

Statistics:

As you can see, the values ​​are the same. In fact, the performance of our scattered Index even better than the other after rebuild operation.

You may want to read “Index Concept and Performance Effect On SQL Server” and “Statistic Concept and Performance Effect On SQL Server“.

Good luck with.

Yusuf KAHVECİ
Author: Yusuf KAHVECİ

Leave a Reply

Your email address will not be published. Required fields are marked *