How to Setting maximum memory in SQL server?
Check out Best Alternative to InfoPath -> Try Now
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
5. Select “Memory” option and set Maximum server memory
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
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.
- 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.
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:
- Difference between clustered and non clustered index SQL server
- Difference between primary key and unique key in SQL Server
- Difference between primary key and foreign key in SQL server
- Move SharePoint databases to new SQL server
- Cannot connect to the configuration database error in SharePoint
- Delete and create a new log file with minimum size in SQL Server
- Enable remote connections for SQL Server
- Sequence in SQL server
- SQL Server joins tutorial
- SQL Server Functions Tutorial
- SQL DML and DDL commands
Download SharePoint Online Tutorial PDF FREE!
Get update on Webinars, video tutorials, training courses etc.