liveBook · Manning

Buffer pool

As explained in 100 SQL Server Mistakes and How to Avoid Them

A buffer pool is the region of SQL Server memory used to cache data and index pages for database operations.

Mistake: letting SQL Server keep its default memory limit. The buffer pool caches data and index pages, and SQL Server can otherwise allocate as much memory as it needs for that cache. section 10.3

That choice can starve Windows and required agents such as antivirus, monitoring, or inventory tools. The risk increases when Lock Pages In Memory prevents the working set from being trimmed. section 10.3 section 10.14

A 64 GB server

For a solitary SQL Server instance on a 64 GB server, a 48 GB maximum buffer-pool allocation leaves approximately 25% for Windows and other requirements. Multiple instances require that allowance to be divided between them. section 10.3

Listing 10.3 Configuring maximum memory
sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO

sp_configure 'max server memory', 49152 ;
RECONFIGURE ;
GO

Use sp_configure to enable advanced options and set max server memory; the value is specified in megabytes. section 10.3

Safer practice:

  • Configure max server memory instead of accepting the default. section 10.3
  • Reserve memory for Windows, other applications, and SQL Server components outside the buffer pool. section 10.3
  • Recalculate the allocation when hosting multiple SQL Server instances. section 10.3

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Defines the cache and explains memory configuration, but does not descend into page-replacement internals.
Examples
Uses a 64 GB solitary-instance server, a 48 GB setting, and an sp_configure script.
What it assumes
Assumes familiarity with SQL Server instances and server memory while defining the buffer pool from zero.
What you can do after
Operate/configure SQL Server memory by setting max server memory with sp_configure.
How it connects
Connects the buffer pool with max server memory, Lock Pages In Memory, Windows, and data and index pages.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.