Setting a fixed amount of memory for SQL Server

By: Sergey Gigoyan | Comments (4) | Related: > SQL Server Configurations

Problem

There is a frequently asked question among many people who work with SQL Server: "Why does SQL Server use almost all of the memory on the server, even if there is essentially no activity on the databases?" This is a common and logical question if you are not familiar with SQL Server memory management and you have not investigated the question. This tip is intended to explain the default SQL Server memory configurations and how to configure SQL Server to use a fixed amount of RAM.

Solution

When a SQL Server instance is running on your machine, you may find that memory usage is perceived as too high. Commonly, most of the memory is used by SQL Server. Moreover, when you increase memory on the machine, but the database load remains the same, it is possible that even after the memory upgrade SQL Server will be the top consumer of memory. The reason is that by default SQL Server dynamically allocates memory during its activity and does not release it until there is a request from Windows.

It is normal for SQL Server, because it tries to keep as much memory as possible for performance purposes. It reserves memory and caches data into memory to decrease access to disk drives and therefore increasing performance. When other processes on the same server require memory, SQL Server releases the needed memory. So, SQL Server can use almost all available memory on the server.

If your server is a dedicated database server there is no problem regarding memory allocation, however sometimes there are many applications running on the database server. Moreover, some applications use memory available at their starting time and may be unable to request additional memory from Windows, if needed. In this case, to permit the other applications to work properly, we can limit SQL Server's access to memory by setting the "max server memory" option. By default it is set to 2147483647 MB, which allows SQL Server to use approximately all of the server's memory. We can change this setting to a lower value, according to our requirements. It could be done either by SQL Server Management Studio or a T-SQL script.

Setting "max server memory" using SQL Server Management Studio

At a very high level, let's monitor memory usage on our machine where SQL Server is running, then set "max server memory" in SQL Server Management Studio (SSMS) and view the changes in memory usage. In our example, the test server has 1 GB of Memory (RAM) and SQL Server 2014 is installed, but not in a running state. Also, there are no other resources consuming memory on our server. By running Task Manager we can monitor memory usage in the Performance tab to get a sense of the memory usage prior to starting SQL Server:

Review Memory usage in Windows Task Scheduler prior to starting SQL Server

Now we will start SQL Server and monitor memory usage again:

Start SQL Server via SQL Server Configuration Manager

We can see that the amount of used memory has increased:

Validate that memory has increased in Windows Task Scheduler

And the top memory "consumer" is SQL Server:

Review the top consumers of memory on the server

In SSMS we can run the following query and find the memory used by SQL Server in megabytes:

SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MB
FROM sys.dm_os_process_memory

In our server it is 117 MB:

Review the memory usage in SQL Server before code is run

Now let's do some activity in our instance. We have TestDB database on our instance and TableA in it, which contains 300,000 rows. Let's select all rows from this table:

SELECT *
FROM TestDB.dbo.TableA

When the query finishes we will check memory usage again in SQL Server:

SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MB
FROM sys.dm_os_process_memory

In this case we can see that 432 MB is used by SQL Server, however there are no active queries on our instance:

And 93% of our server memory is used (mostly by SQL Server):

Review the memory usage in Windows Task Scheduler after load was added to the server

Set "max server memory" in SQL Server Management Studio

Now we will set the "max server memory" option to limit the memory usage by SQL Server. We can do this by right-clicking on our instance and choosing "Properties":

Navigate to the SQL Server Properties in Management Studio

After than choose the "Memory" page:

We can see above that "Maximum server memory" is set to 2147483647 MB. This is the default value. We will change it to 200 MB and click "OK":

Set the SQL Server max memory to 200 MB in SQL Server

By running this query again we can see that memory used by SQL Server decreased to 244 MB:

SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MBFROM sys.dm_os_process_memory

A question arises: why 244 MB, when we set "maximum server memory" for SQL Server to 200 MB? The reason is that our query returns currently used memory, however max server memory controls memory used by the buffer pool, compiled memory, all cache and so on, but it does not control memory for linked server providers other than SQL Server, memory allocated by a non SQL Server DLL, memory for thread stacks, memory heaps, etc. and therefore the result of our query can be a bit more than the value of "max server memory".

Set "max server memory" in SQL Server using T-SQL

We can set "max server memory" also by using a T-SQL script:

DECLARE @maxMem INT = 2147483647 --Max. memory for SQL Server instance in MB
EXEC sp_configure 'show advanced options', 1
RECONFIGURE

EXEC sp_configure 'max server memory', @maxMem
RECONFIGURE

This script sets "max server memory" to its default value - 2147483647 MB:

T-SQL script to change the max memory in SQL Server

We can check in SSMS that "max server memory" has changed:

Recommendations on setting "max server memory"

Microsoft recommends to allow SQL Server to use memory dynamically, however in some situations it is preferable to limit the memory usage by SQL Server. For example, when our server is not a dedicated database server and there are other applications running on the same server which are unable to request memory from Windows, it would be better if we estimate the maximum memory which can be used by SQL Server and set "max server memory" appropriately.

Also, when there are more than one SQL Server instances running on your server, we can set "max server memory" for each of them, considering their load, to control memory usage among these instances. Note that we should consider that the sum of "max server memory" values for all instances will be less than the total physical memory in our server. This ensures that free memory will be available immediately for instances after start up. However if one of instances is not running, the running instances will be unable to use the remaining free memory.

Conclusion

As we can see, SQL Server tends to use all available memory on a server. However in some cases, when we need to limit memory used by SQL Server or properly distribute memory among SQL Server instances we can set the "max server memory" option for each instance. One most important thing which we should consider before managing memory for SQL Server is to correctly estimate the maximum amount of memory which will be needed for each instance.

Next Steps

Read related information:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Wednesday, February 6, 2019 - 5:32:12 AM - Tanmay	Back To Top (78975)
How we can see memory value in registry.