April 20, 2015 at 9:39 am
RonKyle (4/20/2015)
...Be careful of fillfactor, else it is possible that your table ends up taking up more space, unless that is what is expected.
If your choice of a clustered index is such that new records could be inserted anywhere, you should have a fill factor. This will reduce the speed at which the table fragments. If it's sequential (even if not an identity but something like a creation date), a fill factor is probably not necessary. ...
Just to clarify, always explicitly specify the appropriate fillfactor for each index -- if it should be 100%, code that value. Otherwise, yes, there is a pre-set default fillfactor, but I've seen it set as low as 70%, it's not automatically 100%.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 20, 2015 at 9:47 am
there is a pre-set default fillfactor, but I've seen it set as low as 70%, it's not automatically 100%.
What does this mean? The default is 0 (100%). If you've seen it set as low ast 70%, it's because someone changed it. Someone should know the fill factor of an index before they start making changes to it.
April 20, 2015 at 1:08 pm
there is a pre-set default fillfactor, but I've seen it set as low as 70%, it's not automatically 100%.
RonKyle (4/20/2015) What does this mean? The default is 0 (100%). If you've seen it set as low ast 70%, it's because someone changed it. Someone should know the fill factor of an index before they start making changes to it.
We were talking about creating a clustered index. I simply stated you should never assume the default fillfactor is 100% or any other specific value. Instead, explicitly specify the fillfactor you think the new index should have.
For index changes, I believe SQL will leave the fillfactor the same as it was on that index.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 20, 2015 at 1:11 pm
For index changes, I believe SQL will leave the fillfactor the same as it was on that index.
True, but since we weren't talking about altering an index, I was trying to understand why you thought this would apply.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply