Part 4/4 Why you should continue to defrag your indexes :- Performance impact
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.
• Part 1/4 Why you should continue to d...
This first video covers what is a page split and why it is bad for SQL Server.
• Part 2/4 Why you should continue to d...
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.
• Part 3/4 Why you should continue to d...
The third video is going to show how file factor can help bring the number of splits down to almost zero
• Part 4/4 Why you should continue to d...
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.
For an in-depth video on how page splits occur please check out the link below.
https://www.youtube.com/watch?v=ON4fy...
Need more information? Visit https://www.enabledbusinesssolutions.com
To know more about Jayanth Kurup visit http://www.jayanthkurup.com
Jayanth is a Microsoft SQL Server trainer / consultant with over 15 years’ experience. He has worked with companies like Microsoft, IBM, HCL, Wipro, Well Fargo, Zeomega and many more. He is based out of Bangalore and a very active member of the SQL Server Community.
In this channel he covers complex topics related MS SQL Server , PowerBI , Azure , AWS , Powershell, SSIS , SSAS , SSRS , Machine Learning , AI , etc
You can also reach Jayanth via his LinkedIn profile below / jayanth-k. .
music from HAAWK