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.
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
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
EXEC sp_configure ‘max server memory’, @maxMem
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.
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.
Microsoft suggest clearly that don’t install any applications on the server where SQL is running.
Hope this helps while setting up the servers.