SharePoint Performance Issue- Set Memory Limits on SQL Server

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.

SQL Server 2016 - Properties

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.

SQL Server 2016 - Memory

Adnan Amin

Adnan is two time SharePoint MVP (Most Valuable Professional) with over 12 years of extensive experience with major expertise on SharePoint Server, Office 365, .Net Platform and Microsoft BI. He is currently working SharePoint Architect at FMT Consultants. He is MCT Regional Lead for Pakistan Chapter since 2012. He is working on SharePoint for past nine years and worked on different intranet/intranet solutions for private & govt. sector majorly in Gulf region, which include OOB and customized solutions. He is a trainer, technology evangelist and also speaks in community forums.

Leave a Reply