Exam 70-464 topic 1 question 38 discussion

Answer is right...

An index that has many random inserts and has very full pages will have an increased number of page splits. This causes more fragmentation and that is very bad for performance. We can reduce page splits by keeping some room in the data pages by setting fill factor value less than 100. The extra bytes on each page will help to minimize page splits caused by extra length in the rows. Read attached article to understand the best value for fill factor to avoid such page-split operations. We can also use index rebuild operation with the FILL FACTOR option that will allow the page fullness to be changed to fit the query pattern on the index. This will also helpful in reducing total page-split counts. I hope you like this article. You can drop your questions in comment section. Please follow our Facebook page and Twitter handle to get latest updates. https://techyaz.com/sql-server/how-to-avoid-page-split-in-sql-server/#:~:text=We%20can%20reduce%20page%20splits,avoid%20such%20page%2Dsplit%20operations.

Trying to maintain the extra space on the data pages would defeat the purpose of fill factor because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered. https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index?redirectedfrom=MSDN&view=sql-server-ver15 Fill-factor values 0 and 100 are the same in all respects.