Recently on one of the popular SQL Forums I read a post by a DBA who seemed experienced but still had a poor understanding of fill factor and why it is important for SQL server. I’ve been asked this questions a number of times during trainings so here is a video hoping to show some fundamental behavior of data page behavior when inserting rows into SQL server.
In the video I cover how the data page looks when you insert data into it. Followed by what typically happens when a data page is full, followed by the changes to the data page when a clustered index is added for a lower fillfactor than the default value of 100.
I then go on to demonstrate how lower fill factor ensures that data is added into the pages that are already allocated and how a page split might arise.
This is followed by how to correct the issue with page splits. The video covers just the basics, but if you need any further clarifications please feel free to ping me.