Blog

Are you a page half full, or a page half empty kind of person?

Are you a page half full, or a page half empty kind of person?

I’ll never think “I’ve seen it all” when it comes to SQL Server– there’s just always someone waiting around the corner who’s found a weird new thing to do with it. But there are some things I really wish I could stop finding.

One of those things I hate finding is bad fillfactor settings. Fillfactor can be a useful tool to help performance, but it’s often a performance killer if you use it incorrectly.

A quick introduction: what is fillfactor, and how is it set?

“Fillfactor” is a setting for indexes in SQL Server. When you create or rebuild an index, you can tell SQL Server what percentage of each 8K data page used in the “leaf” level of the index it should fill up.

In other words, if you set a 90% fillfactor when you rebuild a clustered index, SQL Server will try to leave 10% of each leaf page empty. The empty space will be present on the page as it’s written on disk, and it’ll take up the same amount of space as it sits in memory.

By default, SQL Server uses a 100% fillfactor and tries to fill up all the pages in indexes as close to full as it can. Depending on how many rows actually fit on the page, your mileage may vary.

There are two ways to set fillfactor in SQL Server:

Index maintenance jobs sometimes automate the second of these options. If you’re using an index maintenance stored procedure you picked up from around the web, you may be setting fillfactor without realizing it.

Why do people love to set fillfactor?

DBAs and developers often read that lowering the fillfactor improves performance by reducing page splits. Perhaps they’re trying to fix a performance problem, or perhaps they’re feeling paranoid. They either lower fillfactor too much on some indexes, or apply a fillfactor change to all indexes.

Here’s the scoop: it’s true that the default fillfactor of 100% isn’t always good. If I fill my pages to the brim, and then go back and need to insert a row onto that page, it won’t fit. To make the data fit and preserve the logical structure of the index, SQL Server will have to do a bunch of complicated things (a “bad” type of page split), including:

  • Add a new page
  • Move about half the data to the new page
  • Mark the data that was moved on the old page so it’s not valid anymore
  • Update page link pointers on existing pages to point to the new page

And yep, that’s a lot of work. It generates log records and causes extra IO. And yes, if you have this happen a lot, you might want to lower the fillfactor in that index a bit to help make it happen less often.

Where do people mess up fillfactor?

Here’s the thing: having a bunch of empty space on your data pages is ALSO bad for performance. Your data is more spread out so you probably have to read more pages into memory. You waste space in cache that’s just sitting there empty. That’s not only not awesome, it can be TERRIBLE in many cases.

This is particularly wasteful because not all indexes are prone to “bad” page splits. Let’s say I have a clustered index on an incrementing INT or BIGINT identity value. I insert loads of new rows and values are rarely updated or deleted. In this case I can fill my pages very full because I’m always putting new rows at the “end” of the index. Adding these new pages aren’t bad page splits– although unfortunately they are counted in the “page splits/sec” performance counter, which makes it very tricky to find just the “bad” splits.

I frequently find that people have put a fillfactor setting of 80 or below on all the indexes in a database. This can waste many GB of space on disk and in memory. This wasted space causes extra trips to storage, and the whole thing drags down the performance of your queries.

Best practices for setting fillfactor

Here’s some simple advice on how to set fillfactor safely:

  1. Don’t set the system wide value for fillfactor. It’s very unlikely that this will help your performance more than it hurts.
  2. Get a good index maintenance solution that checks index fragmentation and only acts on indexes that are fairly heavily fragmented. Have the solution log to a table. Look for indexes that are frequently fragmented. Consider lowering the fillfactor gradually on those individual indexes using a planned change to rebuild the index. When you first lower fillfactor, consider just going to 95 and reassessing the index after a week or two of maintenance running again. (Depending on your version and edition of SQL Server, the rebuild may need to be done offline. Reorganize can’t be used to set a new fillfactor.)

This second option may sound nitpicky, but in most environments it only takes a few minutes to figure out where you need to make a change. You can do it once a month. And it’s worth it– because nobody wants their database performance to slow down and realize that they’ve been causing extra IO by leaving many gigabytes of space in memory needlessly empty.

Now that we’ve covered the basics, how about those five things? Here’s the fine print on how fillfactor is implemented, and what it does and doesn’t impact.

1) Books Online warned you

The most painful thing about finding bad fillfactor settings is that the Microsoft team has tried to warn people about how bad fillfactor settings can hurt performance. Unfortunately, most people don’t seem to find the warning.

Check out this quote in Books Online: “For example, a fill factor value of 50 can cause database read performance to decrease by two times. “

That’s pretty clear, right? So if I set a fillfactor of 70 (when I don’t need it), I risk decreasing performance by 30%. That doesn’t sound great, either. Because of this delicate balance, follow the best practices above.

2) Fillfactor does not apply to heaps

The fillfactor setting only applies to indexes, not to all tables. If you have a table which does not have a clustered index, it’s called a “heap” in SQL Server. Heaps are weird in several ways. One of those ways is that fillfactor doesn’t apply– not even the fillfactor you set at the instance level. (Page splits don’t work the same way either, but this is not a good reason to have heaps.)

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.)

4) Fillfactor does not apply to LOB pages

Fillfactor applies to in-row data pages. When you create a table, depending on the data types, you have options as to when some large types get stored on-row, or off-row. When data is not stored in-row, fillfactor settings don’t apply to those special pages.

A general rule of thumb is that if you infrequently read large object columns, it’s better to keep those columns off-row. You will incur extra IO every time you need to fetch off-row data. but it keeps the frequently accessed in-row columns of your index more efficient.

5) Someone may have changed fillfactor without you realizing it

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.

Like anything else, there’s weird exceptions where in very rare cases, setting a super-low fillfactor on a very heavily updated table (which is probably small), can help reduce contention. These cases are very rare. Often there’s a better long term change in the application layer that would handle the issue.

Bottom line: If you find you’re using a low fill factor and you don’t know exactly why it was needed, always look carefully to see if you’re hurting performance more than helping it. And if you’re using the same fillfactor on all of your indexes, you’re probably wasting lots of valuable memory.

How to check your fillfactor today

Our free sp_BlitzIndex® tool looks for all sorts of insanity in your indexes at the database level. It will snoop through your metadata and warn you about low fillfactor values, plus let you know exactly how big those indexes have grown. Check it out today.

↑ Back to top
  1. Great writeup! For dedicated SharePoint instances however, setting the fill factor to 80 system-wide is recommended. See the article at http://technet.microsoft.com/en-us/library/cc262731(v=office.14).aspx#DBMaintenanceForSPS2010_FineTuneIndexPerfByFillFactor, revised by Paul Randal

    • LOL, even the SharePoint whitepaper says the advice shouldn’t be followed for other systems.

      As a general rule of thumb, for vendor supplied systems like SharePoint you have to read the vendor’s documentation closely and make sure you’re either following the vendor recommended guidelines or prepared to suffer the consequences (which can be pretty darn bad). That applies to wayyy more than just fillfactor. SharePoint is a particularly thorny example.

      Thanks!

    • Postscript for any other readers looking for fillfactor configuration on SharePoint– also check out this blog regarding maintenance scripts in SharePoint 2013: http://sharepoint.nauplius.net/2013/04/the-fill-factor-mystery/

      I’m not a SharePoint specialist and I haven’t confirmed the information in that blog post myself, but if you are working on a SharePoint 2013 configuration then it shouldn’t be much work to confirm the information posted there.

      • SQL Server database architecture isn’t my strong point, but generally with SharePoint, unless otherwise advised by PSS, you should tend to follow Microsoft’s recommendations (70 – 80 fill factor for SharePoint 2007, depending on Service Pack level, 80 for SharePoint 2010, and 80 – 100 for SharePoint 2013). As of SharePoint 2007 SP2, there is an automated timer job that takes care of the defragment of indexes, as well as setting the fill factor, so this no longer has to be part of the DBAs maintenance plan (updating stats is another one that SharePoint takes care of). The only change in 2013 is that the timer job no longer sets the fill factor, but that appears to be due to the use of various fill factor values throughout a single database (content or service application).

  2. Kendra,

    Nice article! We ended up writing a proc that was similar to Ola’s, but with a few tweaks of our own, including some “auto-incrementing” logic for Fill Factor based on how many times an index had qualified for Reorg/Rebuild scope in the last X days. (e.g. if an index has more than some threshold of fragmentation 5 days in a 7 day period, we probably need to bump up its FF by 5 percent). Over time, the system stabilizes so that only the most volatile tables qualify very frequently.

    Obviously, this logic doesn’t work for every DB’s workload, but we seem to have hit a pretty nice sweet spot and have been much more “hands off”. :-)

    • Interesting! Does it notify you if fillfactor goes down past a certain point?

      I ask this having automated myself into a weird corner situation without realizing it a time or two in the past. :)

      • We ended up setting a hard lower limit of 80%, though I believe even that limit is somewhat flexible based on the size of the index.

        (I’d have to review the logic… it is basically a large block of IF…ELSEs on the index size and recent reorg/rebuild frequency, placed in a UDF so we can modify the logic easily. There are also some app-specific sections of that logic since we are an ISV and have the luxury of supporting lots of instances of the same applications).

    • Aaron,

      Care to share this proc with the rest of the group?

      – Mark

      • Mark,

        I wrote the code for my current employer as part of a database maint solution so it is proprietary, unfortunately. Perhaps someday I’ll re-write on my own time and make it available!

        Aaron

        • I’ve built one that’s in a very rudimentary stage… I’m sure not on the level of Aarons. This idea interested me and I’ve decided to see how it would work out. It’s 100% not ready for production use, but it’s a good platform to see how it works.

          Please be gentle ^.^’

  3. Amazing clarification for a very intriguing topic! As usual…great article!

  4. Great blog Kendra. I have a few indexes at my new place that remind me of our mistakes with Atlas more than I’d like to admit… Aaron, wonderful idea with the incrementing proc.

    • Oh, the indexing advanters we’ve had over the years. It’s funny how there’s always NEW ones that show up!

    • Thanks, John! Combining that with using the DMVs to drive scope (and adjusting settings like LOCK_TIMEOUT and DEADLOCK_PRIORITY for the index rebuild task) has eliminated a lot of Production support requests. It was definitely worth the effort to code it up.

  5. Another interesting corner of SQL Server is the dialogue in the maintenance plan wizard: Brad Schulz points out that it asks for free space percent not fill factor percent!. SQL Server’s maintenance plans are not used too often in the wild, it’s a corner of SQL Server I’m not too familiar with. But it reminds me that I haven’t seen it all either.

    BTW, I like the data page illustration, I can see the header and the slot array.
    Fun fact: Microsoft cuts the top right corner of each page so that any pages that are not oriented correctly (i.e. upside down or rotated) can be easily identified.

    • I forget where, but I saw a discussion in the comments on a blog post about whether index trees have the root page physically at the bottom or the top. :)

      That IS na interesting point about fill factor and maintenance plans! Thank you for mentioning that. No wonder people get confused on that point.

    • Actually, the picture is misleading. 8KB refers to all area shown. This includes page header, space for data rows and slot array.

      But the real precious gem is “In other words, if you set a 90% fillfactor when you rebuild a clustered index, SQL Server will try to leave 10% of each leaf page empty.” This exactly how it works! Imagine you have 16 records each taking about 6 % of the space of a page. Again you re-set fill factor from 100 to 10. This should populate leaf level pages with 2 records leaving 88% free, this is 12% full which is higher than fill factor.

  6. Pingback: Managing Memory & Processors within SQL Server | Designed-Solutions

  7. Pingback: Indexing Fundamentals Presentation – Pending Questions Answered | The SQL Pro

  8. Kendra, thank you for yet another excellent article!

    Setting fill factors entails trading off the costs of page splits vs the costs of non-compact storage. I am working with a ‘transactions table’ with about 2.4 billion rows with an identity PK (TransactionID) and clustered index. For the table/CI itself, it seems clear the fill factor should be 100. There are also seven NC indexes on this table, one of which is (AccountID, Transaction_Datetime), 12 bytes per index row. There are about 400,000 accounts so there are about 6000 rows per account. Since each index row is 12 bytes, there are 72,000 bytes per account in this index. With a fill factor of 100, there would be about nine 8-K data pages per account, each of which holds about 680 index rows. With a fill factor of 50, there would be about 18 8-K data pages per account, each half full at the start.

    Since the PK and Transaction_Datetime are in the same order, inserts into this index occur at the end of each account’s set of data pages. After a split occurs, there can be another 340 inserts before another split is necessary because a full page can hold about 680 rows.

    If the fill factor for the index is 100, then for the first insert for each account, there will be a page split, after which we can do another 340 inserts for this account without having a page split. Index scans and seeks will be as efficient as possible since after the split, eight of the ten pages (post-split) are completely filled.

    For the sake of the example, if the fill factor for the index is set to 50, then we will be able to do about 340 inserts on the last originally half-full page before we have to split again. However, index scans and seeks will have to traverse almost double the disk space, 18 pages instead of 10. A more conventional fill factor of 75% would be halfway between these cases, 25 % wasted space for 170 ‘free’ inserts before a split.

    It is interesting to realize that the fill factor only affects the number of inserts that can be made prior to the first split. After the first split, the number of inserts that can be made before the next split is fixed, given the index row size.

    The negative effect of low fill factors (or the positive effect of high fill factors) increases with:
    • Index size (row count and width), via the number of static, ‘historical’ pages in the ‘front’ of the index that are stored non-compactly
    • Index usage, via the number of scans or seeks in an interval
    • The rate at which index read speed declines as fill factor declines
    • High insertion rates, since the benefit of the initial ‘free’ inserts are diluted
    • Low page split costs
    • Infrequent index rebuilds, since the benefit of the initial ‘free’ inserts are less frequent
    The point about index size applies only to this type of index, where inserts are restricted to occurring at the end of sets of data pages. If the index were over a string or GUID, then the location of inserts would be much more random and the size effect would vanish.

    So after all that, I still don’t know what to set the fill factor to, but I have a lot more respect for higher fill factors than I did at the outset. Sorry for the length of this, but I did want to share…

  9. Oops, my math is wrong, since I forgot to include the PK in the index row size. So the row is 16 bytes rather than 12, and this ripples through. But the conclusions remain; if anything, the case is better for high(er) fill factors for large indexes of this type.

  10. Hi Kendra,

    Thanks a lot for this intriguing write up.
    The counter we often use to check fragmentation is page split/sec but as you mentioned it will take into account the inserts at the end,

    Please explain how to check the page splits and which are actually bad.. :)

    • Hi there,

      I would really love to explain a good way to track “bad” page splits– but there’s really just no good way. In theory, you can get them by reading from the transaction log, but this is a very heavy handed and non-trivial operation and you’re much more likely to hurt your performance by constantly reading your transaction log than you are by identifying “bad” page splits.

      Page splits are one of those topics where people get very worried about them and completely miss the big picture. The easiest thing to do is to check which of your indexes get fragmented the most frequently, adjust the fillfactor on those (gradually and slowly), and don’t obsess about the page splits. If you have performance issues, you want to step back and identify when things are bad and take a more general tuning approach to solve ‘em.

      kl

  11. Good article. Thanks.

    One (small) confusion: under “Best practices for setting fillfactor” it says “Reorganize can’t be used to set a new fillfactor”. But under “Fillfactor doesn’t impact new pages inserted at the end of an index” it says “SQL Server only uses fillfactor when you’re creating, rebuilding, or reorganizing an index”.

    • Great question! This stuff is complicated.

      Let’s say my existing fillfactor is 100% and I want to change it to 90%. I can only do that when I create an index or rebuild it.

      Let’s say I previously set the fillfactor to 90%, but a lot of fragmentation has happened. I want to defrag it and get it back to 90%. Reorganize will do this– it applies fillfactor that was previously set.

      In other words, running a reorganize doesn’t obliterate/remove/undo fillfactor settings or only use 100% fillfactor.

      Does that help?

  12. Fantastic article on a lesser known yet critical area in SQL!

  13. Hi Kendra, Just to make sure i’m clear…so you recommending leaving the fillfactor at the default of 100% and only ever changing it after monitoring for fragmentation?

    • Yep, exactly. I’m not saying 100% is perfect, just that it’s got the fewest downsides in general if you have to pick a default.

  14. Good article Kendra.
    one more thing I want to add
    you can reduce fragmentation in a Table by Creating a good Clustered Index.

    • Well, sometimes.

      Let’s say I decide that the existing narrow, unique multi-column index is prone to fragmentation. Oh, that’s bad! I decide to add an identity value to the table and make it the unique clustered index. No queries use it, but hey, it’s not prone to fragmentation.

      Suddenly, I find that all the queries that were using the old clustered index are incredibly slow. I get to choose between leaving them slow, or creating a giant non-clustered index that’s… just like my old clustered index. And prone to fragmentation. Ooops.

      I agree that clustering keys can be designed well in ways that aren’t very fragmented. But I don’t think avoiding fragmentation in the clustered index is a rule to always follow.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php