Buffer Manager for SQL

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:

Counter Set Counter Explanation
SQLServer: Buffer Manager Page life expectancy (seconds) The average age of all pages in the buffer cache

 

SQLServer: Wait Statistics Page IO latch waits A latch wait occurs when the buffer cache is reading pages from the physical disk
PhysicalDisk Current Disk Queue Length The number of tasks pending on the physical disk

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.

 

 

mm
Written By Narada Ellis

Narada is the Fenwick Software guru in the development of custom software integrating with NAV, using ASP.NET, SQL Server and Javascript. Two of the significant systems that Narada has developed are: an in-vehicle system he designed and wrote for waste bin collection, and the web-based job scheduling tool, EasyDispatch. Narada joined Fenwick Software in 2011 after gaining a degree in Information Technology from Swinburne University.

One Response to How SQL Server Manages Memory
  1. […] the database is in sync with the log. The LSN on each page is updated when SQL writes data from the buffer cache (RAM) to the physical data file – this is known as a ‘checkpoint’. The start and end of the […]


[top]

Leave a Reply

Your email address will not be published. Required fields are marked *

Thanks!

We're here to help!

Call us

03 9695 3333

Or, leave us a message