How to Setting maximum memory in SQL server?

I have a server where the SQL server and some web applications deployed. These days I kept getting escalations for the slow performance of applications. When I start doing root cause analysis I realized that the SQL server is taking all the memory on that server.

The best scenario to see this is, DEV and UAT SharePoint environments in many organizations. SharePoint admin / IT team installs all the software in one server.

Setting maximum memory in SQL server

When a SQL server running on a machine the unknown hurdle is that the SQL server will take the maximum RAM and all the other processes running on that server eventually get slowdown.

We have to explicitly set the maximum memory to be consumed by the SQL server. This will helps to improve the performance of the application eventually.

This can be achieved in 2 different ways:

  • Setting max memory utilization from SQL Server Management Studio (SSMS)
  • SQL script to set the maximum memory utilization

Option 1: Using SQL Server Management Studio (SSMS)
Follow the below steps to set the maximum memory utilization through SSMS.
1. Log on to the server with an admin account
2. Launch SSMS as administrator
3. Connect to SQL DB server
4. Navigate to properties of the DB server

increase memory in sql server 2014
increase memory in sql server 2014

5. Select “Memory” option and set Maximum server memory

change max memory sql server 2014
change max memory sql server 2014

6. Once you set the value click on “OK” and check the value by executing the below query.

SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MBFROM sys.dm_os_process_memory
configure memory sql server 2014
configure memory sql server 2014

Option 2: SQL script to set maximum memory:
Execute the below scripts to set the maximum memory utilization.

DECLARE @maxMem INT = 4096 --Max. memory for SQL Server instance in MB
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'max server memory', @maxMem
RECONFIGURE

With the above SQL script I am setting the max memory utilization as 4GB.

Once you execute this you can see the same has been replicated in SSMS.

how to Setting maximum memory in SQL server
how to Setting maximum memory in SQL server

Advantages:

  • This will help the server to render all the applications running on the server.
  • We can enhance the memory at any point in time if we see any requirement for SQL server in the future.

Note:
Microsoft suggests clearly that don’t install any applications on the server where SQL is running. I hope this helps while setting up the servers.

You may like following SQL Server tutorials:

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

>