Create Index Script with Fill Factor Question

  • I was looking for a t-sql script to create all the indexes on a database.  I found the following link which provided a good script to do the job I required.  I ran the script and it produced all the indexes to regenerate; however, I had a problem with some of the lines.  The following line: 

    CREATE NONCLUSTERED INDEX [IX_addVisID] ON [dbo].[Addresses](addVisID ASC) WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR = 0) ON [PRIMARY]

    generated this error: 

    Msg 129, Level 15, State 1, Line 508
    Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100.

    If the script can't use a value of zero for the fill factor, what is the appropriate value to use for fill factor?

  • The answer is it depends. Fill factor of 100 is fine for a clustered index on an incrementing value if your adding data and not updating or deleting. A fill factor of 90 is usually the default I use. If a lot of updates will change the logical order then a lower fill factor may be better.

  • It does not matter at all.
    FIll Factor is only taken into consideration when an index is rebuilt (reindexed).
    If you only drop and re-generate the index, as you mentioned in your post, the fill factor value would not make any difference.

    BTW, since SQL 2005 Fill Factor 0 meant the same as 100.
    In one of the later versions Fill Factor 0 is removed completely.

    _____________
    Code for TallyGenerator

  • Sergiy - Tuesday, March 6, 2018 5:39 PM

    It does not matter at all.
    FIll Factor is only taken into consideration when an index is rebuilt (reindexed).
    If you only drop and re-generate the index, as you mentioned in your post, the fill factor value would not make any difference.

    BTW, since SQL 2005 Fill Factor 0 meant the same as 100.
    In one of the later versions Fill Factor 0 is removed completely.

    That's why it actually does matter.  If you have a 100% Fill Factor on a table (clustered index) or non-clustered index that suffers (like Joe Torre said above) out of order inserts or expansive updates to variable width columns and you intend to make the mistake of defragging the index based only on the percent of fragmentation, you'll enter the wonderful world of blocking due to massive page splits right after you rebuild the index.  It also has the nasty effect of bloating your log file.

    Heh... of course, if you do like the folks did before me where I work and make denormailized tables with 147 columns with a row width of more than 4 KBytes, then, yeah... there's nothing Fill Factor is going do to save you.

    Agreed on the 0 Fill Factor.  I look at it and 100 as a marker for a probable mistake.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Tuesday, March 6, 2018 6:28 PM

    That's why it actually does matter.  If you have a 100% Fill Factor on a table (clustered index) or non-clustered index that suffers (like Joe Torre said above) out of order inserts or expansive updates to variable width columns and you intend to make the mistake of defragging the index based only on the percent of fragmentation

    As I said - fill factor matters only for index defragmentation.
    If it's not anywhere around the picture the number you put into FillFactor does not make any difference.
    Clustered or non-clustered, messy inserts or expansive updates - index will get exactly the same level of fragmentation with index pages sitting at the same places with the same content, with no relevance any Fillfactor value.

    _____________
    Code for TallyGenerator

  • Sergiy - Tuesday, March 6, 2018 6:47 PM

    Jeff Moden - Tuesday, March 6, 2018 6:28 PM

    That's why it actually does matter.  If you have a 100% Fill Factor on a table (clustered index) or non-clustered index that suffers (like Joe Torre said above) out of order inserts or expansive updates to variable width columns and you intend to make the mistake of defragging the index based only on the percent of fragmentation

    As I said - fill factor matters only for index defragmentation.
    If it's not anywhere around the picture the number you put into FillFactor does not make any difference.
    Clustered or non-clustered, messy inserts or expansive updates - index will get exactly the same level of fragmentation with index pages sitting at the same places with the same content, with no relevance any Fillfactor value.

    If I'm understanding what you're saying correctly, that last sentence isn't true.  If you start with a Fill Factor of, say, 70, you will have no change in fragmentation until you run out of the free space on the pages you're hitting (either by out of order insert or expansive update) and start splitting pages.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I believe I have found a solution to my issue. There are two articles that someone has directed me to:
    What is Fill Factor? Index, Fill Factor and Performance, Part 1
    What is the Best Value for the Fill Factor? Index, Fill Factor and Performance, Part 2
    Both articles give a complete overview of fill factor and possible solutions to what values to use.

    In my case, I DBA directed me to use a maintenance tool for SQL Server on Ola Hallengren website. The SQL Server maintenance script will allow me to rebuild and reorganize all my existing indexes. Thanks for everyone help.

  • ctsufer31 - Tuesday, March 6, 2018 9:05 PM

    I believe I have found a solution to my issue. There are two articles that someone has directed me to:
    What is Fill Factor? Index, Fill Factor and Performance, Part 1
    What is the Best Value for the Fill Factor? Index, Fill Factor and Performance, Part 2
    Both articles give a complete overview of fill factor and possible solutions to what values to use.

    Hmm, not really educational articles.
    This:

    Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit.

    is totally misleading.
    Fillfactor value does not allow or disallow SQL Server to fill up the pages in any particular way.
    It will always try to fill pages in the most effective way (well, according to its internal algorythms).

    This

    Fill factor is usually measured at the server level as well as table level.

    is pretty much rubbish.
    On the server level there is a default value of fill factor. Which may not match any of actuall fill factor values for any index anywhere accross the server.
    And fill factor on a table level is simply a nonsense.

    Don't put too much faith in those articles.

    _____________
    Code for TallyGenerator

  • ctsufer31 - Tuesday, March 6, 2018 9:05 PM

    I believe I have found a solution to my issue. There are two articles that someone has directed me to:
    What is Fill Factor? Index, Fill Factor and Performance, Part 1
    What is the Best Value for the Fill Factor? Index, Fill Factor and Performance, Part 2
    Both articles give a complete overview of fill factor and possible solutions to what values to use.

    In my case, I DBA directed me to use a maintenance tool for SQL Server on Ola Hallengren website. The SQL Server maintenance script will allow me to rebuild and reorganize all my existing indexes. Thanks for everyone help.

    Ola's stuff is great for defragmenting but, like Sergiy says, Fill Factor doesn't actually control the way the pages are filled.  For example, let's use a NOT NULL IDENTITY (narrow, ever increasing, unique, mostly immutable) column for a PK Clustered Index/Constraint.  You build the table with a Fill Factor of 70 on the table and start filling the table without doing any updates.  Now, after adding a couple hundred or even a million rows (it doesn't matter), ask yourself how much free space will be available on each page.  It won't be 30% due to a Fill Factor of 70.  The pages will have been filled to the maximum capacity despite the Fill Factor and there will be virtually no logical fragmentation (although, unless it's a single table database, there will always be non-contiguous physical fragmentation).

    To be honest, defragging indexes is almost a stupid thing to do unless you do it right.  Ola's great code works a treat but only if you do it right and I've not yet met anyone that actually does it right.  Here's why...

    Why do you defrag indexes?  Most will say to improve performance but they actually end up killing performance simply because they don't make a plan.  

    For example, it's actually stupid to defrag an index on a table with a 100% Fill Factor that isn't keyed on an ever increasing immutable value that isn't also static.  What it guarantees is that nearly every INSERT will require a page split until all the pages have been split.  The "Natural Fill Factor" that occurs in the near term will be ~50% and fragmentation will be 99.99% just after several page splitting inserts.  During those page splits, you can get some pretty nasty blocking in the system because those kind (bad) of page splits take a relatively long time not only due to the splitting action and related data movement but because all of that is fully logged and that more than doubles the time of the actual split.  Then, because of the supposedly bad fragmentation you see, you rebuild the index and start the agony all over.  It's as dumb as auto-shrinking database files every night.

    The cool part about it is, if you leave the index alone (don't do any index maintenance), then the thing that Sergiy is talking about takes over and the partially empty pages start to fill as if you were actively defragging them without the daily pain of all the pages splitting the day after you do the defrag.  Even Guids will settle out at a very nice average "Natural Fill Factor" of ~70% if you just stop messing with defragmentation.  The page splits that do occur will be minimal, especially when you compare to defragging at a 100% Fill Factor.

    The other thing is that people follow the recommendations of Reorg at 10% fragmentation and Rebuild at 30% fragmentation.  That's really bad because fragmentation does NOT occur unless there are page splits.  It's like waiting for a fire to start blazing for real before putting it out instead of putting it out while it's still just a flicker.  Waiting for 10% fragmentation means that you've already waited for a severe amount of page splits (remember, they block things until done and are tough on the log file).  If you defrag at 1% or even a half % on a table with a PROPER Fill Factor, then performance will not be a problem after you defrag or just before you defrag.

    Still, having not rebuilt any indexes on my production box since January of 2016 (more than 2 years ago), I can tell you that it's generally stupid to rebuild indexes.  If you leave them alone, they will develop a decent "Natural Fill Factor" all on their own and that thing that they call average percent of fragmentation matters very little if you have more than one user on box and more than 1 table in the databases.  You'll find, as I did, that performance actually improves by NOT rebuilding most indexes.  Just make sure you keep after stats updates.  It's crucial.

    As with all else, there are exceptions to the rule and those are when the % of page fullness drops to less than 70% over the long haul.  Those are usually because of expansive (entries in variable width columns have been made wider) and those actually can become a bit of a performance problem (especially on memory limited systems) because you have to read two pages (which will first be moved to memory) just to read 1 page of data.  Those do eventually need to be brought back up to a decent Fill Factor even if you're not making the mistake of general index maintenance.

    The real key to performance is to make sure that your statistics are up to date for both index stats and column stats.  I have, indeed, witness a 2 second query suddenly taking hours to execute all because of bad stats causing an improper execution plan to be formed for the query.

    I'll also tell you (I've done the testing) that the typical "reorg at 10%" really doesn't do a damned thing for performance (I've been testing on a known set of data for the last 3 months) and the bad page splits that occur are nearly identical to the number of bad page splits that occur with no index maintenance at all.

    So... bottom line is...
    1.  It's stupid to do index maintenance on most indexes that have a 100% Fill Factor because it will cause performance issues right after the maintenance is complete due to page splits.  If you insist on wasting time on trusting only % of fragmentation for general index maintenance, you MUST determine what the correct FILL FACTOR for all indexes actually is (I'm also working on a formula for that).
    2.  Reorg sucks.  Avoid it if you can.  It's no more effective than not defragging an index and takes a lot of time and log file (it's always fully logged) to do very little.  It will also NOT change pages that are (for example) 85% full back down to a (for example) 70% FILL FACTOR.
    3.  If you're going to do index maintenance, then don't use the accepted best practice of Reorg at 10, Rebuild at 30.  You waited too long for the damage to be done.  Do a rebuild at 1% or less AND make sure that you actually have a properly planned FILL FACTOR for every index or just don't bother.
    4.  DO rebuild indexes when then % of page fullness drops below 70% or so.  Just make sure that you're not rebuilding using a 100% FILL FACTOR or a FILL FACTOR that won't actually work for very wide rows. (For example, you can't get actually achieve a70% Fill Factor on a table where you can only fit 2 rows per page.  You'll need to study the trade off between having full pages and only half full pages).
    5.  If you do decide to follow what I did and just stop index maintenance, be patient!  Your old ideas of how to defrag stuff have cause a lot of damage and it's going to take some time for the system to heal itself numerically.  I saw a gradual improvement of performance (avg CPU dropped from 22% to 8% over the first 3 months and then stayed at 8%).  The second day, there was virtually no blocking on the system whereas the first day after doing defrags cause huge amounts of blocking.
    6.  No matter what you do, keep after stats updates.  They're crucial and the automatic updates can burn you if they kick in on a busy time of day on a big table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply