Best practices for SQL Server in a SharePoint Server farm

SharePoint 2013

Applies to: SharePoint Server 2013

Topic Last Modified: 2014-06-25

Summary:Learn how to implement best practices for SQL Server in a SharePoint Server 2013 farm.

When you configure and maintain SharePoint Server 2013 relational databases on SQL Server 2008 R2 with Service Pack 1 (SP1), SQL Server 2012, and SQL Server 2014, you have to choose options that promote performance and security. The best practices in this article are ordered based on the sequence in which they would apply, from installing and configuring SQL Server 2008 R2 with SP1, SQL Server 2012, or SQL Server 2014, to deploying SharePoint Server, and then maintaining the farm. Most of the practices apply to both SQL Server 2008 R2 with SP1 and SQL Server 2012. Practices that are unique to either SQL Server version are shown in separate sections.

To ensure optimal performance for farm operations, we recommend that you install SQL Server 2008 R2 with SP1 or SQL Server 2012 on a dedicated server that does not run other farm roles and does not host databases for other applications. The only exception is deployment of SharePoint Server 2013 on a stand-alone server, which we do not recommend for large-scale production environments.

Note:

The recommendation to use a dedicated server for relational databases also applies to virtualized SQL Server environments.

To ensure consistent behavior and performance, configure the following options and settings before you deploy SharePoint Server 2013.

Do not enable auto-create statistics on a server that hosts SQL Server and SharePoint Server. Enabling auto-create statistics is not supported for SharePoint Server. SharePoint Server configures the required settings during provisioning and upgrade. Manually enabling auto-create statistics on a SharePoint database can significantly change the execution plan of a query. The SharePoint databases either use a stored procedure that maintains the statistics (proc_UpdateStatistics) or rely on SQL Server to do this.

Set max degree of parallelism (MAXDOP) to 1 for instances of SQL Server that host SharePoint databases to make sure that a single SQL Server process serves each request.

Important:

Setting the max degree of parallelism to any other number can cause a less optimal query plan to be used that will decrease SharePoint Server 2013 performance.

To help simplify maintenance, such as to make it easier to move databases to another server, create DNS aliases that point to the IP address for all instances of SQL Server. For more information about DNS or Hostname aliases, see How to Add a Hostname Alias for a SQL Server Instance.

As is the case with web servers and application servers, the configuration for database servers affects how well SharePoint Server 2013 performs. Some databases have to be on the same server as other databases. Conversely, some databases cannot be on the same server as other databases. For more information, see Capacity management and sizing overview for SharePoint Server 2013.

SQL Server 2012 AlwaysOn Availability Groups are a new high availability and disaster recovery solution that are an alternative to database mirroring and log shipping solutions. AlwaysOn Availability Groups support a set of primary read-write databases and up to four sets of secondary databases that can be set as read-only.

AlwaysOn Availability Groups require a Windows Server Failover Clustering (WSFC) cluster. A WSFC resource group is created for every availability group that is created. For more information, see the following resources:

We recommend that you separate, and prioritize your data among the drives on the database server. Ideally, you should place the tempdb database, content databases, usage database, search databases, and transaction logs on separate physical hard disks. The following list provides some guidance. For more information, see Configure databases.

For collaboration or update-intensive sites, use the following ranking for storage distribution.

The highest ranked item should be in the fastest drives.

tempdb data files and transaction logs

Content database transaction log files

Search databases, except for the Search administration database

Content database data files

In a heavily read-oriented portal site, prioritize data and search over transaction logs as follows.

For best performance, use a RAID 10 array for the drive that stores tempdb data files. The number of tempdb data files should equal the number of CPU cores, and each tempdb data file should be set to the same size.

Separate database data and transaction log files across different disks. If data and log files must share disks due to space limitations, put files that have different usage patterns on the same disk to minimize concurrent access requests.

Use multiple data files for heavy-use content databases, and put each on its own disk

To improve manageability, monitor and make adjustments as needed to keep content databases below 200 GB, rather than restrict the database size.

Note:

If you manually restrict database size in SQL Server, you can cause unexpected system downtime when the capacity is exceeded.

Proper configuration of I/O subsystems is very important to the optimal performance and operation of SQL Server systems. For more information, see SQL Server: Minimize Disk I/O

Tip:

Consider that how you measure disk speed varies between data files and log files. The fastest drives for database data may not be the fastest for log files. Consider usage patterns, I/O, and file size.

Following are recommendations to proactively manage the growth of data and log files:

When possible, increase all data files and log files to their expected final size, or periodically increase these at set periods, for example, every month or every six months, or before rollout of a new storage-intensive site such as during file migrations.

Enable database autogrowth as a protective measure to make sure that you do not run out of space in data and log files. Consider the following:

The default settings for a new database are to grow by 1 MB increments. Because this default setting for autogrowth results in increases in the size of the database, do not rely on the default setting. Instead, use the guidance provided in Set SQL Server options.

Set autogrowth values to a fixed number of megabytes instead of to a percentage. The bigger the database, the bigger the growth increment should be.

Note:

Use care when you set the autogrowth feature for SharePoint databases. If you set a database to autogrow as a percentage, for example at a 10-percent (%) growth rate, a database that is 5 GB grows by 500MB every time that a data file has to be expanded. In this scenario, you could run out of disk space.

Consider for example, a scenario where content is gradually increased, say at 100MB increments, and autogrowth is set at 10MB. Then suddenly a new document management site requires a very large amount of data storage, perhaps with initial size of 50 GB. For this large addition, growth at 500 MB increments is more appropriate than 10MB increments.

For a managed production system, consider autogrowth to be merely a contingency for unexpected growth. Do not use the autogrow option to manage your data and log growth on a day-to-day basis. Instead, set the autogrowth to allow for an approximate size in one year and then add a 20 percent margin for error. Also set an alert to notify you when the database runs low on space or approaches a maximum size.

Maintain a level of at least 25 percent available space across drives to accommodate growth and peak usage patterns. If you add drives to a RAID array or allocate more storage to manage, monitor capacity closely to avoid running out of space.

We recommend that you continuously monitor SQL Server storage and performance to make sure that each production database server is adequately handling the load put on it. Additionally, continuous monitoring enables you to establish benchmarks that you can use for resource planning.

Take a comprehensive view of resource monitoring. Do not limit monitoring to resources that are specific to SQL Server. It is equally important to track the following resources on computers that are running SQL Server: CPU, memory, cache/hit ratio, and the I/O subsystem.

When one or more of the server resources seems slow or overburdened, consider the following performance guidelines based on the current and projected workload.

Backup compression can speed up SharePoint backup operations. It is available in SQL Server Standard and Enterprise Edition. If you set the compression option in your backup script or configure SQL Server to compress by default, you can significantly reduce the size of your database backups and shipped logs. For more information, see the following resources: