Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

SharePoint 2010 Databases Maintenance Health Analyzer Rules

MSSQLTips author Ray Barley By:   |   Read Comments (3)   |   Related Tips: > 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:

SharePoint 2010 Central Administration Monitoring Health Analyzer

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:

SharePoint 2010 Central Administrator Category Performance

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:

SharePoint 2010 Health Analyzer Rule Definitions

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:

SharePoint 2010 Health Analysis Job

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.


Last Update: 3/21/2012


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, October 19, 2012 - 6:05:00 PM - mardukes Read The Tip

Yes, but how can you tell when the health analyzer decided it was time to defrag or update?  When were indices last maintained?


Sunday, October 21, 2012 - 9:00:35 AM - Ray Barley Read The Tip

I don't think that there is any sort of logging / history that would give you the information you're asking for.  I checked the stored procedures and there are PRINT statements in them so if you ran the procs yourself you would see the indexes being rebuilt and the statistics updated.

If a timer job throws an error you can see those details; click on the hyperlink in the Status column of the job history and you will see details about the job that include an error message.

Just a wild guess but since the number of timer jobs running is totally out of control, they don't log "success" information; it would just be too much data.


Tuesday, March 25, 2014 - 1:06:05 PM - yogendra Read The Tip

Nice Article !!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.