memory usage (MEMORYCLERK_SQLBUFFERPOOL) keeps increasing in sql server management studio

andi rafian 31

I use sql server management studio 17.9.1 with redundant working system
PC has a memory capacity of 32GB
initial setting of 20GB memory usage at SQL server
the problem is that memory usage will continue to rise, until it reaches 20GB (MAX) within 1 month

and resetting memory usage from 20GB to 25GB From 32GB
25/04/2022 15:00 SQL server memory usage 23.5 GB
26/04/2022 15:50 SQL server memory usage 24,99 GB

after checking the large memory problem is in MEMORYCLERK_SQLBUFFERPOOL

how to deal with this problem memory usage keeps increasing?
can MEMORYCLERK_SQLBUFFERPOOL be reset?

thank You
by Andi

Accepted answer

Erland Sommarskog 113.5K Reputation points MVP

2022-04-28T21:25:17.393+00:00

The database is not very big, only around 60 GB, not including the log file. (And the log file does not matter when it comes to estimations of how much RAM you need.)

As I discussed in my previous post, you either need to tune queries or add RAM. Since you don't seem to be very knowledgeable about SQL Server, I guess adding RAM is the easier option. Given the size of the database, 64 GB should be enough.
Please sign in to rate this answer.
andi rafian 31 Reputation points

2022-04-30T02:28:03.81+00:00

I'll try to get a bigger memory later
thank you for the information, this is very helpful
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

4 additional answers

Olaf Helper 45,366 Reputation points

2022-04-26T05:27:55.063+00:00

how to deal with this problem memory usage keeps increasing?

What for a problem, do you have performance issues?
It's normal and by design that SQL Server allocate as much memory as it needs and as it can get.
See https://learn.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-ver15
Please sign in to rate this answer.
andi rafian 31 Reputation points

2022-04-26T06:36:57.317+00:00

hi @Olaf Helper

the problem I'm facing is memory usage will continue to rise and if it reaches the max setting limit
the PC will be very slow, I have tried restarting the PC but still can't,

is there a solution regarding MEMORYCLERK_SQLBUFFERPOOL so that it can be reset.!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
CathyJi-MSFT 22,321 Reputation points Microsoft Vendor

2022-04-26T06:54:57.26+00:00

Hi @andi rafian ,

If you’re going to run other software on the server, you can set SQL Server’s maximum amount of memory to leave memory free for other applications. If there is no other software on this server, SQL Server will allocate as much memory as it needs and as it can get. And SQL Server never lets go or releases memory unless the server comes under memory pressure (like if other apps need memory and Windows sends out a memory pressure notification).

Below is a blog to help you better understood this.

A Sysadmin’s Guide to Microsoft SQL Server Memory

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.
andi rafian 31 Reputation points

2022-04-26T08:32:03.85+00:00

Hi to @CathyJi-MSFT thank you for answering

right like that
very little memory usage for other software

CathyJi-MSFT 22,321 Reputation points Microsoft Vendor

2022-04-26T08:48:34.75+00:00

Hi @andi rafian ,

Did Windows sends out a memory pressure notification? According to your screenshot, it seem there is no many other software on your PC. The biggest consumers of memory is the buffer pool in SQL server. It will allocate memory and not release it unless the system is under memory pressure. That is an expected behavior! Limit max server memory of SQL Server if you are concerned.

If you want to know why SQL Server doesn’t release the memory if there is no load on the server. Please read below blog, you will have an answer.

SQL Server Memory Buffer Pools: Understand the Basics

andi rafian 31 Reputation points

2022-04-26T09:03:34.63+00:00

hi @CathyJi-MSFT

that's right, server_PC only uses SQL_server not used for anything else,
when the memory problem was maxed out (when setting 20GB) and the PC system was too heavy to run, at that time I changed the memory usage setting to 25 GB and the system returned to normal but SQL memory used 23.5 GB and it kept going up.
even though the database has only been created for one month

CathyJi-MSFT 22,321 Reputation points Microsoft Vendor

2022-04-26T09:19:37.29+00:00

Hi @andi rafian ,

> when the memory problem was maxed out (when setting 20GB) and the PC system was too heavy to run, at that time I changed the memory usage setting to 25 GB and the system returned to normal

Increased max memory, then issue resolved. Did you mean that SQL server has memory pressure?

SQL server memory usage keep growing is normal behavior. If you do not want to increase max memory manually when you meet SQL server memory pressure issue. You can set max memory to default value 2147483647. In this setting, the SQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system. The SQL Server Database Engine does this by using the Memory Notification APIs in Microsoft Windows.

Refer to Dynamic Memory Management.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Tom Phillips 17,741 Reputation points

2022-04-26T13:06:46.34+00:00

That is completely normal and expected behavior. SQL Server never releases RAM once it is allocated unless there is a low memory message sent from Windows.

If your system is low on RAM, you should set the max server memory smaller. That will reserve more RAM for other applications.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15
Please sign in to rate this answer.
andi rafian 31 Reputation points

2022-04-27T07:39:42.6+00:00

thank you for answering
maybe my memory is lacking, so I have to add memory on the server
How much memory is suitable for a working SQL server?

Tom Phillips 17,741 Reputation points

2022-04-28T11:46:48.563+00:00

What you describe is not a problem. That is how SQL Server works. It allocates RAM up to the "max server memory" setting. There is no problem to fix.

You have the max server memory set to 25GBs. SQL Server will keep growing until it reaches 25GBs.

andi rafian 31 Reputation points

2022-04-30T02:28:36.417+00:00

I'll try to get a bigger memory later
thank you for the information, this is very helpful
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Erland Sommarskog 113.5K Reputation points MVP

2022-04-26T22:07:05.637+00:00

As I understand it, this is a machine dedicated to SQL Server. If SQL Server starts to run slow when you hit the limit, this suggests that queries are constantly reading more data than fits into the buffer cache. Yes, this makes the machine slow.

The simple resolution is to add more memory to the machine. 24 GB is not much for a database server.

The other alternative is to analyse and tune the workload so that it runs more efficient queries.
Please sign in to rate this answer.
andi rafian 31 Reputation points

2022-04-27T07:37:23.643+00:00

thank you for answering
So how much memory is suitable for SQL server?

Erland Sommarskog 113.5K Reputation points MVP

2022-04-27T21:30:54.603+00:00

The joke is: How much memory does SQL Server need? More!

More seriously, it depends on your workload. And the size of the database.

The point with the buffer pool is simply this: Reading from memory is faster than reading from disk. Thus, when you run a query SQL Server reads those pages into memory, and keeps them in memory, in case you need to query them again. The more of the database(s), it can keep in memory, the faster it can serve the queries.

So if you have a 100 GB database, you may need 100 GB of RAM, if you run queries all over the database. In practice, though, in many application, queries are directed towards a smaller portion of the database, for instance recent data. Therefore you may get away with less than 100 GB in RAM.

But there is one more thing to consider: how efficient your queries are. Users may mainly be asking for recent data, but there may not be appropriate indexes, or queries may be written in such a way that indexes cannot be used efficiently, leading to that SQL Server has to scan tables in whole, despite the queries only asking for recent data.

How big are the database(s) on the server?

andi rafian 31 Reputation points

2022-04-28T01:56:06.703+00:00

I really don't understand because I'm not a server engineer, thanks for helping me

I sent screenshot of disk usage data

system flow
SCADA SERVER system sends continuous data to SQL server every 30s.

SQL server will receive 2 data
data file space reserved and Transaction LOG space reserved,

*data file space reserved data that will be used as system data
*Transaction LOG space reserved. the data is very large, 24GB is in 2 days, the log will be backed up and deleted in SQL data every week

memory will also continue to grow slowly, I don't understand this problem why memory keeps getting bigger, maybe I should add memory
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

memory usage (MEMORYCLERK_SQLBUFFERPOOL) keeps increasing in sql server management studio

4 additional answers

Your answer