SQL Maintenance Plan SharePoint 2010 databases
To maintain good overall databases performance of the SharePoint 2010 databases I use the following SQL maintenance plan. This plan consist of two main parts:
- The configuration of each separate databases and database properties settings
- The automated maintenance plan, to perform database maintenance task once a week.
Please note: For most of the SQL tweaks to perform on a LIVE SQL database you will need the Enterprise edition of SQL 2008 R2. Otherwise you need to put the databases offline before executing the maintenance plan as described in the article.
SharePoint 2010 has three types of databases:
- System/Configuration databases (do not include these databases in the maintenance plan)
- Service Application databases
- Content Databases
Powershell created databases
I always create SharePoint databases with a powershell script and not with the default auto-provisioned functionality. The advantages of this approach are:
- guaranteed control over database names (no GUIDs in the database names)
- guaranteed control over database sizing
- procedural separation of control over application and data environments
To prevent database corruption we need to protect the SharePoint databases. There are two kinds of database corruption:
- Physical corruption, might occur when the disk that holds a log file or data file has been altered. This type of physical corruption tends to affect sectors on a disk due to problems in the I/O subsystem, such as the physical network hardware and disk drives themselves.
- Logical corruption is caused by data being altered in some unanticipated way that severs a data relationship. This type of corruption usually is caused by an application error or human error that causes data problems but doesn’t affect the physical structure of the database.
To prevent and detect any kind of databases corruption issue I use the SQL function DBCC_CHECKDB in the maintenance plan. The only downside is that if any error occurs from this check the only solution will be to restore the database from a backup.
Managing Database files
The recommendation for the SharePoint SQL environment is to place log files and data files on different disks and to ensure that no other application uses those disks. This setup minimizes overall write access to the disks, and lessens the opportunities for file fragmentation.
Measuring and Reducing database index Fragmentation
Index fragmentation can result in performance degradation and inefficient space utilization, and indexes may become quickly fragmented on even moderately used databases. In SharePoint 2010 a table that often becomes fragmented is AllDocs, which contains document libraries, their associated documents and lists and list items, and their respective metadata.
SQL Server Fill Factor
The fill factor of each SharePoint databases needs to be set to default. Default the way the database fill factor defines how much free space is required on a database page during an index rebuild before moving to a new database page. Otherwise the database takes more database space because the database pages are larger. Instead we sill set the default fill factor for the whole SQL server, a server-wide setting of 80% is optimal to support growth and minimize fragmentation.
SQL Server 2008R2 has the ability to shrink data files, which recovers disk space by removing unused data. The best way is not to set the shrinking of the databases in SharePoint to automatically shrink the data files.
Also not to configure a maintenance plan that does it automatically on a SharePoint database. The reason is that the database auto-shrink ignores the fill factor setting and causes all the indexes to become fragmented. Then, when you run a rebuild indexes command, the database grows back to its original size. Instead of auto-shrinking databases on SQL, it’s safer to partition content databases or to remove data from existing databases on SharePoint 2010 level.
Recommended Database Maintenance plan for SharePoint 2010
All tasks to prevent the issues that can occur are being automated within a SQL 2008 R2 maintenance plan. Normally I will schedule this maintenance plan for once a week but in some environments (where there are a lot of changes in the SharePoint environment) you can choose to execute the maintenance plan more often.
1. The maintenance plan contains the following steps:
- Remove excessive transaction log file fragmentation by ensuring the appropriate recovery model and backup schedule.
- Turn off any scheduled shrink operations to reduce the risk of unnecessary index fragmentation.
- Put a regular process in place to detect and remove index fragmentation.
- A process to run DBCC CHECKDB.
- A maintenance plan should include either index reorganization or index rebuilding (never use both).
- Update Statistics
2. The configuration of each database:
- Turn on AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS, and have a regular process in place to update statistics.
- Turn on instant file initialization such that database auto-growth is an instantaneous operation rather than a slow operation that requires zero-filling new space.
- Place Database and log files on different SAN disks.
- Set auto-growth correctly by using a predetermined set file size rather than a percentage. Follow this up by periodically examining database sizes and determining whether manual database growth is necessary to ensure optimum performance.
- Turn on page checksums.