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
I am Krishna.Vandanapu a SharePoint architect working in IT from last 13+ 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
Download SharePoint Online Tutorial PDF FREE!
Get update on Webinars, video tutorials, training courses etc.