https://www.sqldbachamps.

com
Praveen Madupu - +91 98661 30093
Sr SQL Server DBA, Dubai
[email protected]

In SQL Server, configuring minimum and maximum server memory settings is crucial for optimizing performance
and ensuring that the SQL Server instance doesn’t consume too much or too little memory. These settings allow
you to control how much memory SQL Server can allocate, balancing memory usage with the needs of the
operating system and other applications on the server.

1. Understanding SQL Server Memory Usage

SQL Server uses memory for various tasks, such as caching data (buffer pool), execution plans (procedure
cache), and managing internal data structures. By default, SQL Server dynamically allocates memory based on
workload and system availability, but this can lead to potential issues:

- Too much memory usage: If SQL Server is the only application running on the server, it may take as much
memory as needed, leaving little for the operating system or other services.

- Too little memory usage: If SQL Server doesn't have enough memory allocated, it may perform poorly due to
frequent disk I/O (paging), resulting in slow query execution.

Configuring the minimum and maximum server memory ensures that SQL Server operates within set boundaries
and doesn't starve the operating system or other applications of memory.

https://www.sqldbachamps.com
2. Minimum and Maximum Server Memory Settings

- Minimum Server Memory (min server memory): This setting specifies the minimum amount of memory that SQL
Server will use once it has ramped up to that level. It won't release memory below this limit.

- Maximum Server Memory (max server memory): This setting limits the maximum amount of memory SQL
Server can allocate. SQL Server won’t use more memory than this limit, ensuring that other processes on the
server have enough resources.

3. Best Practices for Configuring SQL Server Memory

3.1. Leave Enough Memory for the Operating System

- SQL Server should not consume all available memory, as the operating system and other applications also
need memory to function properly.

- General Recommendation:

- On a dedicated SQL Server instance, leave at least 4-6 GB for the operating system.

- For larger systems (e.g., >64 GB of RAM), leave 10% of the total RAM for the OS but not less than 4 GB.
https://www.sqldbachamps.com
Praveen Madupu - +91 98661 30093
Sr SQL Server DBA, Dubai
[email protected]
3.2. Minimum Server Memory Best Practices

- Default Setting: The default value for min server memory is 0, meaning that SQL Server will start with as little
memory as possible and grow as needed.

- When to Set Minimum Memory: You may set a minimum memory value if you want to ensure that SQL Server
always has a certain amount of memory available for its workload, preventing it from competing for resources with
other applications.

Example:

EXEC sp_configure 'min server memory', 8192; -- 8 GB

RECONFIGURE;

In this case, SQL Server will always hold at least 8 GB of memory, ensuring that it doesn't drop below this level
even if other processes require memory.

3.3. Maximum Server Memory Best Practices

- Default Setting: The default value for max server memory is 2,147,483,647 MB (effectively unlimited), which
means SQL Server can consume as much memory as the system provides, potentially starving the OS.

https://www.sqldbachamps.com
- Recommended Setting: Adjust max server memory to ensure that the OS and other critical services have
enough memory. Here's a simple formula to estimate memory allocation:

- For servers with ≤ 64 GB RAM: Allocate around 80-90% of the total memory to SQL Server.

- For servers with > 64 GB RAM: Reserve 4-6 GB or 10% of the total RAM for the operating system.

Example for a server with 64 GB RAM:

EXEC sp_configure 'max server memory', 56320; -- 55 GB for SQL Server

RECONFIGURE;

Here, SQL Server is allowed to use up to 55 GB of memory, leaving the rest (approximately 9 GB) for the
operating system.

Example for a server with 128 GB RAM:

EXEC sp_configure 'max server memory', 117760; -- 115 GB for SQL Server

RECONFIGURE;
https://www.sqldbachamps.com
Praveen Madupu - +91 98661 30093
Sr SQL Server DBA, Dubai
[email protected]
3.4. Monitoring Memory Usage

You should regularly monitor SQL Server memory usage to ensure the configured settings are optimal:

- Use sys.dm_os_sys_memory to check the total and available memory for the server.

- Use sys.dm_os_memory_clerks to monitor how SQL Server is allocating memory internally.

- Use Performance Monitor (PerfMon) counters such as SQLServer:Memory Manager – Target Server Memory
and SQLServer:Memory Manager – Total Server Memory to see how much memory SQL Server is using
compared to its configured limits.

Example:

SELECT

(total_physical_memory_kb / 1024) AS Total_Memory_MB,

(available_physical_memory_kb / 1024) AS Available_Memory_MB

FROM sys.dm_os_sys_memory;

3.5. Considerations for Virtual Machines

https://www.sqldbachamps.com
On virtualized environments, special care is needed:

- Ensure that the virtual machine has dedicated memory assigned (avoid memory overcommitment by the
hypervisor).

- Set the max server memory based on the virtual machine’s allocated memory, not the physical host’s total
memory.

4. Tuning Memory for Large Servers

For large SQL Server instances with significant RAM (e.g., 128 GB, 256 GB, or more), consider the following:

- Leave at least 10% of memory for the OS and background processes, but always ensure that at least 4-6 GB
is free for OS operations.

- For servers with multiple SQL Server instances, allocate memory to each instance carefully to avoid
contention.

- NUMA (Non-Uniform Memory Access) Optimization: In servers with NUMA, SQL Server automatically
manages memory and CPU affinity, but you can fine-tune memory and CPU usage for each NUMA node.
https://www.sqldbachamps.com
Praveen Madupu - +91 98661 30093
Sr SQL Server DBA, Dubai
[email protected]
5. Adjusting Memory Dynamically

SQL Server supports dynamic memory management, so you can adjust min server memory and max server
memory without restarting the instance. After you change these settings, SQL Server will adjust its memory usage
accordingly based on current activity and system conditions.

6. Example Memory Configuration Scenarios

Scenario 1: SQL Server on a 32 GB RAM Server

- You have a SQL Server instance running on a dedicated machine with 32 GB of RAM.

- Recommendation:

- Reserve 4 GB for the OS.

- Set max server memory to 28 GB.

EXEC sp_configure 'max server memory', 28672; -- 28 GB

RECONFIGURE;

Scenario 2: SQL Server on a 64 GB RAM Server with Multiple Applications

https://www.sqldbachamps.com
- SQL Server shares the server with other applications, and the server has 64 GB of RAM.

- Recommendation:

- Reserve 8-10 GB for the OS and other applications.

- Set max server memory to 54-56 GB.

EXEC sp_configure 'max server memory', 56320; -- 55 GB

RECONFIGURE;

Scenario 3: SQL Server on a Virtual Machine with 16 GB Allocated Memory

- SQL Server is running in a virtual machine with 16 GB of allocated memory.

- Recommendation:

- Reserve 2-3 GB for the OS.

- Set max server memory to 13-14 GB.

https://www.sqldbachamps.com
Praveen Madupu - +91 98661 30093
Sr SQL Server DBA, Dubai
[email protected]
EXEC sp_configure 'max server memory', 14336; -- 14 GB

RECONFIGURE;

7. Monitoring and Adjusting Over Time

- As workloads change (e.g., more databases, increased query load), you may need to revisit and adjust the
min server memory and max server memory settings.

- Regular monitoring using Performance Monitor, DMVs, and tools like SQL Server Profiler or Extended Events
can provide insights into how memory is being used and if adjustments are needed.

Summary:

Properly configuring the min server memory and max server memory settings in SQL Server is critical for
ensuring stable and efficient performance.

It ensures that SQL Server has enough resources to function optimally while leaving memory for the operating

https://www.sqldbachamps.com
system and other processes.

Following best practices, you can strike a balance between SQL Server's memory demands and the overall
health of your server or virtualized environment.