In this article, we will explore the concept of bad page splits in SQL Server and how they can affect CPU and IO performance. We will use a specific use case to illustrate the impact of bad page splits on the data stored in the leaf pages of a clustered index.

For this analysis, we will be using the Sales.SalesOrderDetail table from the AdventureWorks2017 database. The clustered index of this table is based on the SalesOrderID and SalesOrderDetailID columns. We will focus on a scenario where we insert a row into an already filled page with a fill factor of 100%.

To begin, we will ensure that the clustered index is fully packed by rebuilding it with a fill factor of 100%. This can be done using the following code:

ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID 
ON Sales.SalesOrderDetail REBUILD
WITH(DATA_COMPRESSION = NONE, FILLFACTOR = 100)

Next, we will search for a row on a leaf page that we can delete and then rebuild the index to add that row back into the table. This will simulate a bad page split. The code for this analysis is well-documented and can be found in the attached script.

After deleting the row, we will examine the changes in the leaf pages of the clustered index. We will traverse the B-Tree structure of the index to observe the effects of the bad page split. We will also check the intermediate level pages and the root page to see how the pointers and page boundaries have been adjusted.

Once we have analyzed the impact of the bad page split, we will rebuild the index again to ensure that the data pages are fully packed. This can be done using the following code:

ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail REBUILD
WITH(ONLINE = ON, DATA_COMPRESSION = NONE, FILLFACTOR = 100)

Finally, we will traverse the B-Tree structure of the index once more to see the changes in the page numbers and the arrangement of the data after the index rebuild.

From this analysis, we can draw several conclusions:

• Bad page splits result in the data from the original page and the new row being written into a new page, which is most likely not in sequence and not in the same extent.
• When SQL Server scans or performs an index range seek, it has to search for pages that are out of order, leading to external fragmentation.
• Both the old index page and the new index page after a bad page split may have internal fragmentation.
• Rebuilding the index can remove the effects of bad page splits.

It is important to note that the page numbers and specific details may vary depending on your installation. However, the overall results and conclusions should still be valid.

Thank you for reading this article on understanding bad page splits in SQL Server. If you have any comments or questions, please feel free to leave them below.