How is the size of the buffer pool (data cache) calculated?

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply