Setting a fixed amount of memory for SQL Server


By:   |   Updated: 2016-03-07   |   Comments (4)   |   Related: More > 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:

Review the memory usage in SQL Server after code is run

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:

Memory Properties interface in SSMS

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
	

Review the memory usage in SQL Server after the max memory has been configured

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:

Validate the max memory setting in SSMS

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:



Last Updated: 2016-03-07


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, February 06, 2019 - 5:32:12 AM - Tanmay Back To Top

 How we can see memory value in registry.


Tuesday, March 20, 2018 - 7:19:57 AM - Dinusha Back To Top

Thank you very much for this article. Its really helpful to me.

 

Thanks

 


Saturday, July 16, 2016 - 2:32:08 AM - Sergey Gigoyan Back To Top

 Hi Nagesh,

The value in 'max server memory' does not mean that SQL Server buffer pool acquires the whole specified memory immediately after it starts. Memory increases regarding to workload, so it is even possible, the memory allocated to buffer pool does not reach to the maximum value. '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. 

Thanks

 

 


Thursday, July 14, 2016 - 5:15:31 PM - Nagesh Back To Top

Hi Sergey Gigoyan,

 

I need some clarification on memory configuration at SQL. There is an application APP1 hosted on a database server (i.e Application and database both are hosted on same server) and we have configurred 10gb as Max server memory for SQL server instance. Total memory is 16gb where 6gb left for OS and other applicaions.

After i rebooted the server the application APP1 is using much memory i.e. 9GB than SQL server. SQL is just occupied only 4gb and remain 3gb memory is free.

Here my question: Is MAX server Memory will not lock the memory for SQL server until it occupied by queries? i.e. Can other apps use the MAX server memory until occupy it occupied by SQL?

 

Please need your Valuable suggesion.

 

Thanks in advance,

Nagesh



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools