0

What is the formula for calculating the maximum size of the buffer pool (data cache) for SQL Server (when there is only one instance of the machine).

Say the machine has 1 GB of physical memory, what is the maximum size of the buffer pool in such a scenario?

Assume max server memory is 0.

The question came about when reading about memory grants and I read that the resource semaphore can use 75% of the buffer pool for query memory grants. Then the natural question is well how is the buffer pool calculated.

Thanks

Improve this question

1 Answer 1

Reset to default
1

With max server memory set to 0, two factors will dictate how much memory SQL Server will use, and thus how big the buffer pool will grow.

But before talking about those, consider that with out any constraints, SQL Server will read a data page into memory and keep it there until something else request that space.

  1. Size if the database(s) will dictate how large the buffer pool will grow. If you have a 20 GB database any 128GB is system memory, it’s likely the buffer pool will grow to around 20GB, but no larger. At this point, you entire database would be in memory.

  2. The other factor that will limit the buffer pool is Windows. Windows will let SQL consume memory as along as another application or service does not need it. It’s very possible for SQL Server to want additional memory, but for Windows to not grant it. It also possible to a Windows to take memory back. This all depends on what other services and applications run on the server.

So, there is not really a set number. SQL Server will grow it as large as it can, in an attempt to put the entire database in memory, until Windows says “no”.

As for how large should you plan for it to be? That answer can vary. Start with a nominal amount of memory, say 1/8 a 1/4 the size of the database, and monitor it for memory contention. If you see any, increase memory until you don’t.

Improve this answer
3
  • So you are saying that the buffer pool can in theory use almost 100% of the physical memory on the machine?
    – xhr489
    May 15, 2022 at 16:04
  • In theory, yes. However, Windows would never let that happen. It’s not uncommon for a SQL Server with 384GB of memory be at 98% physical memory utilization when max server memory is not set.
    – Brendan McCaffrey
    May 15, 2022 at 16:48
  • 3
    Note, it's SQL Server that reacts to the system low memory condition by trimming its caches, rather than the OS taking memory back. Windows will take memory if SQL Server isn't using lock pages in memory by paging committed virtual memory to the page file, but SQL Server hates that, and tries to avoid that happening.
    – David Browne - Microsoft
    May 15, 2022 at 19:52

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.