Technology Blog

SQL Best Practice for SharePoint 2010 and 2013

1

Over the past few years I have delivered a number of SharePoint health checks, and one of the most common areas where I have seen misconceptions is with regards to SQL Server Management for SharePoint databases. SharePoint stores a majority of its content in SQL Server and this is where a lot of processing takes place.Whilst I understand customers mostly use dynamic storage which adds its own layer of complication to the mix, database maintenance is still key to ensure we get the best performance out of our SharePoint application. The following are excellent resources to ensure your databases are in top shape.

1. Planning Storage: We do not have a capacity planning automated tool, rather is encouraged to use data modelling, piloting and tests to get actual data as to how your organizations content will scale and grow in SharePoint.

2. Maintenance of Databases: Most organizations focus on project milestones with maintenance left on the back burner, the following articles are excellent in getting your SQL Databases back on track.

Finally SharePoint has a complicated manner in terms of dealing with Database Statistics, by default the databases are provisioned by SharePoint with the correct configuration. But there is a lot of guidance floating around that says all database statistics should be off and this is not accurate. The following is the criteria for databases settings when it comes to database statistics.

Also check the following databases and ensure that Auto Create / Update Statistics Enabled

  • StateService
  • Secure_Store_Service_DB
  • Bdc_Service_DB
  • PerformancePoint Service Application
  • Search_Service_Application_CrawlStoreDB
  • Search_Service_Application_PropertyStoreDB
  • Search_Service_Application_DB
  • Managed Metadata Service
  • User Profile Service Application

And the following databases for Auto Create / Update Statistics Disabled

  • WordAutomationServices
  • SharePoint_Config
  • SharePoint_AdminContent
  • WSS_Content
  • User Profile Service Application_ProfileDB
  • User Profile Service Application_SocialDB
  • WSS_Logging
  • WebAnalyticsServiceApplication_StagingDB
  • WebAnalyticsServiceApplication_ReportingDB

I am attaching a database cheat sheet that can be used as a quick reference for database type and role maintenance task required. This data was extracted from the following TechNet article:

https://technet.microsoft.com/en-us/library/cc678868.aspx

  • Brandon Katz

    Hi there,

    Have you considered a third party analytics solution for your SharePoint portal? A solution like CardioLog Analytics drills down deep to provide much deeper insights than those available in SharePoint or Google analytics.It’s available as an On-Prem or SaaS deployment. The solution is specifically built for SharePoint, so it’s tailored to provide analytics specifically on SharePoint’s architecture. If you’re looking for more information regarding your portal visitors, navigation paths, search behaviors, and content performance check out http://www.intlock.com

    Hope this helps!