SQL Best Practice for SharePoint 2010 and 2013
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.
- Storage and SQL Server capacity planning and configuration https://technet.microsoft.com/en-us/library/cc298801.aspx
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.
- Database maintenance for SharePoint 2010 Products https://technet.microsoft.com/en-us/library/cc262731
- Reorganize and Rebuild Indexes https://msdn.microsoft.com/en-us/library/ms189858.aspx
- How to defragment SharePoint https://blogs.technet.com/b/patrick_heyde/archive/2009/03/31/how-to-defragment-sharepoint-databases.aspx
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