SQL Server buffer pool

No matter how much memory you put in a system, SQL Server will use all it can get until it’s caching the whole database in memory.  Why does it do this?  SQL Server is a database: SQL writes data to hard drives and fetches it when needed (e.g. SELECT statements are issued). To improve performance, SQL Server caches data in memory.  With SQL only one server’s SQLserver.exe can touch the data files at any given time.  SQL Server knows that once it reads a piece of data from the drives that data isn’t going to change unless SQL Server itself does the changing (an UPDATE, INSERT, or DELETE query).  Data can be read into memory once and safely kept around forever.  If you have a server with enough memory to cache the entire database, then that is what SQL Server will do.

The buffer pool is by far the largest consumer of memory in SQL Server, it holds and manages SQL server’s data cache.  Monitoring the buffer pool is a good way to sport memory pressure. Performance monitor provides counters like:

Memory\Available MByte: This shows how much physical RAM windows has that isn’t being currently used. There are various ideas as to how much is a good minimum with some recommending 150MB and other recommending up to 1GB. Try to keep at east 500MB as a minimum so there will be enough RAM to enable support tool to be run on the server. If the available MB drops below 64MB then most windows servers will signal a low memory notification that SQL Server monitors for, and the SQLOS inside of SQL Server will reduce memory usage as a result of this notification occurring.

Buffer Manager \ Page Life Expectancy: This shows how many seconds SQL server expects to keep a page in the data cache and is a good measure of memory pressure. If the PLE is below 300 you should check ‘available Mbytes’ to see how much RAM is available, if ‘available Mbytes’ is less than 5MB then windows will begin trimming working sets aggressively.

• If PLE is low (below 300) and available Mbytes is high (above 600MB) you should increase ‘Max Server Memory’.
• If PLE is low and Mbytes is low you need to buy more RAM.
• If PLE is high and Mbytes is low then you should lower ‘Max Server Memory’.

Another thing to look for if the PLE is low is the lazy writes/sec. If this is showing non-zero values and the Page reads/sec and Page writes/sec is high then there may be buffer pool contention.

Also if the PLE is low, looking at the ‘Memory manager\Total server memory’ and the ‘Memory manager\Target server memory’ will tell you if SQL has reduced the size of the buffer pool. (The Total server memory is the current size of the buffer pool. The Target server memory is the ideal size for the buffer pool).  In a stable SQL server the total and target server memory size will be very close. If the Total server memory is much less than the Target server memory size then either the workload on the server is not big enough to grow the buffer pool or the buffer pool cannot grow due to memory pressure and further troubleshooting is required.

Min and Max SQL Server memory.

The min and max SQL server memory values control the allowable amount of all SQL servers’ memory usage.

The Min server memory value is the amount of RAM that SQL will try to keep committed. When SQL server starts it initially has only the minimum amount of memory required and it grows this amount as necessary. When it hits the ‘min server memory’ value it will then not release any memory below the min value.

The Max server memory value is the upper limit for SQL server memory usage.

Setting the Max server memory value is the more important of the two as if SQL server takes too much memory from Windows server then Windows will start to trim SQL servers working set (The working set of a process is the set of pages in the virtual address space of the process that are currently resident in physical memory).

There are two ways to calculate the value for Max server memory:

1. Examine the SQL server’s maximum usage: Set the SQL server to dynamically manage memory and then keep an eye on the ‘Total server Memory’ value. Observe this value over a business cycle and then set the ‘Max server memory’ size to the LOWEST value that was observed.
2. Determine the requirement for processes outside SQL server:

Here you try to determine how much memory is required by things like the windows server OS, other applications, etc:

2GB for the OS

1 GB for each 4GB of Ram installed from 4-16GB

1 GB for each 8GB of Ram installed from 16GB+

1-3 GB for applications running on the server e.g. antivirus, backup programs, etc.

After you have configured the Max server memory you need to monitor it so that you can determine if it is effective.

If after configuring the ‘Max server memory’ setting you still find that Windows is trimming the working set then you can configure SQL to use ‘Lock pages in memory’. When this is configured the buffer pool pages are locked and windows cannot touch them when trimming.

Configuring Lock Pages in Memory by default is probably a good preventative measure.