0

SQL Buffer pool is consuming more memory in sql server 2017. Is there any way to reduce it?? or How can we find what is consuming more? We are receiving alert for low buffer cache hit ratio. MEMORYCLERK_SQLBUFFERPOOL 90365.62 MB

One particular database alone consuming 87GB buffer pool. Total memory allocated to this instance is 100GB

Improve this question
3
  • 2
    All I can say is what you are seeing is normal. Buffer pool being biggest consumer of memory has to be the one taking the most. BCHR counter should ideally be ignored reason This Article.
    – Shanky
    Jun 17, 2020 at 11:25
  • You want to reduce buffer pool? Why? Because SQL Server is using a lot of memory for buffer pool? If you reduce buffer pool capacity that means even more data will have to go between memory and disk when you need it. You kind of want to use as much memory as possible for buffer pool. As for buffer cache hit ratio, it's just a number, is your application performing acceptably, or have you directly tied some performance issue to this ratio? How have you made this tie? If your data is bigger than memory, and you need to read all the data, your only solution is to add memory (or remove data).
    – Aaron Bertrand
    Jun 17, 2020 at 13:18
  • Thanks for your comments. We received alert for buffer cache hit ratio. so only i had doubt on this. Do we need to check the queries which consuming more memory???
    – kumar
    Jun 17, 2020 at 16:33

1 Answer 1

Reset to default
1

SQL Buffer pool is consuming more memory in sql server 2017.

That is by design.

The default memory management behavior of 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.

Memory Management Architecture Guide - Dynamic Memory Management

The thinking is that it's better for SQL Server to use the memory to cache data than to leave it unused.

Is there any way to reduce it??

You can set MAX SERVER MEMORY to constrain it if you want.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.