Skip Ribbon Commands
Skip to main content

Tales from a SharePoint farm

:

​​​​​​
Benjamin Athawes > Tales from a SharePoint farm > Posts > SharePoint, SQL server fill factor and index rebuilds – a correction
January 23
SharePoint, SQL server fill factor and index rebuilds – a correction

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.

Disclaimer

I'm relatively new to SQL server configuration and would class myself as an "accidental DBA". I like to think that this blog post is well researched – and the recommendations contained within worked in my specific environment - but be warned that I am certainly no expert. If you plan on making changes in response to this post I suggest you seek professional guidance and TEST everything!

 

What is fill factor anyway?

According to MSDN, 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?

  • According to Kimberly L. Tripp, 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 here which highlights the fact that non-sequential GUID based identifiers are unnecessarily wide, resulting in wasted space). Thanks to Jonathan Kehayias 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:

 Determine Fill Factor using sys.indexes

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
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 sys.dm_db_index_physical_stats.

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 in a SharePoint Content DB

 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:

Database maintenance for Office SharePoint Server 2007 (white paper)

Database maintenance for SharePoint Server 2010

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:

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

Incorrect fill factor setting for SharePoint 2010
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 Neil Hodgkinson 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 MSDN.

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 this KB 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

 

 

 Latest Video

 

Comments

There are no comments for this post.

Add Comment

Title (optional)


Body *


Your Name *


Your name will be published along with your comment.

URL (optional)

Type the Web address: (Click here to test)  

Type the description: 

Email (optional)


What is 1+2? *


To confirm you aren't a bot.

Attachments

MCTS Logo
 

 SharePoint tip of the day

 
  
  
At its core, SharePoint Foundation is a provisioning engineInside Microsoft SharePoint 2010
"STS" stands for "SharePoint Team Services"SharePoint 2010 Professional Administration
"vti" stands for Vermeer Technologies, Inc., the company that created FrontPageVarious sources
20 Web applications per SP2010 farm is a reasonable limitSpencer Harbars
The client-side object model was introduced in SharePoint 2010.Inside Microsoft SharePoint 2010
SharePoint 2010 projects must be configured to target .NET Framework 3.5Inside Microsoft SharePoint 2010
SharePoint 2010 is 64-bit only, no exceptions.Technet
Never access the SharePoint configuration database or any of the content databases directly. Use the OM.Inside Microsoft SharePoint 2010
The MSFT recommended limit of 10 applies to content application pools only, not service endpoints.Spencer Harbars
16 GB RAM is recommended for DB servers in medium sized SP2010 farms.Technet
Pre-grow SP2010 content databases for performance reasons.Technet
A SP2010 Standard FIS license is limited to a single AD domain and related subdomains.sharepoint.microsoft.com
BLOB caching can be used with host-named site collections in SP2010.SharePoint Server Caches Overview
Use an XML webpart with XSL to create a RSS feed viewer in SP2010 Foundation.Dux Raymond
Restoring a SharePoint SQL config DB backup is NOT supported, so ensure you document/backup your farm settings!Sean McDonough
Look after your SQL tempdb. It is used constantly!Michael Noel
Splitting your tempdb for performance should be based on physical - not virtual - CPU cores.Michael Noel
SQL Server 2008 R2 is strongly recommended over RTM for performance reasons.Michael Noel
Implement a 1:1 ratio of physical to virtual cores in your virtualised SP2010 farm for performance.Michael Noel
Office 365 represents a shift from capital expenditure to operational expenditure.Matt Groves
Following an upgrade to SP2010, you can still switch back to V3 visuals using PowerShell.Todd Klindt
A feature instance is what gets created when a user activates your feature definition.Inside Microsoft SharePoint 2010
SharePoint creates feature instances with entries in either a content db or the config db depending upon feature activation scope.Inside Microsoft SharePoint 2010
The only file that is required for every feature definition is the feature manifest file that must be named feature.xml.Inside Microsoft SharePoint 2010
The 4 possible feature activation scopes are Web, Site, WebApplication and Farm.Inside Microsoft SharePoint 2010
SharePoint does not allow two versions of a feature definition to be installed side by side within the same farm: Updated versions overwrite earlier versions.Inside Microsoft SharePoint 2010
The only way to back up your AAM configuration is to document itSpencer Harbars
SP2010 automates SQL index defragmentation & statistics maintenance"Database Maintenance for Microsoft® SharePoint® 2010 Products"
The DBCC CHECKDB command should be run on SharePoint DBs once a week."Database Maintenance for Microsoft® SharePoint® 2010 Products"
Do not auto-shrink databases or configure a maintenance plan that programmatically shrinks your databases."Database Maintenance for Microsoft® SharePoint® 2010 Products"
1 - 30Next
Sponsored Links





 

 Recent Posts

 
  
  
  
0
  
0
  
0
  
7
  
2
1 - 5Next

© Copyright 2010 Benjamin Athawes. Site powered by fpweb.net.