Home > Workload Solutions > SQL Server > Best Practices > AMD-Based SQL Server Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Database Best Practices > Performance Best Practices > SQL Server 2019: Minimum and Maximum Server Memory
In this best practice we increased the minimum and maximum server memory values for SQL Server. The goal was to validate if allocating additional memory to SQL Server will increase database performance.
Category | SQL Server 2019 Enterprise |
Product | SQL Server |
Type of best practice | Performance Optimization |
Day and value | Day 3, Fine Tuning |
Overview
Database administrators can modify memory allocation for SQL Server using the minimum server memory and maximum server memory parameters.
Minimum Server Memory
Minimum server memory is the amount of allocated memory that the SQL Server will reserve for the database. This setting is a memory allocation guarantee that the database administrator can use to optimize performance. Analysis of memory use from the operating system and other applications is required to accurately define the minimum server memory setting. Setting the minimum server memory to a high value can impact the operating system and lower overall performance.
In this best practice the minimal server memory was changed from the default value to 122880 MB (120 GB).
Maximum Server Memory
Maximum server memory is the maximum amount of memory that the SQL Server can use. Setting the maximum server value sets a limit to the amount of memory the database can use. The difference between minimum and maximum server values defines the amount of memory SQL Server can dynamically use. For example, if the minimum is set to 64 GB and the maximum is set to 128 GB, then the SQL Server can dynamically use 64 GB (128 – 64 = 64). Analysis of the operating system and other application memory use is highly recommended to ensure that the database does not impact the overall system.
Prior to modifying the minimum server memory parameter, we used the default setting of 2,147,483,647 MB.
We changed the maximum server memory to a value of 122880 MB (120 GB). By setting the maximum and minimum server memory values to 120 GB the database will allocate all the memory and not exceed the 120 GB memory configuration.
Recommendation
Setting the minimal and maximum server memory values to 120 GB showed minor performance improvements for these metrics:
The minor improvements in NOPM and the other metrics were not significant enough to categorize the performance gains as more than Day 3, Fine Tuning activity. SQL Server with the default memory settings does very well at optimizing memory allocation for the best possible performance. With detailed analysis there is the opportunity to improve database performance by setting minimum and maximum server memory parameters. The database administrator should evaluate and test the settings of these parameters based on the database workload.
There was a significant CPU utilization savings, of approximately 12%, in setting the minimum and maximum server memory settings. We did not investigate the cause in the drop of CPU utilization however, the savings might be attributed to the database engine not having to dynamically manage memory allocation.
PowerMax average read and write times did show minor increases. The minor increase in read and write times can be attributed to the slight increases in NOPM, TPM, BRPS, and storage IOPS.
Implementation Steps
In SQL the Server Management Studio we clicked on the Properties tab and selected the Memory page shown in Figure 1. The Minimum server memory field enables the administrator to define the minimal amount of allocated memory to SQL Server. As demonstrated in Figure 1, the minimum and maximum server memory values were both set to 122880 MB (120 GB).
Figure 1: Memory page from SQL Server Management Studio
We also recommend applying this setting through the mssql config file. Run the following command to update the memory reserved for the SQL Server process to 120GB:
/opt/mssql/bin/mssql-conf set memory.memorylimitmb 122880
Additional Resources
Server memory configuration options