The performance of SharePoint Server 2016 is heavily dependent on SQL Server. In this article I’m going to show you how to configure SQL Server optimally and how to improve the performance of a SharePoint platform overall.

Almost all SharePoint data is stored in SQL Server. Because of this fact it’s not surprising that the SQL Server performance has a direct impact on the speed of SharePoint. Below are a few tips to improve performance at no extra cost.

Configuration at Windows Server level

Windows Server Performance Options
By default Let Windows choose what’s best for my computer is set. In most cases, the server is optimized for a nice display, but this is not very important for a server. The value can be set to Adjust for best performance:
Right-click on This PC> Properties > Advanced System Settings > Advanced > Performance > Settings… > Visual Effects
2013-04-02_125748

Set Page File Size
By default, the page file is managed by Windows. In most cases, it makes sense to determine the size and storage location yourself.
This PC > Properties > Advanced system settings > Advanced > Performance > Settings… > Advanced > Virtual Memory > Change…
The size of the page file should correspond to the factor 1.5 of the allocated RAM of the server. Example: 8192 MB RAM x 1.5 = 12288 MB Pagefile
2013-04-02_125750

Customize Power Plan
By default, balanced mode is activated. However, high performance should be used for server operation, as this allows up to 15% higher throughput.
Navigate to Start > Control Panel > Hardware > Power Options and switch to High Performance.
2013-04-02_125751
If the High Performance option is disabled, click Change settings that are currently unavailable. The change can then be made.
2013-04-02_125752

Optimize Allocation Unit Size
The NTFS allocation unit size should be adjusted to 64K. You can display the current settings by opening the Command Line Tool (cmd) with administrative rights and entering the following command:
chkdsk D: (D: corresponds to the desired partition)
In the bottom section of the output you will find the information you require.

To change the NTFS allocation unit size for partition D: to 64K, use the following command in the Command Line Tool (cmd):
D: /Q /FS: NTFS /A: 64K /V: Data /Y
Caution: This command formats the drive!

Explanation
D: = drive letter of the desired partition (in this example D:)
/Q = Quick Format
/FS = File System (in this example NTFS)
A = Allocation Unit Size (in this example 64K)
/V = Volume Label (in this example the volume is called «Data»)
/Y = Confirms the query to format the disc.

Database Volumes
The database volumes should be divided into separate LUNs (Logical Unit Numbers), ideally consisting of separate physical disks.

Prioritization of data for disks with different speeds (in descending order)

  • 1. TempDB Data Files and Transaction Logs
  • 2. Content Database Transaction Logs
  • 3. search databases
  • 4. Content Database Data Files

If data is read and less written in the farm, the positions 2 and 4 must be exchanged for each other.
Furthermore, the transaction logs of the search database should be separated from the transaction logs of the content databases.

Configuration at SQL Server level

  • SharePoint should always be run on a dedicated SQL server or at least one dedicated SQL instance.
  • Using a Named SQL Instance.
  • The authentication method for SQL Istanz should be Windows Authentication.
  • For larger search applications, a separate SQL instance must be used for the search.
  • Use the latest service pack and patches for SQL Server installation.
  • The SQL Collation for SharePoint is Latin1_General_CI_AS_KS_WS.
  • Creation of SQL databases with PowerShell to avoid GUIDs in the database names.
  • The SQL Service Account requires the rights Lock Pages in Memory (see http://support.microsoft.com/kb/918483/en-us) and Performance Volume Maintenance Tasks (gpedit.msc > Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignments > Perform volume maintenance tasks)
  • Leave Auto Create Statistics and Auto Update Statistics on False

Setting the minimum and maximum server memory
Microsoft SQL Server Management Studio > Object Explorer > Right click on servername\ instance > Properties > Memory
Set the minimum server memory and the maximum server memory value in MB. Attention: When setting the miximalen server memory, about 3 GB should be deducted for running the operating system.

Fill Factor and Backup Compression
Microsoft SQL Server Management Studio > Object Explorer > Right click on servername\instace > Properties > Database Settings
Change the value of the default index fill factor to 80 and check Compress Backup to enable backup compression.

Max Degree of Parallelism
This setting determines the number of processors that are used for processing a query.
A high MAXDOP value is usually used if the number of queries to be processed at the same time is rather low. If the number of queries to be processed at the same time is rather high, a low MAXDOP value is usually set.
Since SharePoint has a very high number of queries to be processed at the same time, the value should be set as low as possible, so the optimal value for a SharePoint environment is 1.
If the value is set to 0, SQL Server can use all available processors to process a single query.
Microsoft SQL Server Management Studio > Object Explorer > Right click on servername\ instance > Properties > Advanced
Set the Max Degree of Parallelism value to 1.

Trace Flag 1117
Setting trace flag 1117 ensures that all data files grow evenly.
Navigate to Start > Microsoft SQL Server 2017 > SQL Server Configuration Manager > SQL Server Configuration Manager (local) > SQL Server Services > SQL Server (Instance name) > Properties (right-click) > Advanced > Startup Parameters > and add ;T1117 at the end.

Configuring the databases

Configuring the databases: General database adjustments
Pregrow
Enlarge the content database to the expected size to minimize fragmentation.

Autogrowth
Should have remained activated. For smaller database sizes, the Autogrowth value should be set to 256 MB or 512 MB. For larger databases, starting from about 5 GB, 10% is an appropriate value.

Number of data files
The number of data files should correspond to the number of processor cores (minimum 4/maximum 8)

Recovery model
In most cases, the Recovery Model Simple can be used. Full should be used if a backup strategy with regular backups of the transaction logs is planned or a high availability configuration with log shipping or database mirroring is planned.

ModelDB settings

  • The initial size of the MDF file should be adjusted to a reasonable estimate.
  • The initial size of the LDF file should be set to 25% of the initial MDF initial size. Make sure that the LDF value can be divided by eight out of the MDF value.
  • In order to achieve optimal growth rates, the autogrowth setting should be set sensibly. This value is usually 25-50% of the initial file size.

TempDB settings

  • The initial size should be set to 25% of the size of the largest content database.
  • Split into several data files. The number of data files should correspond to the number of processor cores (minimum 4/maximum 8)
  • The data file size of all data files must be the same size.
  • Set the Autogrowth value to 10% of the assigned TempDB size, but as a fixed value in MB.

Maintenance plan
Daily Full Backup of the content databases incl. Consistency Check
Transaction Log Backups every 30 minutes
Weekly backup of system databases
For more information please refer to SQL database maintenance plan for SharePoint

Antivirus

The SQL Server data, transaction log and backup directories must be excluded from the virus scanner.

SharePoint configuration

  • Content databases should not be larger than 200 GB, otherwise backup/restores can take a long time.
  • No more than 5,000 site collections per content database.
  • Set quotas at SharePoint Web Application Level.

SQL Alias
Instead of the actual SQL server and instance name, you should store an SQL alias on all SharePoint servers. This makes it easy to migrate the SQL Server at a later date.
Creating an SQL alias is described here: https://www.techtask.com/sharepoint2010/sql-alias-fur-sharepoint-web-applikationen-erstellen/