Home > Workload Solutions > SQL Server > Best Practices > Intel-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 to determine if database performance would also increase.
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 the SQL Server will reserve for the database. This setting is a memory allocation helps the database administrator optimize performance. Analysis of memory usage from the operating system and other applications is required to accurately define the minimum server memory setting. Setting the minimum server memory value to high can impact operating system performance thus, lowering overall performance.
Prior to modifying the minimum server memory parameter, we used the default value setting, which is zero. A default of zero allows the SQL Server to dynamically manage the amount of memory.
We set the minimal server memory to a value of 122880 MB (120 GB) for this best practice.
Maximum server memory
Maximum server memory is the maximum amount of memory an SQL Server can use. Setting the maximum server value limits 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 at 128 GB then the SQL Server can dynamically use 64 GB (64 – 128 = 64). Analysis of the operating system and other application memory usage 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 of 2,147,483,647 MB.
We set the maximum server memory to 122880 MB (120 GB). By setting the maximum and minimum server memory values to 120 GB the database allocates all the memory and does not exceed the 120 GB memory configuration.
Recommendation
Setting the minimal and maximum server memory values to 120 GB produced minor performance improvements for the following metrics:
As a result of modifying the minimum and maximum server memory settings, there was a significant CPU utilization savings, of approximately 12 percent. 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 also showed minor increases. The minor increase in read and write times can be attributed to the slight increases in NOPM, TPM, BRPS, and storage IOPS.
These slight improvements were not significant enough to categorize the performance gains more than Day 3, fine tuning. SQL Server with the default memory settings efficiently optimizes 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 setting these parameters based upon the database workload.
Implementation Steps
In the SQL Server Management Studio, we clicked the Properties tab and selected Memory, as shown in Figure 1. The minimum server memory field enables the administrator to define the minimal amount of allocated memory to SQL Server. We set both the minimum and maximum server memory values 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. Perform 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