SQL SERVER - Fill Factor - Instance Level or Index Level - SQL Authority with Pi...

One of the conversations that keep on coming up during Comprehensive Database Performance Health Check is at what level one should set the fill factor – Instance Level or Index Level. I give clear guidance to my clients and let us discuss it today.

Instance Level

You can always set the fill factor at the instance level. However, when you set the fill factor at the instance level, it will impact all the indexes on the instance with all the databases where the fill factor is not set. Now this means pretty much every single index on the database is impacted.

When to set this one?

When you have a single database or multiple databases where you do not know what is the right fill factor for all of them and want to play on the safe side by leaving a little space in all the indexes to avoid the page splitting issue.

I prefer to set the instance-level fill factor around 95 with the following script.

In most cases, you should not set this one and leave it to 0 or 100. If you have time and energy you should set this one at the index level.

Index Level

While I personally prefer to set the fill factor for every single index, it has an almost impossible task every single time. The process to find the right fill factor, not a straightforward one. Every expert will give you their own logic and experience. I personally follow also very complicated calculation, however, let me try to explain to you this in a simple single sentence.

If a table gets updated 10% time during a week and I rebuild the index every week, I keep the fill factor to 90 (100-10). 

Please note the statement above is very subjective and I often personally change the value for the index. There are lots of variables like columns used in the table, their distinct values as well as the frequency of the index rebuilding and statistics update.

Here is the script to set up the fill factor for the new index.

If you already have an index on your system and want to set up the fill factor for that you can use the following script.

Conclusion

I personally like to set the fill factor at every single index, but that is almost impossible most of the time when there are hundreds of tables on the database and each has many indexes. In that scenario, during  Comprehensive Database Performance Health Check we identify a few top tables for which if we change the fill factor, we get the most benefits.

When it is not possible to identify the top tables or we are in the just first iteration of the health check, I often suggest to my client that we change the instance level settings to around 95 or any even higher number like 98 and keep working on the low hanging fruits for the performance tuning. Once we take care of the other issues, we can return back and start looking at the index level fill factor and reset the instance level fill factor to the default value.

As I said everyone has their methodology to set the fill factor and I would like to hear yours. Please leave a comment on this blog post. You can also reach out to me on Twitter for further conversation.

Reference: Pinal Dave (https://blog.sqlauthority.com)