Auto Update Statistics and Auto Create Statistics – ON or OFF for SharePoint 2010 Databases?

  • Article

Update: CTS complained that some customers turned ON auto-create statistics on content databases because of this post. If you think you should turn on this option on content databases after reading this post, please let me know by leaving a comment below. The key message in this post is, in most of cases you don't need to change the default settings of these two options on SharePoint databases. In the extreme case where you have to change them, contact CTS for support first.

If you ask any SQL DBA a question like, “should I turn on Auto Update Statistics and Auto Create Statistics on databases?” You’ll get an answer like, “Yes, definitely!” or probably “It depends”. But if we are talking about SharePoint 2010 databases, the answer is “It really depends”.

In the official document, it suggests to turn off these options and explains why they should be turned off. But what it didn’t tell is how SharePoint will maintain the statistics if the options are turned off. Therefore, it could lead to some confusions like should we turn them off on all databases or just on some databases? should we manually maintain the statistics if we turn them off? etc.

So let me try to explain how SharePoint maintains the statistics first.

Health Analyzer Rule: Databases used by SharePoint have outdated index statistics

As explained in this whitepaper, SharePoint uses this Health Analyzer rule to maintain the statistics (unfortunately the whitepaper didn’t mention the name of this rule). By default, this rule is scheduled to run everyday and it is a repairable rule, meaning it has a repair action. What this rule does is very simple. It will enumerate all SharePoint databases in the farm and check if the database implements a store procedure, proc_UpdateStatistics. If so, it will call this store procedure directly. Otherwise, it will do nothing. 

So now the problem become simple. To find out on which databases we should turn on the options and on which we should turn them off, we just need to check if the database implements the store procedure. If it implements the store procedure, means it has its own way to maintain the statistics and doesn’t rely on SQL server. So the options can be turned off. Otherwise, the options should be on.

Based on my checking, the following databases implement the store procedure, and the two options are turned off by default, meaning SharePoint has its own way to maintain the statistics of these databases. These two options should be left OFF on these databases in most cases.  The list is quite similar to the databases mentioned at the beginning of the above whitepaper. (I hope I’ve exhausted the SharePoint dbs.)

  • Configuration databases
  • Content databases
  • User Profile Service Application Profile databases
  • User Profile Service Application Social databases
  • Web Analytics Service Application Reporting databases
  • Web Analytics Service Application Staging databases
  • Word Automation Services databases
  • WSS_Logging

The only exception is the crawl db of the SharePoint search service. It implements the store procedure but the two options are turn on as well. I am not sure if it was intended to be set like that or what.

So let us go back to our original question, should we turn on or off the two options on a SharePoint 2010 database? My answer would be it depends on which database you are talking about. If the database knows how to maintain its own statistics, the options should be turned off and SharePoint will take care of it. Otherwise, the options should be turned on.