Last week, I attended the SQLSaturday 2014 in Paris and participated in a session on SQL Server optimization for Sharepoint by Serge Luca. This session tried to list the best pratices and recommendations for Database Administrators in order to increase the SharePoint performance. This blog post is based on this session and is meant as a sequel to my previous post on Improving your SharePoint performance using SQL Server settings (part 1).

SQL Server instance

It is highly recommended to use a dedicated SQL Server instance for a SharePoint farm and to set LATIN1_GENERAL_CI_AS_KS_WS as the instance collation.

Setup Account permissions

You should give the Setup Account the following permissions in your SQL Server instance:

  • securityadmin server role
  • dbcreator server role
  • dbo_owner for databases used by the Setup Account

Alias DNS

It is recommended to use Alias DNS to connect to the SQL Server instance with your SharePoint server. It simplifies the maintenance and makes it easier to move SharePoint databases to another server.

Disk Priority

When you plan to allocate your SharePoint databases accross different databases, you might wonder how to maximize the performance of your system.

This is a possible disk organization (from faster to lower):

  • Tempdb data and transaction log files
  • Content database transaction log files
  • Search database data files (except Admin database)
  • Content database data files

Datafiles policy

You should use several datafiles for Content and Search databases, as follows:

  • distribute equally-sized data files accross separate disks
  • the number of data files should be lower than the number of processors

Multiple data files are not supported for other SharePoint databases.

Content databases size

You should avoid databases bigger than 200 GB. Databases bigger than 4 TB are not supported by Microsoft.

Conclusion

SharePoint is quite abstract for SQL Server DBAs because it requires specific configurations.
As a result, you cannot guess the answer: you have to learn on the subject.