Are you trying to perform SQL Server Memory Allocation the right way?
This guide will 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.
When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory.
Maintaining this free memory prevents the operating system (OS) from paging.
If less memory is free, SQL Server releases memory to the OS.
If more memory is free, SQL Server may allocate more memory.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to use SQL server.
In this context, we shall look into the best practices to 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 with SQL Server Memory Allocation? We are happy to help you.]
Conclusion
This article covers SQL server memory allocation best practices to follow while allocating memory.
By default, SQL Server can change its memory requirements dynamically based on available system resources.
SQL Server is designed to use all the memory on the server by default.
The reason for this is that #SQL Server cache the data in the database in #RAM so that it can access the data faster than it could if it needed to read the data from the disk every time a user needed it.
Changing SQL Server's Max Server memory is an online option – you don't need to restart SQL Server.
Though when you do make the change, you can and likely will cause data or procs to leave their caches so things could be a slight bit slower for a short while after you run it.
SQL Server will always give you better performance because the query is executed on the server.
Access on the back-end won't help because your client application will need to pull all the data from the tables, and then perform the join locally.
SQL Server has better indexing options.
'Max server memory' ensures that SQL Server buffer pool will not use more memory than specified in 'max server memory'.
So, SQL Server can use less memory than the value of 'max server memory' and other services can use the remaining memory.
You can monitor memory use at the database level as follows:
1. Launch SQL Server Management Studio and connect to a server.
2. In Object Explorer, right-click the database you want reports on.
3. In the context menu select, Reports -> Standard Reports -> Memory Usage By Memory Optimized Objects.
This article covers SQL server memory allocation best practices to follow while allocating memory.
By default, SQL Server can change its memory requirements dynamically based on available system resources.
SQL Server is designed to use all the memory on the server by default.
The reason for this is that #SQL Server cache the data in the database in #RAM so that it can access the data faster than it could if it needed to read the data from the disk every time a user needed it.
Changing SQL Server's Max Server memory is an online option – you don't need to restart SQL Server.
Though when you do make the change, you can and likely will cause data or procs to leave their caches so things could be a slight bit slower for a short while after you run it.
SQL Server will always give you better performance because the query is executed on the server.
Access on the back-end won't help because your client application will need to pull all the data from the tables, and then perform the join locally.
SQL Server has better indexing options.
'Max server memory' ensures that SQL Server buffer pool will not use more memory than specified in 'max server memory'.
So, SQL Server can use less memory than the value of 'max server memory' and other services can use the remaining memory.
You can monitor memory use at the database level as follows:
1. Launch SQL Server Management Studio and connect to a server.
2. In Object Explorer, right-click the database you want reports on.
3. In the context menu select, Reports -> Standard Reports -> Memory Usage By Memory Optimized Objects.
