By: Ray Barley | Updated: 2012-03-21 | Comments (3) | Related: > SharePoint
Problem
I'm a DBA and trying to understand what sort of database maintenance is automatically performed in SharePoint 2010. I ran across the Health Analyzer rule definitions for fixing database fragmentation and stale statistics. I have been unsuccessful in finding out any real details about how these things actually work. Can you provide some insight? Check out this tip to learn more.
Solution
Health Analyzer is a feature in Microsoft SharePoint Foundation 2010 that enables administrators to schedule built-in jobs that identify problems with SharePoint and potentially automatically fix those problems. To access the Health Analyzer launch SharePoint Central Administration from the Microsoft SharePoint 2010 Products program group and select Monitoring; you will see the following page:
Click Review problems and solutions to view issues that have been identified; click Review rule definitions to show the rule title, schedule, enabled state, and whether to repair issues automatically as shown below:
The rule definitions that fix index fragmentation and outdated statistics are highlighted in the above screen shot. They run daily and perform an automatic repair. Click on the rule title to display its properties as shown below:
To determine when these processes run and what they do requires a little bit of poking around. First I started SQL Server Profiler, then ran the Health Analyzer rules on demand (see Run Now button in the previous screen shot), and watched the stored procedure names being executed in Profiler. I saw two familiar stored procedure names: [dbo].[proc_DefragmentIndices] and [dbo].[proc_UpdateStatistics]. I think these were the same stored procedure names in SharePoint 2007. Based on a quick review of these stored procedures, the following are the main points:
[dbo].[proc_DefragmentIndices]
- The sys.dm_db_index_physical_stats DMV is used with the LIMITED scanning mode; this is the quickest way to gather the pertinent info on indexes; e.g. average fragmentation percentage
- Rebuilds indexes where the average fragmentation percentage is > 30% and the row count is > 10,000
- Indexes are rebuilt online if SQL Server edition is Enterprise else offline is used which locks the table
[dbo].[proc_UpdateStatistics]
- Drops statistics where auto_create = 1 and object type <> ‘S' i.e. system base table
- Note the database option AUTO_CREATE_STATISTICS should be set to OFF which is a SharePoint best practice
- Updates statistics on indexes where (sys.sysindexes.rowmodctr * 100) / (sys.sysindexes.rowcnt + 1) > 1
The monitoring page (first screen shot above) has a link to review timer job definitions. Timer jobs in SharePoint are kind of like SQL Server Agent Jobs in that they encompass various utility functions that are run according to a schedule. The Health Analyzer rules are run by timer jobs, but which one(s)? Return to the monitoring page and click Review job definitions; what we do know is that the Scope is Any Server and the Schedule is Daily. I ran the timer jobs on demand that matched the Scope and Schedule and watched the Profiler output until I saw the familiar stored procedures noted above. The timer job that runs the database maintenance Health Analyzer rules is Health Analysis Job (Daily, Microsoft SharePoint Foundation Timer, Any Server); click the job title on the Review job definitions page to display the schedule properties as shown below:
If necessary you can change the schedule on this page.
I suppose the idea of the Health Analyzer rules is to automate identifying and fixing things especially when there is no DBA on staff to handle the care and feeding on the SharePoint databases.
Next Steps
- Keep in mind that although you as a DBA may not know much about SharePoint, you can figure things out by using the tools that you use all the time e.g. SQL Server Profiler.
- If the above stored procedures do not meet your needs, you should disable the Health Analyzer rules and create your own maintenance jobs. Make sure to follow this guidance from Microsoft about what you can and can't do to SharePoint databases if you want to get support.
- Take a look at the tips on MSSQLTips.com in the SharePoint category; many of these are written specifically for DBAs.
- Stay tuned for a future tip that will cover additional administration topics related to SharePoint databases.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-03-21
