SharePoint 2010 and the Health Analyzer

Sean, itgroove President and SharePoint MVP asked me to produce a "Coles Notes" about SQL tuning and SharePoint 2010 for our internal blog.  I thought it might be of some help to you.

Everything below is a precis of what Microsoft has published on SharePoint 2010 and SQL here: http://www.microsoft.com/download/en/details.aspx?id=24282)

To make a long story (25 pages, actually) short, SharePoint 2010 does a ton of SQL tuning and clean up all by itself via the "Health Analyzer" that is built in to SharePoint 2010; the Health Analyzer is located under Monitoring in Central Admin.

Health Analyzer has a series of built in rules and stored procedures that are fired off on a regular basis by the SharePoint Timer Service. These rules will do things like report on problems within the SharePoint SQL database(s) and also performs a lot of regular tuning very similar in scope to the types of things that can be done with database maintenance plans.

The big thing that the Health Analyzer handles is automatic index defragmentation (online if using Enterprise or Developer versions of SQL, offline for all other versions). Many indexes are handled this way although there are indexes that will be reported as fragmented that have to be manually defrag’d. Index health is critical to the overall operation of SharePoint so it’s no surprise that the Health Analyzer performs these functions.

The Health Analyzer will not defrag tables but it will report on tables that should be defrag’d. There are very few tables that need regular defraging so it is best to let the Analyzer alert you to what is required.

Microsoft recommends that you allow the Health Analyzer rules to manage the whole index defragmentation routine and to NOT try and do it via maintenance plans unless there is a compelling reason to do so. Also, note that SharePoint databases do NOT support DROP INDEX and CREATE INDEX commands so all the old DBA tricks of dropping and recreating indexes won’t work in a SharePoint 2010 environment.

The Health Analyzer will report on databases that are candidates for shrinking and this is a task that needs to be done manually. Basically, the idea here is that if gobs of data have been deleted or moved out of a database then the database file is probably all "swiss cheesy" with holes. Shrinking the database squashes out all the fragmented space then the database size can be grown out, again, and data will be contiguous.

Microsoft also recommends regular CHECKDB runs be performed against the databases to ensure data and indexes are not corrupted. We generally have this function run as part of scheduled backups (BackupExec or other backup program). If this function is not run on a regular basis by backup it is then probably a good idea to run it manually on a regular basis.

Finally, Microsoft recommends SharePoint databases have the "index fill factor" changed from the regular default setting of "0" to a setting of "80". The fill factor is a "server-wide" setting meaning it is set across a SQL instance – if you run other databases in the same instance as that supporting SharePoint then the fill factor will affect those databases, as well. The fill factor controls how data "spills" through the index nodes. The recommended setting for SharePoint apparently helps to keep index fragmentation down to a minimum.

That’s it for the Coles Notes. See the document for the gory details.