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, a distinguished professional, boasts an impressive track record as a Microsoft MVP, having achieved this prestigious recognition for the eighth consecutive year since 2015. With an extensive career spanning over 18 years, Adnan has honed his expertise in various domains, notably excelling in SharePoint, Microsoft 365, Microsoft Teams, the .Net Platform, and Microsoft BI. Presently, he holds the esteemed position of Senior Microsoft Consultant at Olive + Goose. Notably, Adnan served as the MCT Regional Lead for the Pakistan Chapter from 2012 to 2017, showcasing his leadership and commitment to fostering growth within the tech community. His journey in the realm of SharePoint spans 14 years, during which he has undertaken diverse projects involving both intranet and internet solutions for both private and government sectors. His impact has transcended geographical boundaries, leaving a mark on projects in the United States and the Gulf region, often collaborating with Fortune 500 companies. Beyond his roles, Adnan is a dedicated educator, sharing his insights and knowledge as a trainer. He also passionately advocates for technology, frequently engaging with the community through speaking engagements in various forums. His multifaceted contributions exemplify his dedication to the tech field and his role in driving its evolution.
No Comments