** Let me share a quick backdrop concerning this article. One Sunday morning back in 2003 this came into fruition. Was just playing with the idea of writing as one of my-coworkers at the time was pushing to make a career of it. Was under scrutiny at work because the number of transactions a second and the average time for a transaction went into a negative direction. We are talking milliseconds here. Milliseconds are money in a lot of cases. Put another way, milliseconds matter. What caused the issues was the post-reindex aftermath. Those indexes were packed and ready for SELECT statements, but they weren't ready for changes. Memories of laying on the carpet in an unfurnished flat (one-bedroom) in St Charles Missouri while still in a fuzzy state of mind are as fresh as ever. This is also known as the pre-coffee state of mind.
This is a great topic and this article isn't completely obsolete. There are some environments out there where page splitting is something to keep an eye on and to counter. There are also plenty of environments where it shouldn't be a concern. Anyway, I think an updated version of this diatribe is in order. For now....here it is. With all of it's flaws!
Are Fill Factor settings important? I believe so. Although I have read from many sources that mention the default value is generally good enough. I have also read that you shouldn't change the Fill Factor value unless you know what your doing. As with everything else in the world of Database Administration it is a good idea not to change anything unless you understand what you are doing. This is great, but how do you determine the value for a Fill Factor and know what your doing? I have searched for this answer and was hoping to find a nice easy formula to set my values. Guess what? I didn't find it.
To understand the Fill Factor it is beneficial to have a good understanding of clustered and non-clustered indexes, heap tables, extents, pages, page splits, and the results from the DBCC SHOWCONTIG command. If you are not familiar with these terms then I recommend reading books online or another reputable source for background. Understanding the makeup of SQL Server’s physical file architecture is very important.
Basically the Fill Factor is the percentage of the leaf node page that is used to store the records when the index is created or rebuilt. We may have all heard this before, but there is a key point in this statement that is often overlooked. "When the index is created or rebuilt." Once a page split occurs the Fill Factor is obsolete in the two new pages. If an index has a Fill Factor of 80 when it is created then most of the pages will be about 80 percent full. After one of the pages is filled, a new record is added or changed, then the page splits into two new pages that are approximately fifty percent full. The original Fill Factor setting has no affect on these two new pages. If you don't rebuild your indexes then there is a good chance your indexes have had many page splits, and the Fill Factor setting when it was created is obsolete unless there have been minimal changes to the number and content of the records in the table.
Still, what percent should we set the Fill Factor too? A low Fill Factor setting will increase the number of pages required for the index and subsequently cause more reads; this will hurt performance. A high Fill Factor could cause a lot of page splits; this too will hurt performance. Page splits consume both CPU and I/O resources, but are a necessary evil. You may have heard that the Fill Factor should be high for read only tables and lower for tables with a lot of changes. There is some truth to this, but how do you know? If your environment is anything like mine then figuring out which tables are which will be a time consuming task. One of my databases has over 22,000 tables and you can imagine how long this would take. And indexes on the same table may fragment at different rates. You want to keep those complaints about performance at bay so how do you define a 'high' and a 'low' setting?
What I am about to explain is not an exact science or the right approach in every situation. What this is, is my "general" rule of thumb that I often follow; similar to putting an index on foreign keys when creating the physical model. So, I offer a caveat. Try this in a test environment or just work on one or two tables. Then see how it works for you and the environment you work with.
Find a balance between page splitting and index size. A low Fill Factor reduces page splitting while increasing the number of pages needed for the index. A high Fill Factor is just the opposite.
Keep the number of extent switches as low as possible, and the pages contiguous. The Scan Density should be as high as possible for each index. I like 90 percent or higher even though I do not always reach this between index rebuilds.
Prevent spikes in page splits/sec after index rebuilds. This can literally shut down your server.
Keep the Average Page Density as high as possible for each index. High Average Page Density values mean fewer reads.
Have the Average Page Density stay close or increase above the value for the Fill Factor setting. In a perfect world the Average Page Density would go from the Fill Factor value to 100 percent between index rebuilds.
Keep database growth stable or consistent. You may want to monitor is the amount of space used in the files before and after you rebuild the indexes. There's a good chance the amount of space used is reduced after the rebuild.
Keep performance as consistent as possible between index rebuilds. If I had to mention just one goal, this is it! The previous goals lead up to this one. I like stability and consistency across the board. This will be one less thing to worry about in the event of performance issues.
First Things First, Let's Get Started.
Define a schedule for rebuilding your indexes. My schedules vary according to the database. Most of the smaller ones I run a scheduled job that executes a DBCC DBREINDEX command on all of the user tables once a week during non-peak hours. Some databases receive personalized attention at specified intervals. This is a very important part of how I am suggesting to set your Fill Factors. The Fill Factor setting you choose will become dependent on the frequency of the index rebuilds
Prepare to obtain some database metrics. You will be able to add more to this list, but I wanted to share a few of the more critical ones. Through Performance Monitor watch Page Splits/Sec. Remember page splits are a necessary evil. You will also want to obtain information from DBCC SHOWCONTIG. I have a job that executes once a week that pulls the results from all of my servers user databases, then loads it into a table. Of course I have added columns for Instance and database name, which allows me to easily query the information. Run DBCC SHOWCONTIG with the ALL_INDEXES and TABLERESULTS options. The TABLERESULTS option allows for easy reading and manipulation. The ALL_INDEXES option is a necessity because without it all you will see is the Clustered index or the heap. We need to see the big picture and treat every index individually. Just because one index on a table is in good or bad shape it doesn't mean that all of the indexes on the table are in the same condition. One index could be on field with dynamic values while the another index is fairly static. You should also obtain a list of table names, indexes, and the OrigFillFactor values in the sysindexes table.
Okay, now grab your database metrics and start your baseline. The DBCC SHOWCONTIG command will grab some resources so you may wish to do this when the database is not very busy. Your database may have a lot of tables and indexes so you may also wish to break your result sets up alphabetically or by row count. The results from this command may be a little intimidating at first. After working with the results for a while you'll understand the importance of the values.
Start your DBREINDEX commands per the schedule. Always be cautious when executing this command. It is resource intensive, there is potential for database corruption if something happens to the server, and in a counter-intuitive manner the performance in the database may decline. Say for example, the Fill Factors are set to 100 and the database is highly transactional with a lot of inserts. Guess what happens? There will be a lot of page splits. I have seen this first hand. Because the Fill Factor settings were improperly set, rebuilding the indexes hurt performance for a couple of days.
After rebuilding the indexes monitor the page splits/sec and see if it has increased. Obtain the Page Splits/Sec value midway between index rebuilds and one more time 24 hours prior to the next rebuild. You should obtain this metric 3 times in every cycle. Once after the index rebuild, another at the midpoint before the next rebuild, and one just before the rebuild.
Within 24 hours prior to the next rebuild run DBCC SHOWCONTIG and save this information.
With the original Fill Factor values and the results from the DBCC SHOWCONTIG command you are ready to start preparing for changes to the Fill Factor settings. With a schedule in place to rebuild the indexes you can monitor the amount of fragmentation to each index in a given time frame; assuming the transactional volume is stable.I don't bother to adjust indexes that are small. Not small by row count, but by the number of pages. This reduces my analysis time and changing the Fill Factor probably will not have a lot of affect. For each index take a look at the Scan Density value. This is 'generally' what I key off, but not always. If the Scan Density is at 24% this is a good sign that the index was splitting and something needs to change. If the Average Page Density is at 50% when the Original Fill Factor is at 90 then here too there are probably some issues. I may also see situations where the Original Fill Factor is at 80 and the average page density is at 90 percent where I might be able to increase the Fill Factor now that I know when the indexes will be rebuilt again.
General Rules/Guidelines for Determining Fill Factor Settings
- Be consistent with the frequency of index rebuilds.
- If the index is small then don't adjust the Fill Factor.
- Monitor and make changes at the index level, not the table level.
- Keep the Fill Factor values at 0, or between 75 and 100. If any Fill Factors need to be set lower than 75 you should be confident that this is what is needed. Which very possibly is the case in which you will keep seeing a low Scan Density and a low Average Page Density. Do some discovery work and find out how often the table is read from before setting lower Fill Factors.
- If the Scan Density is at 90 percent or above, leave the Fill Factor alone. At least for the first couple of passes at changing the Fill Factors.
- If the Scan Density is between 60 and 90 percent then bring the Fill Factor down with small decrements. Say 2 percent.
- If the Scan Density is lower than 60 then increase the amount of the decrement. Usually I will pick a value that is halfway between the Average Page Density and the Original Fill Factor. For example, if the Original Fill Factor is at 100 (or even 0) and the Average Page Density is at 60 then I would set the Fill Factor to 80.
- If the Average Page Density is higher than the Original Fill Factor and the Scan Density is close to 100 then raise the value of the Fill Factor. This is great, because you know you can pack the pages a little further. For example, if the Fill Factor is at 80, the Scan Density at 98, and the Average Page Density is at 88 then on average the pages filled by 8 percent just prior to the next index rebuild, and are still fairly contiguous; I would increase the Fill Factor, but not bring it higher than 92 since 8 percent growth brings me to 100 percent and another page split. I would probably bring the value to 88 and watch what happens.
- Try to avoid drastic changes to the Fill Factor. I intend on taking more than one pass at changing the values so I can adjust a little at a time.
So there you have it. Some general rules and guidelines to set Fill Factors. If you rebuild your indexes at regular intervals then you can determine just how much your indexes are going to fragment. Use the values from the DBCC SHOWCONTIG that was executed prior to the next index rebuild to assist with determining what the new Fill Factor value should be. By monitoring you will develop some instincts with page splits and determining the Fill Factors. I have yet to find a formula for determining Fill Factor values, but did establish some procedures. My general rules are not cut and dry and I do like to keep the Fill Factor value as high as possible. They are basic guidelines with many exceptions and additions.
After you are prepared for obtaining and reviewing the metrics you will be amazed at just how easy this is. And just how quickly you can make things better. Don't expect to make things perfect. Just be sure to make things better and work towards the goals.
Other Points and Considerations
- Indexes in older, more established databases generally should have higher Fill Factors than newer younger databases. This is easily explained. Many tables grow at a constant rate, not linearly or exponentially. Imagine there is an index that consumes 1000 pages initially, grows by 500 pages between index rebuilds, and the Fill Factor is set to 90. This leaves roughly 1000 pages times (.10 * 8096), which equals roughly 790 KB of room for growth/changes. This may be considered a 'high' Fill Factor setting which you may want to lower. But once the table has 10,000 pages with the same Fill Factor of 90 there will be approximately 7906 KB of room for growth and changes. Once the index has 10,000 pages it could potentially be beneficial to raise the value from 90. The size and growth of the index can and ought to be a consideration when setting the Fill Factor.
- Be more concerned with large tables/indexes with low scan densities or large drops in average page density than smaller tables/indexes. It takes more page splits to bring the percentages down on a larger index. Remember we're trying to reduce the page splits! A very large index that drops only 2 percent in scan density probably experienced more page splits than a small index that dropped 30 percent in scan density.
- Pay attention to more than just the scan density and average page density results from the SHOWCONTIG. There is a lot of relevant information here.
- The Original Fill Factor value in the sysindexes table does not represent the Fill Factor value when the index was created, or at least not necessarily. This is the value that was used when the index was last created or rebuilt.
- You may want to consider rebuilding your heap tables too.
After you feel the Fill Factors are properly set plan on revisiting them after a period of time. This is an ongoing maintenance.