How to Setting maximum memory in SQL server?

increase memory in sql server 2014

Issue:
I have a server where SQL server and some web applications deployed. These days I was keep getting escalations for slow performance of applications. When I start doing root cause analysis I realized that SQL server is taking all the memory on that server.
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.

Solution:
When a SQL server running on a machine the unknown hurdle is that 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 SQL server. This will helps to improve the performance of application eventually.

This can be achieved in 2 different ways:
1. Setting max memory utilization from SQL Server Management Studio (SSMS)
2. 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 server with 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
Witt 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:
1. This will help the server to render all the applications running on the server
2. We can enhance the memory at any point of time if we see any requirement for SQL server in future.

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


You May Also like the Following SharePoint Online Tutorials:

About Krishna Vandanapu

I am Krishna.Vandanapu a SharePoint architect working in IT from last 12 years, I worked in SharePoint 2007, 2010, 2013, 2016 and Office 365. I have extensive hands on experience in customizing SharePoint sites from end to end. Expertise in SharePoint migration tools like Sharegate, Doc Ave and Metalogix. Migrated SharePoint sites from SharePoint 2007 to 2010 and 2010 to 2013 several times seamlessly. Implementing CSOM with Microsoft best practices. Spent quality time in configuring SharePoint application services like User Profile, Search, Managed Meta data services etc. Now exploring SharePoint Framework and SharePoint 2019

View all posts by Krishna Vandanapu →