Age old topic but still very important even in the age of SSDs. Page splits and fragmentation concepts were one of those early things we learned while learning about SQL Server architecture. Years later, with the advent of ultra-fast storage, the importance of these concepts has diminished but they are not irrelevant. Data in SQL Server is stored on 8 KB pages and sorted based on the clustering key/column(s). Assuming that the data is nicely arranged in an ordered fashion, fragmentation is zero or minimal. For example, when you create a new clustered index, SQL Server will re-arrange the pages and at that point, the pages are nicely laid down from start to end. Fragmentation is determined by the percentage of out-of-order pages vs the ordered pages. Page split is a phenomenon that leads to fragmentation and reading fragmented data is slower compared to defragmented data. How much slower? Is it a deal breaker? Well, it depends on a lot of factors. Let’s come back to the page splits. When you try to update a record in such a fashion that its length increases and there is no space on the page to hold it anymore, SQL Server will add a new blank page at the end of the file and move approximately fifty percent of the data from the existing page (where the modification was happening) to the new page. This will make space for the current record to expand. This operation of adding a new page and records moving around is called a page split. And you can now see how the data starts to get unordered. The rows that were nicely sitting in the correct order are now relocated at the end of the file to a new page and are no more in the order, thus increasing fragmentation. Watch the demo below.
Get new content directly in your inbox – Click here
You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).
Hope the video was worth your time.
Happy Learning.
Leave A Comment