The purpose of this blog is to outline five SQL Server configuration changes that every SharePoint administrator must know about. These changes optimize Microsoft SQL Server (2008 and higher) for SharePoint workloads. These changes are well document and have been proven to have positive impact on SQL server performance. These changes/tweaks have to be enabled after the SQL server binaries are installed. The changes are listed below:
- Enable Instant File Initialization: SQL Server data files can be initialized instantaneously thus allowing for fast execution of file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously. SharePoint databases storing SQLBLOBS are constantly expanding and this change deters the Windows OS from dedicating CPU cycles to managing this growth.
- Lock Pages in Memory: Lock Pages in Memory allows SQL Server to reserve and retain cache memory pages that it builds based on activity from being paged out to disk. The setting must be enabled in conjunction with VM memory reservations.
- Enable SQL Engine for Ad-hoc Workloads: The “optimize for ad-hoc workloads” option is used to improve the efficiency of the plan cache for workloads that contain many single-use ad-hoc batches. When it is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This relieves memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused. For details, see http://msdn.microsoft.com/en-us/library/cc645587.aspx.
- MAXDOP Setting: This setting should be set to 1 for SQL server hosting SharePoint databases. Oren Bouni does a fantastic job of explaining this. His blog can be found here: http://blogs.technet.com/b/sqlpfeil/archive/2012/02/01/four-tips-for-sql-tuning-for-sharepoint-part-1.aspx
- Set SQL Server maximum memory to lower value (large servers): This setting will allow SQL Server to give back some memory to the operating system. The rule I’ve been sticking to is allocating between 10-20% of RAM available to the host operating system e.g. on 64GB server I’ve set the MAX memory usage to 57GB and I’ll monitor performance counters to make sure that the server remains healthy
In summary, the changes outlined about have a significant (positive) impact on SharePoint database performance if the SQL server hosting SharePoint databases was installed with default settings.