An SQL server buffer pool is
nothing but a place in system memory that is use for caching tables and index
data page for read or modify from the disk. It is also known as sql server
buffer cache. The primary purpose of the SQL buffer pool is to reduce database
file I/O and improve the response time for data retrieval.
Hard disks are slow, memory is fast.
Whenever data is written to or read from a SQL Server database, it will be
copied into memory by the buffer manager. The buffer pool will use as much
memory as is allocated to it in order to hold as many pages of data as
possible. When the buffer cache fills up, older and less used data will be
purged in order to make room for newer data.
Below sql script is use to find
the buffer pages
SELECT
COUNT(*) AS
buffer_cache_pages,
COUNT(*) * 8 / 1024 AS
buffer_cache_used_MB
FROM
sys.dm_os_buffer_descriptors;
|
