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.
Memory Limits on SQL Server
Performance Issue
SharePoint
SharePoint 2013
SharePoint 2016
SharePoint performance issue
SQL Server
Adnan is six time Microsoft MVP (Since 2015) with over 16 years of extensive experience with major expertise on SharePoint, SharePoint based development, Microsoft 365, Microsoft Teams, .Net Platform and Microsoft BI. He is currently working Sr Microsoft Consultant at Olive + Goose. He is MCT Regional Lead for Pakistan Chapter since 2012. He is working on SharePoint for past 12 years and worked on different intranet/intranet solutions for private & govt. sector majorly in United states and Gulf region and have experience of working with multiple Fortune 500 companies. He is a trainer, technology evangelist and also speaks in community forums.
No Comments