Why you should continue to defrag your indexes:-What are page splits 1/4

By | May 18, 2020

Recently I came across a video in which it was suggested the Page Splits/sec counter was not useful in identifying Page splits and that using fill factor to reduce the number of page splits doesn’t really fix the issues and often makes things worse. Naturally this goes contrary to a number of practices DBAs have been doing for decades now and I made we wonder if we got it wrong. The obvious next step was to try it out for myself and I realized that the suggestions in the video I came across needed some context. In this 4 part series I try to provide that context and specifically show that what DBAs have been doing was correct when it was implemented correctly.

This first video covers what is a page split and why it is bad for SQL Server. For an in-depth video on how page splits occur please check out the link below.

https://www.youtube.com/watch?v=ON4fy7_mbUQ

The second video is going to cover how page splits/sec counter needs to be used to identify and monitor the number of splits occurring.

The third video is going to show how file factor can help bring the number of splits down to almost zero

The fourth and final video is going to cover why in spite of using fill factor we don’t really face any significant performance issues and how it is worth it compared to actual page splits occurring.

Some basics about clustered indexes
Advanced concepts on Clustered Indexes