Share via

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.

Comments

  • Anonymous
    August 08, 2013
    Actually the "official" documentation you reference makes absolutely no mention of Auto_Update_Stats option.  The official documentation states to turn off Auto_Create_Stats.
  • Anonymous
    September 15, 2013
    I'm running into an issue where SharePoint 2010 takes forever (>20 minutes) to upload even a small file into a document library. Usually first user in the morning, after it completes it's continually fast again. SQL Triage discovers it's ALWAYS this query that "hangs" at that moment:SELECT StatMan([SC0], [LC0]) FROM (SELECT TOP 100 PERCENT CONVERT(varbinary, SUBSTRING ([Content], 1, 100)++substring([Content], case when LEN([Content])<=200 then 101 else LEN([Content])-99 end, 100)) AS [SC0], datalength([Content]) AS [LC0] FROM [dbo].[AllDocStreams] WITH (READUNCOMMITTED)  ORDER BY [SC0] ) AS _MS_UPDSTATS_TBLInvoked by the SharePoint service account. It seems that the creation of statistics is triggered by the upload of the small file, which does not seem to be occurring on a regular bases ?Should I enable auto-create-statistics / auto-update-statistics to resolve this issue ...?
  • Anonymous
    January 07, 2014
    Update: I blogged about my specific issue around the StatMan here: schoennie.blogspot.nl/.../slow-statman-query-issue-with-sp2010.html
  • Anonymous
    April 10, 2014
    And, looking at proc_updateStatistics, it appears the main content tables are always subjected to a full scan.           o.object_id = @objectid&nbsp;      IF ((@objectid = object_id(N'[dbo].[AllDocs]')) OR          (@objectid = object_id(N'[dbo].[AllUserData]')) OR          (@objectid = object_id(N'[dbo].[AllLinks]')) OR          (@objectid = object_id(N'[dbo].[AllUserDataJunctions]')))       BEGIN           SELECT @bNeedFullScan = 1
  • Anonymous
    April 22, 2014
    How about just turning off the health Analyzer Rule's and maintaining indexes and statistics the way we DBA's usually do?Many of my customer don't have a dedicated SQL backend for SharePoint, but instead have a multipurpose backend hosting SharePoint and other user databases.
  • Anonymous
    April 22, 2014
    Of my 150+ SharePoint content DB's about a dozen report "missing statistics" in the profiler.  Whats up with that?