September 28, 2012 at 8:27 am
Someone suggested that fill factor needs to be set 70-85 range for all indexes where index update is greater than index seek, scan and lookup combined. Is that a valid suggestion?
Thanks
September 28, 2012 at 8:40 am
No, not as a blanket statement like that.
Depends on the type of inserts (end of index or anywhere), depends on whether updates will grow the row or not. Depends on whether deletes are expected to leave holes in pages or not.
One way to manage fill factor - if an index gets fragmented quickly (and quickly is up to your definition), then lower its fill factor. The lowest I'd probably set for all indexes without investigation is 90-95, then lower it if necessary for particular indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2012 at 8:59 am
GilaMonster (9/28/2012)
No, not as a blanket statement like that.Depends on the type of inserts (end of index or anywhere), depends on whether updates will grow the row or not. Depends on whether deletes are expected to leave holes in pages or not.
One way to manage fill factor - if an index gets fragmented quickly (and quickly is up to your definition), then lower its fill factor. The lowest I'd probably set for all indexes without investigation is 90-95, then lower it if necessary for particular indexes.
Thanks Gail. All of our indexes are partitioned and aligned. Does that make a difference? There are no deletes, only partition switching for archiving. Inserts occur on a the active partition of course. So would changing fill factor should improve insert performance? Would rebuilding an index (on a specific partition) with a fill factor take longer that without the fill factor?
September 28, 2012 at 9:08 am
Gail can correct me if I'm wrong.
But unless your Index is static and insertions happens at the end, altering FILL factor will do little or nothing in performance gain or minimizing fragmentation. As a matter of fact, if your table is big, you will end consuming more space than what is needed.
All those "magic numbers" that you can find online, like 30% for internal fragmentation or 80 for internal, were provided by Kimberly Tripp long time ago when she was preparing a document for Microsoft. I believe I read that long time ago from her post. She later confirm that those are random numbers, and like Gail said, the best way to calculate that is seeing weekly or periodically how quickly those get fragmented after a rebuild and after playing with default FILL FACTOR.
EDIT
Expanding my answer a bit. If you are doing partitions, the FILL FACTOR is not related. The SWITCH will basically change the pointer or metadata and you will move or see the old information on your archiving table. Also, if your insertions occurs at the end, new pages on leaf level will be added at the end, not in the middle, so the FILL FACTOR won't help on that particular case. The FILL FACTOR basically leaves some space on each page, leaf level, so if you do INSERTIONS in the middle, MS-SQL avoids page splitting. If the Index is static and we have no insertions in the middle (or deletes) I do not see a reason to play with it.
September 28, 2012 at 9:15 am
Lowering the fill factor can improve write performance by reducing the number of page splits. It can impair read performance by requiring a larger number of pages to be read into memory to retrieve the same amount of data. The number upon which you eventually decide will strike a balance between the two and will depend on the things Gail mentioned, and, more importantly, your performance testing.
John
September 28, 2012 at 9:49 am
sql-lover (9/28/2012)
But unless your Index is static and insertions happens at the end, altering FILL factor will do little or nothing in performance gain or minimizing fragmentation. As a matter of fact, if your table is big, you will end consuming more space than what is needed.
Other way around.
If the index is static or inserts happen at the end (and there are no row-size increasing updates), then altering the fill factor will not help.
All those "magic numbers" that you can find online, like 30% for internal fragmentation or 80 for internal, were provided by Kimberly Tripp long time ago when she was preparing a document for Microsoft. I believe I read that long time ago from her post. She later confirm that those are random numbers
It was Paul Randal, not Kimberly, and he did not say they were random numbers, he said they were rough guidelines that he made up based on his knowledge of how the storage engine works, and he wrote portions of the storage engine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2012 at 9:56 am
GilaMonster (9/28/2012)
It was Paul Randal, not Kimberly, and he did not say they were random numbers, he said they were rough guidelines that he made up based on his knowledge of how the storage engine works, and he wrote portions of the storage engine.
Right! 😉
I remember I read it, but it was long time ago.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply