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



  • 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



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2