Solved

Sharepoint 2010 DataBases and SQL Update Stats

Posted on 2012-04-11
7
818 Views
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?
0
Comment
Question by:compdigit44
  • 4
  • 3
7 Comments
 
LVL 8

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):

http://blogs.technet.com/b/patrick_heyde/archive/2010/05/27/advanced-maintenance-for-sharepoint-databases-defrag-update-index.aspx

Here is some more relevant information for SP 2010.

Timer Job reference:
http://technet.microsoft.com/en-us/library/cc678870.aspx

Recommended Database Maintenance for SharePoint:
http://technet.microsoft.com/en-us/library/cc262731.aspx
0
 
LVL 8

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.  

Reference:
Reorganize and Rebuild Indexes
http://technet.microsoft.com/en-us/library/ms189858.aspx

UPDATE STATISTICS
http://msdn.microsoft.com/en-us/library/ms187348.aspx

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.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 37834250
How can I tell if and when the last time the sharepoint db stats were updated.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Author Comment

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

Author Comment

by:compdigit44
ID: 37840492
Any thoughts on this? Thanks Again
0
 
LVL 8

Accepted Solution

by:
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')
 GO

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.
0
 
LVL 8

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
 GO
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

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…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now