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
  • 4
  • 3
LVL 10

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 10

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 19

Author Comment

ID: 37834250
How can I tell if and when the last time the sharepoint db stats were updated.
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

LVL 19

Author Comment

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

Author Comment

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

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 10

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

839 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