Sharepoint 2010 DataBases and SQL Update Stats

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?
LVL 21
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Greg BurnsSQL / SharePoint EngineerCommented:
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:
Greg BurnsSQL / SharePoint EngineerCommented:
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.
compdigit44Author Commented:
How can I tell if and when the last time the sharepoint db stats were updated.
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

compdigit44Author Commented:
Can run a DB stats update help improve the performance of a DB?
compdigit44Author Commented:
Any thoughts on this? Thanks Again
Greg BurnsSQL / SharePoint EngineerCommented:
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')


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:

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Greg BurnsSQL / SharePoint EngineerCommented:
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 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.