Memory configuration
Last updated
Was this helpful?
Last updated
Was this helpful?
The max server memory option sets the maximum amount of memory that the SQL Server instance can use. It is generally used if multiple applications are running on the same server where SQL Server is running and you want to guarantee that these applications have sufficient memory to function properly.
Some applications only use whatever memory is available when they start and do not request additional memory, even if they are under memory pressure. That is where the max server memory setting comes into play.
On a SQL Server cluster with several SQL Server instances, each instance could be competing for resources. Setting a memory limit for each SQL Server instance can help guarantee best performance for each instance.
Recommends leaving at least 4GB to 6GB of RAM for the operating system to avoid performance issues.
Using SQL Server Management Studio to adjust minimum or maximum server memory requires a restart of the SQL Server service. You can also adjust server memory using transact SQL (T-SQL) using this code:
Nonuniform memory access (NUMA) is a memory-access optimization technology that helps avoid extra the load on the processor bus.
If NUMA is configured on a server where SQL Server is installed, no additional configuration is required because SQL Server is NUMA-aware and performs well on NUMA hardware.
The index create memory option is another advanced option that should not normally need to be changed from defaults.
It controls the maximum 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. However, if you encounter difficulties creating indexes, consider increasing the value of this option.
When a query is run, SQL Server tries to allocate the optimum amount of memory to run efficiently.
By default, the min memory per query setting allocates >= to 1024KB for each query to run. It is a best practice is to leave this setting at the default value in order to allow SQL Server to dynamically manage the amount of memory allocated for index creation operations. However, if SQL Server has more RAM than it needs to run efficiently, the performance of some queries can be boosted if you increase this setting. Therefore, as long as memory is available on the server that is not being used by SQL Server, any other applications, or the operating system, then boosting this setting can help overall SQL Server performance. If no free memory is available, increasing this setting might hurt overall performance.
