Overview

Today we are going to be taking a look at the fill-factor option that is available within SQL Server 2005 and later, in the context of Microsoft SharePoint.

Aside from providing reasons for caring about fill-factor, I point out a mistake in the SQL maintenance guidance for both SharePoint 2007 and 2010 that relates to index rebuilds that I have made Microsoft aware of.

What is fill factor anyway?

According to , the fill factor option “determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth”. The idea is that an appropriate fill factor should reduce page splits whilst maintaining performance and using space efficiently.

In case you are wondering, a “page” in this context isn’t something that sits in a Pages library after activating the SharePoint publishing infrastructure. It is the basic storage building block in SQL Server and is exactly 8192 bytes in size.

The default fill factor option for SQL server 2005 and later is “0”, which really means “completely fill each leaf-level page so as not to waste space and potentially optimise read performance”. A fill factor setting of 0 or 100 both result in pages being 100% full.

I’m a SharePoint admin – why should I care about this?

• , fill factor is “the MOST IMPORTANT thing to understand about index maintenance and reducing fragmentation (especially in databases that are prone to it)”. I’m not going to doubt this given Kimberley’s credentials.
• SharePoint uses GUIDs (unique identifiers) as primary keys for all tables which causes page splits and massive fragmentation (there is a good article on this  which highlights the fact that non-sequential GUID based identifiers are unnecessarily wide, resulting in wasted space). Thanks to  for explaining this concisely.

Therefore, a non-default fill factor is – according to various clever SQL bods – appropriate for SharePoint in order to reduce fragmentation and improve performance.

How do I check my fill factor?

As mentioned in the two database maintenance documents that I link to below, you can check your fill factor by querying the sys.indexes catalog view. Here is a simple example (replace DatabaseName with the name of your DB):

use DatabaseName

select name,fill_factor from sys.indexes

order by fill_factor desc

And, for good measure here is a screenshot of the results:

Determining fill factor using sys.indexes

Of course, you can also determine the default server-wide fill factor using the server properties dialog:

Determining default server-wide fill factor (this setting may not be appropriate for your specific environment)

OK, I know my fill factor. So what?

Now that you know your fill factor you probably want to determine your fragmentation level to work out if a change might help. The “Database Maintenance for Office SharePoint Server 2007 (white paper)” document link below includes instructions on how to do this. Below is an example.

1. Determining the database ID (replace DatabaseName with the name of your DB):

select DB_ID(N’DatabaseName’) as [Database ID]

2. Determining average fragmentation (replace DBID with the integer ID found in step 1):

select database_id, index_type_desc, alloc_unit_type_desc, avg_fragmentation_in_percent, page_count from sys.dm_db_index_physical_stats (
DBID
, 0
, NULL
, 0
, NULL
)
order by avg_fragmentation_in_percent desc

I’ll leave out discussion of the specific parameters above as it’s probably beyond the scope of this blog post, but Technet contains plenty of information on 

The result of the above script is as follows. As you can see a number of indexes are heavily fragmented in this SharePoint 2007 content database:

 Determining index fragmentation using sys.dm_db_index_physical_stats.

Can’t we just set the fill factor to something really small to prevent the issue?

Unfortunately this isn’t a realistic option because – based on the MSDN article above – fill factor is roughly proportional to read performance and a low value will result in a lot of wasted space. For example, if we were to set this value to 10% our read performance might suffer by up to 90%.

Notice that I say “really small” instead of 0 because 0 is the default setting which as we now know sets fill_factor to 100%J.

The Microsoft guidance

If you were to read through the following documentation, you might think that the guidance from Microsoft on an appropriate fill factor is quite clear:

The links recommend a fill factor of 70% and 80% for SharePoint 2007 and 2010 respectively.

However if, like a lot of “accidental” SharePoint DBAs you decide to follow the guidance to implement an appropriate maintenance plan, you will soon come across the following screenshots. The big, idiot proof dialogues are really there for me so that I don’t refer to this blog later on and copy the wrong settings into my environment:

Free space per page percentage appears to be the inverse of the fill factor guidance for SharePoint 2007…

And it’s the same issue for SharePoint 2010!

Just to be clear, the above screenshots show free space per page percentage values that are the exact opposite of the written guidance contained within their respective documents. E.g. in the case of the SharePoint 2010 recommendation, the screenshot suggests that you change free space per page to 80% – the written guidance states that pages should be 80%full. In other words, the number in the screenshots above should – as far as I’m concerned – be 30% and 20%.

Being relatively new to the world of SQL server configuration I scratched my head for a few minutes trying to work out whether I had misunderstood the written guidance. I defined an index rebuild task according to the screenshot above in a SharePoint 2007 test environment, and used a Dynamic Management View (DMV) to validate the resultant fill factor setting. To be more specific, I queried the sys.indexes catalog view and – confirming my suspicions – the fill_factor column displayed a value of 30!

“Logical inversion failure”

I discussed my observation above with  from Microsoft. Neil is a SharePoint 2007 and 2010 MCM and as well as knowing a shed load about SQL is a very helpful guy.

He describes the issue as “logical inversion failure” which is a lot more concise than my attempt to explain it. Rather than putting the recommended free space amount in the screenshots, Microsoft appear to have mistakenly put the inverse quantity: the recommended percentage that pages are filled.

Neil assured me that the Technet documentation will be updated in due course and I’d like to take the opportunity to say thanks for the prompt response.

He also made one observation that I hadn’t considered: setting a server-wide fill factor may not be appropriate as non-content databases and particularly non-SharePoint databases (in the case of a shared instance) may not benefit from the change.

What’s the damage?

I know a bunch of SQL people who have never even considered using the UI to create a maintenance plan as they prefer to use scripts for everything. Those people will most likely be unaffected by the typos in the two screenshots above.

I also know a lot of “accidental” SharePoint DBAs (I would consider myself to be one) who like to use the SSMS GUI to create maintenance plans due to the shallow learning curve. There is a fair chance that those people will be affected by the typos shown above, in which case I would consider it important that the fill factor setting is rectified.

The good news is that as far as I am aware this is relatively straightforward to resolve by changing the “free space per page percentage” to either 30% (SharePoint 2007) or 20% (SharePoint 2010) and performing an index rebuild. Although this is a very expensive operation, it could well be a one-off task assuming that you have the correct fill factor set. My advice would be to pick a sensible time during off-peak hours where your users won’t be too cheesed off if your SQL server CPUs(s) happen to hit 100% usage for a while (obviously this will depend on your specific configuration but you get the idea).

Note that although scheduling an index reorganisation task can often be a suitable alternative to rebuilding them (it’s cheaper), it doesn’t change the fill factor assuming you are using the SMSS UI. The fill factor option “applies only when an index is created, or rebuilt” according to .

Also note that if you are using Windows SharePoint Services 3.0 SP2 and later, the problem may not be as significant as you might think, see the next section…

Do I even need to schedule an index rebuild?

I couldn’t finish this blog post without mentioning the proc_DefragmentIndices stored procedure that was introduced for WSS 3.0 in  and remains in SharePoint Foundation and Server 2010. From Windows SharePoint Services 3.0 SP2 and later, the stored procedure is executed as part of a timer job to reduce fragmentation for search, profile and content databases.

The stored procedure rebuilds indexes that are heavily fragmented in order to improve performance. This is great as it means that Microsoft recognised that the use of non-sequential GUIDs as primary keys leads to heavy index fragmentation.

In light of the purpose of this stored procedure, do you need to schedule regular index rebuilds via a maintenance plan? “Probably not” is the best answer I can come up with given my limited knowledge of SQL server and even more limited knowledge of your specific environment. Personally, I find that scheduling a weekly index reorganisation task and leaving index rebuilds to proc_DefragmentIndices keeps fragmentation reigned in.

One more thing…

If you take a close look at the proc_DefragmentIndices stored procedure mentioned above, you will notice that Microsoft rebuild indexes with a fill factor of 80 for both SharePoint 2007 and 2010 despite the guidance contained in the 2007 white paper.

My stance on this is that if a stored procedure is going to execute on a daily basis and potentially change my index fill factor to 80 for heavily fragmented indexes, I may as well set the fill-factor setting to 80 (rather than 70) for consistency. You might find that your results differ but in our environments I have found that 80% fill factor (20% free space per page) is appropriate for both products.

Summary

• Setting an appropriate fill factor according to the MS guidance is definitely worthwhile as it ensures that the fill factor is correct from the off, reducing index fragmentation due to page splits.
• A server-wide fill factor may not be appropriate, particularly if you are sharing your SQL instance (i.e. it isn’t dedicated to SharePoint).
• The Microsoft documentation needs to be updated to show the correct settings for an index rebuild.
• An index rebuild maintenance task can be useful to correct fill factor as a one-off.
• Otherwise, the provided stored procedure can be relied upon to set fill factor correctly for indexes that are heavily fragmented.
• A maintenance plan is still important to automate index reorganisation and check the integrity of your databases (in addition to backups).

I’d be interested to read any thoughts or further insights you might have on this.

Ben