Looking for SQL Server Memory Allocation Best Practices? We can help you.

An SQL Server is one of the memory-intensive database systems. And the most resource-intensive operations in SQL Server are DISK IO operations.

The SQL servers use memory to minimize disk IO operations by creating a buffer pool to hold pages read from the database.

SQL Server dynamically acquires and frees memory as required. But if we want to allocate some memory to SQL Server, we can follow the best practices given in this article.

Here at Bobcares we often handle requests from our customers using SQL server as a part of our Server Management Services.

Today, we will see some of the best practices which our Support techs follow while allocating SQL server memory.

How SQL Server allocates Memory?

We need to have enough memory to process any transaction in SQL Server.

VAS corresponds to Virtual Address Space. The virtual address space for a process is the set of virtual memory addresses that it can use.

The maximum virtual address space for 32-bit Windows is 4GB for 64-bit Windows is 16 TB.

In 32 bit system, by default 2 GB is allocated to user-mode VAS where SQL Server runs and the remaining 2 GB is allocated to kernel-mode VAS that is used by the system or other shared processes.

The user-mode VAS is divided into two distinct regions. One is the space occupied by the buffer pool that serves as a primary memory allocation source of SQL Server and the rest is occupied by external components that reside inside the SQL Server process, such as COM objects.

As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target.

When there is no excess of free memory it frees the memory that has more than the min server memory setting, and Windows indicates that there is a shortage of free memory.

Role of MIN and MAX Server Memory Configuration Option

These server memory configuration options are used to reconfigure the amount of memory that is managed by the SQL Server Memory Manager for a SQL Server process used by an instance of SQL Server.

MIN and MAX server memory configuration options indicate the upper and lower limits to the amount of memory used by the buffer pool of the Microsoft SQL Server Database Engine.

The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 MB. There is no need to restart the machine or SQL Server Instance post making these changes.

We can change the values of min & max server memory configuration options using GUI in SQL Server Management Studio (SSMS).

We can do this with the following steps:

1. Firstly, Right-click on SQL Server Instance and choose properties.

2. After that click on the “memory” tab from the left side pane.

3. From here we can change the value to min and max server memory.

Max Worker Threads

The max worker threads help to optimize performance when large numbers of clients are connected to the SQL server.

The default value is 0, which allows SQL to automatically configure the number of worker threads at startup. This works for most systems. Max worker threads are an advanced option and so should not be altered without proper analysis.

If the average work queue length for each scheduler is above 1 then adding more threads to the system gives benefits. But it’s only when the load is not CPU-bound or experiencing any other heavy waits.

Index Create Memory

The index create memory option is another advanced option that usually should not be altered. It controls the max amount of RAM initially allocated for creating indexes.

The default value for this option is 0 which means that it is managed by SQL Server automatically.

Min Memory per Query

When a query is run, SQL tries to allocate the optimum amount of memory for it to run efficiently.

The best practice is to leave this setting at the default value of 0, to allow SQL to dynamically manage the amount of memory allocated for index creation operations.

General SQL Server Memory Allocation Best Practices

Generally, SQL Server will try to eat up all the memory from the Operating System by default. This can greatly stress the Operating System from performing its core tasks. In order to prevent this, perform the following:

For Systems with 4 GB of RAM: Reserve 1 GB of RAM for the OS
For Systems of 16 GB of RAM: Reserve 4 GB of RAM

We need to reserve 1 GB for the OS for every 8 GB of RAM greater than 16 GB.

For 32 GB Systems: Reserve 6 GB of RAM for the OS (4 GB till 16, then 1 GB for every 8 GB)

For SQL Server with Higher Memory, Allocate 10% System Memory to OS.

Best Practices for SQL Server Buffer Pool Extension

1. Implement it only if we have High-Speed Disk I/O Subsystems (Fusion-IO or SSD).
2. Performs best with OLTP workloads that are read-heavy.
3. Not recommended for Data-warehousing or write-heavy workloads.
4. Recommended for Systems with memory ranging from 8 GB – 64 GB.
5. Works in SQL Server Standard Editions.

Recommendations for In-memory OLTP

1. Memory Requirements are outside the regular SQL Server and OS Memory Requirements.
2. Size your In-memory Tables for memory size and allocate memory to system OS.
3. Not recommended for Systems with Less than 64 GB of System Memory.

[Need assistance? We are happy to help you]

Conclusion

To conclude, today we saw SQL server memory allocation best practices that our Support Techs follow while allocating memory