question

andirafian-2799 avatar image
1 Vote"
andirafian-2799 asked andirafian-2799 commented

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

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?

196517-setting.jpg
196455-query.jpg
196456-memory.jpg

thank You
by Andi


sql-server-generalsql-server-transact-sqlsql-server-reporting-servicessql-server-analysis-services
setting.jpg (51.6 KiB)
query.jpg (89.7 KiB)
memory.jpg (60.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered andirafian-2799 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered andirafian-2799 edited

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://docs.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-ver15
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hi @OlafHelper-2800

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.!



0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @andirafian-2799,

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".



· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi to @Cathyji-msft thank you for answering

right like that
very little memory usage for other software

0 Votes 0 ·
setting.jpg (51.6 KiB)

Hi @andirafian-2799,

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


0 Votes 0 ·

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

0 Votes 0 ·
Show more comments
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered andirafian-2799 commented

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://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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?

0 Votes 0 ·

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.


0 Votes 0 ·

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

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered andirafian-2799 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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?

0 Votes 0 ·

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

197175-disk-usege-2842022.jpg


0 Votes 0 ·