Sharepoint 2010 DataBases and SQL Update Stats

Posted on 2012-04-11
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 500 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')


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.
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 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 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