February 5, 2018 at 10:19 pm
Comments posted to this topic are about the item Index Fill Factor
February 6, 2018 at 12:11 am
Good question thank you Arsh.
...
February 6, 2018 at 2:08 am
Nice question, thanks Mohammed
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
February 6, 2018 at 7:33 am
I would like to see documentation on this:
When a new transaction requires a new page, SQL Server doesn't apply the 80% fill factor for new pages allocated.
I have always assumed that when data is inserted into the end of a table requiring new pages/extents be allocated, that those pages would only be filled to the designated Fill Factor.
February 6, 2018 at 8:19 am
Thanks for the interesting question Mohammed. Quite hard, but the documentation helped me.
Is it e.g. "5 Things About Fillfactor", and in Docs "ALTER INDEX (Transact-SQL)".
February 6, 2018 at 8:31 am
George Vobr - Tuesday, February 6, 2018 8:19 AMThanks for the interesting question Mohammed. Quite hard, but the documentation helped me.
Is it e.g. "5 Things About Fillfactor", and in Docs "ALTER INDEX (Transact-SQL)".
Thanks, that refuted my assumption. Glad to learn something new!
February 6, 2018 at 9:49 am
Thanks David , George and others as well for compliments and sharing more related info. I'll definitely share the doc I referenced if I can as I go through my favorites lists n other stuff. I had the same assumption as David's about FF for new pages , but as I learned it turned out the FF for the new pages is 100% until the next rebuild with non-100% FF .
Thank you
Arshad
February 6, 2018 at 11:18 am
I don't agree with the answer.
Fill factor is available for viewing in the view (?) sys.indexes. It would probably be a bad feature for reorgs to ignore the "set" fillfactor (you know, the entity that this question assumes does not exist), and additionally, you cannot specify fill factor for reorgs. So lets do a thought process here, say you rebuilt an index with an SQL statement that specified a fill factor of 75. If this setting were not persistant, what would the next reorg use? 100?
Think about it, if I set the fill factor to 75, then did reorgs later that subsequently used a fill factor of 100 because according to the OP the fill factor doesn't persist, wouldn't that just make reorg a pain to use?
What this question seems to muddle is that there is a "specified" fill factor that you can examine for each index in the view sys.indexes, an actual empty space percentage for each page of the index at a specific point in time, and average empty space percentage for the entire index at a specific point in time.
We know pages fill up then split when inserted (obviously not at the very last index leaf for sequentially increasing index key values, these probably don't need splitting, we just get a new page), but this question seems to imply that the fill factor is not persisted anywhere and I just don't think this is accurate.
This page https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/ seems to back this up:
Once fillfactor is set on an index, it stays there. Further rebuilds or reorganizations of the index maintain that fillfactor unless you specify a different value. It’s easy for a change in fillfactor to sneak in. Unless you check for indexes with a fillfactor set, you might not realize what’s going on in your database.
February 6, 2018 at 1:14 pm
patrickmcginnis59 10839 - Tuesday, February 6, 2018 11:18 AMI don't agree with the answer.
Fill factor is available for viewing in the view (?) sys.indexes. It would probably be a bad feature for reorgs to ignore the "set" fillfactor (you know, the entity that this question assumes does not exist), and additionally, you cannot specify fill factor for reorgs. So lets do a thought process here, say you rebuilt an index with an SQL statement that specified a fill factor of 75. If this setting were not persistant, what would the next reorg use? 100?
Think about it, if I set the fill factor to 75, then did reorgs later that subsequently used a fill factor of 100 because according to the OP the fill factor doesn't persist, wouldn't that just make reorg a pain to use?
What this question seems to muddle is that there is a "specified" fill factor that you can examine for each index in the view sys.indexes, an actual empty space percentage for each page of the index at a specific point in time, and average empty space percentage for the entire index at a specific point in time.
We know pages fill up then split when inserted (obviously not at the very last index leaf for sequentially increasing index key values, these probably don't need splitting, we just get a new page), but this question seems to imply that the fill factor is not persisted anywhere and I just don't think this is accurate.
This page https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/ seems to back this up:
Once fillfactor is set on an index, it stays there. Further rebuilds or reorganizations of the index maintain that fillfactor unless you specify a different value. It’s easy for a change in fillfactor to sneak in. Unless you check for indexes with a fillfactor set, you might not realize what’s going on in your database.
Ah..Confusion here. What I meant was that for any new pages(end of last row) allocated after a rebuild with a non-100% FF (say, 80%) , SQL server tries to keep the free space on ONLY those new pages to zero. Yes , when the next rebuild occurs even these NOW new pages would get FF of 80%.So I was talking about what happens to FF for new pages(end of last row) and only the new pages in between two Rebuilds. Taking it further to other cases where Inserts/Updates occur any where in the index , SQL searches for older pages first to see which page has sufficient enough free space to fit the incoming insert/update row , disregarding the Fill Factor value, avoiding IO cost that it would otherwise incur . Hence I meant that FF has effects on the index ONLY at the time of Rebuild/Reorg. Hope I'm clearer this time. Please correct if I'm missing anything.
Thanks..Arsh
February 6, 2018 at 3:09 pm
Ah..Confusion here. What I meant was that for any new pages(end of last row) allocated after a rebuild with a non-100% FF (say, 80%) , SQL server tries to keep the free space on ONLY those new pages to zero. Yes , when the next rebuild occurs even these NOW new pages would get FF of 80%.
I think it would have been better had you acknowleged what happens with reorgs.
Also, is it really the case that the 45 percent answering wrong think that pages split or combine during EVERY table insert or delete? Or is it more that the 45 percent answering wrong believe that the fill factor setting is saved for each index, like I did?
So I was talking about what happens to FF for new pages(end of last row) and only the new pages in between two Rebuilds.
You could have listed operations and asked which operations refer to a fill factor setting. This would be clearer.
Taking it further to other cases where Inserts/Updates occur any where in the index , SQL searches for older pages first to see which page has sufficient enough free space to fit the incoming insert/update row , disregarding the Fill Factor value, avoiding IO cost that it would otherwise incur . Hence I meant that FF has effects on the index ONLY at the time of Rebuild/Reorg.
This I specifically disagree with. Rows in an index go in the page not based on sufficient free space, but on the value of the
key(s). If a row is meant to go into a page, and the page is full, then by definition the page will be split, SQL will not search
for a page that is less full because then the value range of the keys contained in this LESS FULL page will be a mismatch for
the incoming key and SQL server will then be producing incorrect results. Remember, fill factors describe index pages, not heap
pages!
Hope I'm clearer this time. Please correct if I'm missing anything.
Sorry, it really got much less clear and your new explanations only add to my objections. Nothing personal of course, only talking about the subject matter!
February 6, 2018 at 7:00 pm
Maybe the explanation can be found in paragraph 3) of 5 Things About Fillfactor:
3) Fillfactor doesn’t impact new pages inserted at the end of an index
SQL Server only uses fillfactor when you’re creating, rebuilding, or reorganizing an index. It does not use fillfactor if it’s allocating a fresh new page at the end of the index.
Let’s look at the example of a clustered index where the key is an increasing INT identity value again. We’re just inserting rows and it’s adding new pages at the end of the index.
The index was created with an 70% fillfactor (which maybe wasn’t a good idea). As inserts add new pages, those pages are filled as much as possible– likely over 70%.
(It depends on the row size and how many can fit on the page.)
and also in Docs ALTER INDEX (Transact-SQL) in the paragraph describing the argument FILLFACTOR:
An explicit FILLFACTOR setting applies only when the index is first created or rebuilt. The Database Engine does not dynamically keep
the specified percentage of empty space in the pages. For more information, see CREATE INDEX (Transact-SQL).
February 6, 2018 at 9:20 pm
Thanks George for sharing the docs.
February 6, 2018 at 9:28 pm
I acknowledge what you are saying and agree. . My mention of scenarios represents bigger picture.I would try to write a post as I get docs for backing up .The info is based more on interactions with reknowned experts on this forum.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply