Is it better to take a hit on performance due to Fill Factor or Page Splits?
That is the question being answered in this video. As with most things in SQL Server there is a trade-off. Is the impact of additional pages caused by a fill factor of 70% more damaging than the impact of frequent page splits caused by a fill factor of 100%?
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.
What are page splits?
This first video covers what is a page split and why it is bad for SQL Server.
How to monitor Page Splits / sec ?
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.
How to use Fill factor to control page splits?
The third video is going to show how file factor can help bring the number of splits down to almost zero
Does low fill factor decrease performance?
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.
Please Consider Subscribing