By default, SQL Server is set to use max 2TB of RAM. You might not be having 2TB RAM in your environment, you might have 8GB, 16GB or more depending on your farm and SharePoint installation. Here is a formula to determine memory size for all instances of SQL Server.
SQL Max Memory= TotalPhysicalMemory – (NumberOfSQLThreads * ThreadStackSize) – (1GB * CEILING(NumberOfCores/4))
NumberOfSQLThreads = 256 + (NumberOfProcessors*- 4) * 8 (* If NumberOfProcessors > 4, else 0)
ThreadStackSize = 2MB on x64 or 4 MB on 64-bit (IA64)
We recommend to set Maximum Server Memory calculated as Available RAM – 2GB (for OS). This may be increased as needed up to 2TB.
This is one of the know issue that SharePoint server getting slow with time due to SQL Server which is taking most of the RAM on the system, so it’s important to define the memory limit to SQL Server instead of using the default limit.
To set memory limits in SQL Server
1 – Open SQL Server management studio.
2 – Right click on server and select properties from context menu.
3- Select Memory in left navigation of Server Properties to bring up Server memory options. Maximum memory show in diagram below is just for illustration purpose.
Latest posts by Adnan Amin (see all)
- SharePoint 2016 Multi-Server Farm Installation Guide - November 20, 2017
- SharePoint Server 2016: Provision Service Applications - November 20, 2017
- SharePoint Server 2016: Provision User Profile Services - November 17, 2017