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:
- At the SQL Server instance level using a sys.configurations setting for fill factor. (I don’t recommend using this option, for reasons I’ll describe below.)
- At an individual index level by specifying fillfactor when you create or rebuild the index.
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:
- Don’t set the system wide value for fillfactor. It’s very unlikely that this will help your performance more than it hurts.
- 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.
72 Comments. Leave new
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).
And I thought I’d made this article complicated! Holy wow.
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 ^.^’
Hi Kendra, Thank you for your article regarding Fill-factor and page splits.
It’s very informative and I am getting a lot out of it.
I just want to clarify some concepts.
If I understand things correctly setting a fill factor to less than 100% will only be beneficial if the columns in those indexes are updated again after the index is created/rebuild/reorganized. Assuming that you run an index maintenance on the relevant indexes every 24 hours, if most of the columns in those indexes are inserted/updated/delete between the index maintenance schedules and never get touched again (updated, deleted) then applying a fill-factor on those indexes columns would be useless.
Am I right to say that Fill-factor is only relevant to columns that would be updated again after the index maintenance that sets a fill-factor?
TIA
Hi,
Great question!
Yes, if you do not do inserts/updates/deletes on a column then the empty space you’re using is just that– empty space. Not useful in any way.
Hope this helps. Just let me know if there’s more to the question and I missed it.
Amazing clarification for a very intriguing topic! As usual…great article!
Thanks so much!
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.
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.
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…
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.
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
Dear Kendra,
could extended events be used to collect page splits events and to differentiate bad ones using splitOperation detail data?
Yes, but then you have the overhead of a trace.
Isn’t this captured as part of the DMVs now? And if you were using a trace, can you not use a server-side trace for this data? I find that there is very minimal overhead when using SS and would never ever recommend to a client that they use the client-side tracing tool.
Ven – no, good vs bad page splits still aren’t measured in the DMVs.
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?
Fantastic article on a lesser known yet critical area in SQL!
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.
Hi Kendra,
Does Enterprise feature row/page compression do any changes to your recommendation of 100% fill factor i.e. does it compress the empty “fill”, for example if I have fill factor 85, would it compress pages so that those are “100%” filled when on disk? How about those being read into buffer cache, are those 15% empty or 100% filled there?
Check out this post by one of the people who designed it! http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/05/12/data-compression-and-fill-factor.aspx
He answers the question of the fillfactor on disk. The pages in buffer cache is the same as the pages on disk.
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.
Hi Kendra, great info as always!
I’m using Ola Hallengren’s index maintenance scripts, and I see a particular index is very fragmented (80+ %) every run and is being rebuilt. Current fill factor is 100.
If I manually rebuild the index with a fillfactor of 90, will future runs of Ola’s script keep fillfactor at 90 or will it reset to the system default of 100?
Great question. It will use the existing fillfactor on an index unless you tell it to do otherwise by specifying the @fillfactor parameter.
Hi Kendra, Thanks you for your post on Fill-factor.
I just need to verify that I am understanding this properly.
If my application inserts and update columns records within 24 hours (or between index rebuild/reorganizing) and never updates them again, then if those updated columns are indexed and I suspect it the cause of high numbers of page splits, rebuilding that index with a fill-factor less than 100 will not make much difference as they will never be touched again except for report querying.
Am I right?
TIA
Great article, I will take the idea of logging when an index is fragmented and fit adjusting the fill factor into it. Very good idea and automated.
Dont forget to log when a fill factor is adjusted though 🙂
Great article Kendra, could you write some recommendations about when (and when not) to enable the PAD_INDEX=ON option in association with setting a FILLFACTOR < 100
I haven’t found a case yet where PAD_INDEX has been proven to improve performance.
Hi Kendra,
I am having a doubt. Could you please let me know How much a new page of an index would be filled if i have set 90% fill factor value for this index.
Hi Shakti– the easiest way to answer your question is this quote from books online:
“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. ”
https://msdn.microsoft.com/en-us/library/ms188388.aspx
The exact amount that a new page at the end of the index would be filled will vary by row size/how much it can fit on the page / how much you actually inserted. So I can’t give you a number, I can just say it won’t apply the fillfactor until the index is rebuilt or reoganized. (The quote is a little imperfect, reorganize will apply an existing fillfactor.)
Thanks Kendra, Got my answer 🙂 .
Hi Kendra,
I have one Index with setting fillfactor 0, Frag_Percentage =88.39, fragment count =305, pagecount 605
after rebuilding this Index (withought changing the fillbactor) fragment count becomes 4894 and pagecount reach to 43061, So will it cause the performance degradation. Does Index need to be rebuild withe lower fillfactor setting.
Sardar
Hi Sardar,
Something doesn’t quite add up here. If you start with 605 pages, and rebuild the index with 0% fillfactor (fill to capacity), you shouldn’t end up with 43,061 pages afterward. If you’re filling to capacity, you should have 605 or less pages afterward. So I’m not quite sure what happened– it sounds like data was added between the measurements.
Kendra
Hi Kendra, great blog.
I’ve been delving into indexes a lot deeper lately and came across this when researching fill factor.
I didn’t realize fill factor is not maintained “live” unless you build/rebuild/reorganize. In that case, what’s the point? If the whole point is so that pages have free room for future inserts, then this only works on indexes created AFTER the table has many rows (and many X% full pages), but once a table (in my case) is big enough to warrant this sort of deep investigation – it’s often too late to rebuild indexes (i.e. it would take too long and can’t be done without downtime – etc)..sort of a catch 22.
If indexes aren’t rebuilt/reorganized often enough (which I know is a whole ‘nother issue), then it sounds like any benefit from fillfactor will just faze out as those pages are filled, correct?
On a related note, I was trying to put the following test together to test how non-clustered indexes react to data inserted out of order (been finding mixed info online), and how a fillfactor may help:
I created a test table with 988byte rows, so that 8 rows (7904bytes + overhead/pointers etc) fit on one page. The table has an identity column which is the clustered key (i.e. no logical fragmentation there), an int column (“COL1”) which I made a non clustered index on, and a varchar(1000) for substance.
I included the varchar(1000) field in the NC index so that the NC index is just as wide as the Clustered index – otherwise I would need millions of rows to get any useful number of pages on the NC index (for this test ONLY – wouldn’t do in real life)
For the first test I inserted 40K rows (5K pages) with an ascending int COL1 value (1,2,…40,000), which based on dm_db_index_physical_stats generated 5K pages as expected, with no fragmentation.
I then inserted 16K more rows (2K pages) but with Col1 values interspersed randomly (6,2,56,9,etc). This of course generated fragmentation on the NC index amongst (~56%) the expected 7K pages.
Finally, with this baseline, I decided to experiment with fill factor on the NC index. However no matter what fill factor I use (and I rebuild it before doing the ‘random’ COL1 inserts), the fragmentation is pretty much exactly the same.
Am I missing something fundamental?
In my understanding index Fill Factor is only of benefit in updates to records that have had their indexes reorganized before the update. Eg. Insert data in business hours, rebuild/reorganize relevant indexes overnight with Fill Factor adjustment, update data the following day.
Inserts will only be written to new pages and not to existing ones unless the new data has a clustered index key that has to go in between existing records instead of at new pages like in a clustered key where it’is always ascending.
I have few questions, may be not related to the article, but asking thinking you are expert in Mysql indexes.
1) Is it advisable to drop & recreate Indexes on Slave (In Master-Slave scenario)
2) How to estimate the time it takes for creating all indexes
3) does stopping slave from replication & recreating indexes on slave advisable?
Hey Charlie,
I am the farthest thing from a MySQL expert– I write about SQL Server! Might want to try that on a MySQL site or a q&a forum site like dba.stackexchange.com.
Kendra
An explicit FILLFACTOR setting applies only when the index is first created or rebuilt.
Not on reorganizing.
I immediately thought of this post and shared it with our client’s DBA when I found that their FILLFACTOR was set to 10% across the board. There was over 500GB of index data on multiple tables, any they wanted to know why their database was growing so fast… Crazy!
So, I have inherited a script that rebuilds the fill factor on the primary key which is an INT to 50 and than turns around drops the index, alters the column to BIGINT and rebuilds the fill factor back to its original 90.
Does that make sense to anyone?
Paul – for random unrelated questions, your best bet is to head over to a Q&A site like http://dba.stackexchange.com.
Re: “Fillfactor doesn’t impact new pages inserted at the end of an index”
This is exactly the opposite of what I want. I have a clustered PK with records inserted incrementally. Great. Problem is, several varchar fields and an xml field fill in over a few days’ time. So, older records need almost no room to grow (fill=98), where newer ones need a fair amount (fill=60). Is there any strategy better than simply regular rebuilds?
You can try clustering on non-sequential values.
Nice article. Question regarding the index-key column. Do the leaf-level pages have a copy of the index key column too or only all other non-indexed columns ? If the factor on a index is from the perspective of any changes to the index-key column only , what would be the guarantee that the free space is used only by changes on that column only ? I mean is it right to think that the free space can be utilized by any other column in the leaf pages ? Thank you.
I have a question. I have a test scenario where I have added fillfactor = 90 to a clustered index over a random generated GUID primary key. If I create the table without fill factor = 90 then the number of page splits is initially 31 for 3974 rows and splits occur with every subsequent insert. When I include the fillfactor = 90 the number of splits on population rises to 35 but splits stop happening with every subsequent insert. All good so far! The problem is that when I add a non-clustered index although the clustered index still indicates a fill factor of 90% the initial insert is back to 31 splits and splits are happening again on every subsequent insert indicating that the fillfactor is being ignored. Does anyone have an idea as to why this might be happening?
Tim – for Q&A, head on over to https://dba.stackexchange.com
I have a clustered index on ItemId (sequential number). Fill factor was 0. There was 27% fragmentation and page fullness was 88%. Small rows – average row size 24. I changed the fill factor to
My question: If a page is never revisited for update. There are only inserts into the table. When the fill factor is reached. Will a page split occur or just a new page be added? Looks like there are holes in the ItemId sequence, so I assume page fullness was affected by deletes? Would adding the new page cause the fragmentation? Trying to get a better understanding. Thank you.
Greg — for Q&A, head over to dba.stackexchange.com
Thanks!
Almost every table in my database has a fill factor of 0 and many have a 99% fragmentation, even after a de-frag and rebuild its fine for a day then back up to 99% very quickly.
[…] a higher Fillfactor for […]
Great article! Thanks for taking the time to share your knowledge
While dealing with some performance problems a while back, I came across this post:
https://www.sqlservercentral.com/articles/a-self-tuning-fill-factor-technique-for-sql-server-part-1?preview_id=3668579
At the time, I know I had issues getting it to run (test environment). If this script actually works, I’m wondering how well it would improve performance of the SQL Server. I do understand that performance will be a problem initially as it will take a few days for the Indexes to have their fill factors worked out.
I have gone through your article and I have a query. Normally in our applications, we have Identity based INT or BIGINT columns, Delete operation is also soft-delete operation. The point is that if we have IDENTITY based columns, soft-delete operations and very frequent update operations, in that case, does having a fill factor of almost full, say 90% to 95% can cause page split issues? How the Delete and Update operations can cause page splits here?
Hi! Soft deletes are outside of the scope of this blog post, and the answer would depend a lot on how the soft deletes are accomplished, what percentage of the table is soft deletes, how the hard deletes are done later, etc. It’s beyond what I can answer quickly in a blog post comment.
I’ve just watch Jeff Moden’s video on GUIDs as clustering keys, index maintenance & the evils of REORGANISE and the usefulness of FILL FACTOR.
With GUIDs as your primary key you get seemingly random insertions in the clustering index and «bad» page splits are prevented by a fill factor between 70-90% as well as index rebuilds once physical fragmentation gets to 1%. There is no other automatic index maintenance and certainly no index re-orgs. The contention that happens at the end of the index is obviated and there is no waiting for IDENTITY().
His evidence seems compelling and I have to try it out.
Has anyone had practical experience of GUIDs as primary keys in conjunction with fill factors in the 70-90% range? What were your experiences?
The link is here: https://www.youtube.com/watch?v=_07ZP52HxfE
Sean – you’re not going to get great answers by commenting a question on a 6-year-old blog post.