Buffer Pool

According to Microsoft TechNet SQL Server glossary, “a block of memory reserved for index and table data pages.”

A buffer pool is the area where various type of cache (buffer cache, plan cache, log cache and so forth) are stored.

Although sometimes used synonymously with buffer pool, the buffer cache (sometimes called the data cache) is a particular area of the SQL Server Buffer Pool. A buffer cache can store a certain amount database data (data pages) in local memory so that no I/O operation is required for retrieval.

For example, say we run a specific select query to show all data rows of a table. That means all the data pages of that table are required to fulfill the query. The first step is moving the data pages from disk to buffer pool/cache. The reading of data pages from disk to memory is known as physical IO.

However, if the same query is run again, there is no need to read data pages from disk to buffer pool because all the data pages are already in the buffer pool. This kind of data retrieval operation is commonly known as logical IO, and is typically faster than physical I/O.

With SQL Server 2014, DBAs can expand the buffer pool with a nonvolatile storage device such as a solid-state drive or an SSD array. Furthermore, SQL Server will only write clean pages to the buffer extension to avoid any possibility of data loss. Extending the buffer pool permits significant performance improvements for online transaction processing (OLTP) workloads, and particularly for read-intensive loads.

Idera’s SQL Diagnostic Manager provides a Cache Manager function to help you keep track of your cache usage. Spikes in cache usage can mean memory issues, and chronic high cache usage probably means it’s time for index updates or query tuning.