SharePoint 2013: Limit SQL Server Memory Allocation
Many times we have seen developers complaining about their SharePoint development environment performance. Since they have SharePoint standalone installation on their development Virtual Machine they always complain their machine is too slow.
This is not only the case with development environment, even for SharePoint Test, Staging and Production environment, we always blame SharePoint the cause of performance hit. But wait a min the problem is not with SharePoint there could be other factors which might be causing performance issue. And one of the factor is default settings of SQL Server database. In this article I am not going to touch all other factors but yes I am going to touch base very important and quick setting which you can do to boost your SharePoint environment performance.
SQL Server is the one who eats all your memory every time, because by default it is given freedom to SQL Server to consume all memory on the server. By Default, SQL Server is set to use max 2TB of RAM. You might not be having 2 TB RAM in your environment, you might have 8 GB, 16 GB, 32 GB or more depending upon your farm and size of SharePoint installation. So what will happen if SQL Server itself is free to take up to 2 TB RAM, then whenever any operation comes to SQL Server it will try to consume as much as it can consume. What will happen to the OS and other processes you have running on the server, they also need some memory, they are also responsible for doing their task. So here is what you can do to limit the memory size of SQL Server and tell him that you are not the one there are others who also needs some memory. At the end you need to make sure your SQL Server and your Server they are not competing themselves to the same memory resource this causes bad performance on the server.
Let me first show you the setting in SQL Server where it is configured to consume max 2 TB of RAM.
1) Login to your SQL Server management Studio and right click on your database server and click on properties as shown in below screenshot
2) Click on Memory tab and see the default memory size as highlighted in below screenshot
So you can restrict SQL Server memory by doing following calculation 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)
I understand it is not so easy to put all values in above formula and get your SQL Server memory size, however if you do this exercise I am sure you will see performance boost in your SharePoint environment.
Note: The above formula will give you the memory size for all SQL Server instance, so in case you have multiple instances of SQL Server then again you have to further divide the memory to distribute to each of the instances.
Since SQL Server 2008 R2, if you have only one instance of SQL Server then you don’t have to do all this calculation, it is inbuilt in SQL Server to do dynamic calculation to determine how much memory to allocate based on the how much other process and OS needs the memory.
Other languages
This article is also available in the following languages :