Friday, December 10, 2010

SharePoint 2010 Content Databases -- Auto Update Statistics Setting

I noticed that our SharePoint 2010 content databases were being set to Auto Update Statistics Off, and Auto Create Statistics Off.  Since we normally run with Auto Update Statistics on, and that was standard in WSS 3.0 and SharePoint 2007, I did some checking.

It's the default for new content databases added and/or attached, and is part of the upgrade process for databases moved over from a WSS 3.0 or SharePoint 2007 farm.  This is only the case with content databases.  The application and config databases default to your model configuration if created by SharePoint, and are not changed if you use DBA created databases.


I could only find one official reference to this in Microsoft's documentation, which is curious to me since their explanation indicates that performance is negatively impacted if your forcibly re-set Auto Create Statistics to on (although no mention of Auto Update is made).  This Microsoft TechNet article contains the information as a passing reference.  I've copied the relevant text below.

Do not enable auto-create statistics on a SQL Server that is supporting SharePoint Server. SharePoint Server implements specific statistics, and no additional statistics are needed. Auto-create statistics can significantly change the execution plan of a query from one instance of SQL Server to another instance of SQL Server. Therefore, to provide consistent support for all customers, SharePoint Server provides coded hints for queries as needed to provide the best performance across all scenarios.

Thanks to rjgd80 for pointing out that my original post was not clear regarding the Microsoft article.

2 comments:

  1. The article talks about auto create statistics not auto update statistics. These are diffeent settings.

    ReplyDelete
  2. Thanks for catching that, I wasn't stating it very well, I'll update the post.

    ReplyDelete