Sharepoint 2010 DataBases and SQL Update Stats

Posted on 2012-04-11
Medium Priority
Last Modified: 2012-04-17
I have default install on Sharepoint 2010 that uses a Sql 2008 DB on a seperate server. I noticed that all of my Sharepoint DB's are set to false for the " AutoUpdate Stats" in SQL.

here are my questions.
1) How does an update stats job differ from a index defrag?
2) Why are all of the sharepoint DB update stats disabled by default?
3) SHould I enable this? What will I gain by doing so?
Question by:compdigit44
  • 4
  • 3
LVL 11

Expert Comment

by:Greg Burns
ID: 37833620
SharePoint 2010 (and to some extent SharePoint 2007) have timer jobs that will trigger an update stats, or reindexing using their own internal logic.  For this reason, most databases created via SharePoint will turn those SQL-triggered options off.  

There are ways you can disable the SharePoint jobs and do this yourself.  You can take a look at this blog post (note, this post is a little old and talks about SharePoint 2007):


Here is some more relevant information for SP 2010.

Timer Job reference:

Recommended Database Maintenance for SharePoint:
LVL 11

Expert Comment

by:Greg Burns
ID: 37833917
OK to address your questions directly:

1) How does an update stats job differ from a index defrag?

An index is a pointer to a physical location where data resides.  As you move and change data, there are gaps in the data, just like files on a disk.  

Per Microsoft: "Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly."

So you have two options: A reorg, or a rebuild.

A reorg is where the index is updated to match the physical layout of the data (actually its slightly more complex than that, but you get the idea).  A reorg is an "online" operation, so the table doesn't have to be taken offline to update the index.

A rebuild is a more drastic action.  It actually physically moves the data to match the index order, which significantly improves lookups.  With a Reindex, the table must be temporarily taken offline as records are moved around, unless you have SQL Server Enterprise Edition, which offers an "online reindex" option.  

So how does all this differ from an "Update Statistics" command?

SQL Server likes to plan ahead.  Periodically it will gather information about your data and how it's laid out: this information is called "Statistics". When you update statistics, you are telling SQL to re-gather information and recompile all the queries so they run with the updated stats.  

If the physical layout of your data has changed, you'd want to update the statistics.  So what I usually do after an index rebuild is to follow up with Update Stats.

In the case of your databases, these automatic updates are turned off, because SharePoint wants to handle Stats all by itself.

Microsoft recommends against running Update Stats too often, especially in large databases, because it does take time to recompile stats.  

Reorganize and Rebuild Indexes


2) Why are all of the sharepoint DB update stats disabled by default?
Explained in the previous post

3) Should I enable this? What will I gain by doing so?
Since SharePoint is managing its own databases, I'd recommend you leave them alone.  

On the other hand, SQL Server maintenenace plans are "fully supported" and you can create a maintenance plan to trigger reindexing and update stats.  You won't hurt anything by doing so.  

But do you need to?  It's debatable, and it really does depend on the state of your databases, their fragmentation, and the perceived performance.  These are DBA questions and does get a bit science-y.  If you are the DBA, then it's your call, of course.  But keep in mind the SharePoint solution is supposed to handle all this for you; it's designed for enterprises, but also small companies that don't have an IT staff.  

If you are looking for answers because your SharePoint farm is slow, then that is a whole different ball of wax.
LVL 20

Author Comment

ID: 37834250
How can I tell if and when the last time the sharepoint db stats were updated.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 20

Author Comment

ID: 37834548
Can run a DB stats update help improve the performance of a DB?
LVL 20

Author Comment

ID: 37840492
Any thoughts on this? Thanks Again
LVL 11

Accepted Solution

Greg Burns earned 2000 total points
ID: 37846509
Sorry it took me so long to respond, I got busy with with work.

1. To find out when stats were last updated, run this against each database table you're interested in.  

For example, here is a query against the AdventureWorks database, checking stats for the "HumanResources.Department" table:

USE [AdventureWorks]
SELECT name AS index_name,
 STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
 FROM sys.indexes
 WHERE OBJECT_ID = OBJECT_ID('HumanResources.Department')

Reference: http://blog.sqlauthority.com/2010/01/25/sql-server-find-statistics-update-date-update-statistics/

2. Yes, Update Stats is designed to improve DB performance.  It's not a magical cure, it just makes things more efficient.  If you have badly designed indexes, it won't help much.  I'm not really an expert on indexing, though.  

Here is something to look at, though: right click on your database, select Reports, and run the Index Physical Statistics report.  This will show you lots of good info about every table and every index, showing how fragmented tehy are and a recommendation of whether you should Reorg or Rebuild.

If you are really interested in digging in to the guts of your SQL Server and how things are performing, I recommend you check out Glenn Barry's website.  Here he posts a bunch of scripts to help you analyze your instance and databases for performance: http://sqlserverperformance.wordpress.com/2012/03/28/sql-server-2008-diagnostic-information-queries-april-2012/

Keep in mind that Microsoft really, really wants to keep people out of the SharePoint databases, so direct modifications, even something as simple as creating a new index, might have unexpected results.   Make sure you have a good backup in any case.
LVL 11

Expert Comment

by:Greg Burns
ID: 37846533
Here is a more general query to show all tables in a database and each index, with their StatsUpdated date.  It is ordered to show the oldest statistics first:

Use [AdventureWorks]
SELECT sys.tables.name as Table_Name, sys.indexes.Name as Index_Name,
 STATS_DATE(sys.indexes.OBJECT_ID, sys.indexes.index_id) AS StatsUpdated
 FROM sys.indexes inner join sys.tables on sys.indexes.object_id = sys.tables.object_id
ORDER BY statsupdated

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
When using a search centre, I'm going to show you how to configure Sharepoint's search to only return results from the current site collection. Very useful when using Office 365 with multiple site collections.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question