How SQL Server Manages Memory

The Buffer Manager is an integral component of SQL Server’s architecture. Understanding the Buffer Manager is important for administrators and application programmers.

This post will explain the principles of the buffer architecture, and discuss practical aspects of administering, monitoring and troubleshooting the buffer.

Principles

Physical disks are very slow compared to RAM and CPU data transfer speeds. The primary goal of the Buffer Manager is to manage the limited resources of the physical disks through caching, batching and prediction.

The buffer cache (also known as the buffer pool) is an area in RAM where SQL keeps data for fast access. All interaction between disks and users/applications (e.g. Dynamics NAV) goes through the buffer cache.

SQL Server arranges data on disk and in the buffer cache into pages, which are 8 kilobyte blocks. All tables and indexes in SQL are comprised of pages.

Reading and Writing Pages

When a SELECT statement is issued to SQL Server, these actions occur:

1. SQL Server determines how it will execute the query and which data structures need to be accessed e.g. an index seek or table scan
2. If the required data pages are not already in the buffer cache, the buffer manager will read the data from the physical disk and write it into the buffer cache. This is called a physical read
3. The data pages in the buffer cache are read by SQL to fulfil the request. This is called a logical read

When an UPDATE statement is issued to SQL Server, these actions occur:

1. SQL Server determines which tables and indexes are effected and what pages need to be updated
2. The data pages are updated in the buffer pool and marked as dirty. This is called a logical write
3. Later, dirty pages are written out to the disk. This is called a physical write, and the process is known as checkpointing

Releasing Pages and Least-Recently-Used

The Buffer Manager will not release (free up) pages in the cache until it is necessary. This means that SQL may use many gigabytes of RAM even when it is not processing transactions. This is normal behaviour.

If new data needs to be read from the disk and the buffer cache is already full, the Buffer Manager will release pages to make space. When deciding which data pages to give up, the Buffer Manager will choose the ‘least recently used’ pages, i.e. the data that has remained unused for the longest period.

The least-recently-used principle keeps ‘hot’ data in RAM constantly and less important data will cycle in and out of the buffer as needed.

Tools and Administration

Maximum Server Memory

SQL Server has an option to limit memory usage. It is important to set an appropriate limit here so that SQL Server doesn’t starve the operating system and any other applications of RAM.

In SQL Management Studio, right click on the server node in the Object Explorer pane and select Properties. On the Memory tab, set the field Maximum server memory.

Performance Monitor

Performance counters are available for tracking the buffer and physical disk usage in Windows Performance Monitor. Some commonly used counters are:

If the server has insufficient memory for the workload, either add RAM, or review the maximum limit. If the buffer or physical disk appears to be a bottleneck in your SQL instance, this could mean:

• Inefficient queries are reading more data than necessary, e.g. full table scans. Either improve indexing, or remove the inefficient queries using alternative solutions
• Indexes may be fragmented (meaning related data pages are split across the disk) which slows down physical reading. Install index maintenance and optimization routines to periodically re-organise indexes. Ola Hallengren’s freely available scripts are Fenwick’s standard tool for this

Direct Interrogation

SQL offers DMVs which allow inspection of buffer cache contents, allowing us to determine what is using space in the cache. Examples are available here.