Memory Management

SQL Server Buffer pool is an address space of SQL Server which is used to manage client requests.
Below are the data structures allocated in buffer pool. The buffer pool is furthur divided into different into multiple segments.


SQL Server Buffer Cache

SQL Server Buffer cache or data cache, is an area of the Buffer pool. (Memory Pool).
Where the data pages of database reside in the memory is known as Buffer cache.
Buffer cache hit ratio performance counter indicates the performance of Buffer cache. The value 95% indicates availability of the database in the memory, ie., 95% of time datapages are available in the memory. If it show <95% then there might be need of more physical memory for the server.

To determine buffer cache hit ratio

Start – Run – Perfmon – Select SQL Server Buffer Manager – Select Buffer Cache Hit Ratio Counter

Procedure Cache
It contains all execution plans of T-SQL Statements that are executed on the Instance. Procedure Cache is used to cache the query plans. It allows reusing of query plans. We can find more information like size and activity using a DBCC command called DBCC PROCCACHE.
To Clear the procedure cache DBCC FREEPROCCACHE

Log Cache – Reserved for reading and writing log pages.

Connection Context – Each connection to the instance has a small memory area to record current state of the connection. It includes parameters of stored procedures, cursor positioning etc.,

Dirty Page
Any Uncommited information resides in the buffer cache is known as dirty page. In other words the modified data in buffer cache which is not flushed into disk.

Lazywriter
DML Queries like Insert, Update and Delete will always make modifications to data pages in memory.
When a modification happened to a page in memory then that page will marked as “dirty” indicates that there are some changes happened. There are two internal processes of memory management are Lazywriter and CheckPoint. Both are used to scan the buffer cache, The job of lazywriter is to identify dirty page in the disk and writes into disk and drop from the buffer cache. Its responsibility is to keep certain amount of free space in the buffer pool to be used by server.

Checkpoint
The Checkpoint process is scans the buffer cache periodically and writes dirty pages into disk. The difference between lazywriter and checkpoint is it doesn’t keep available buffers in buffer pool in memory pressure. The job of checkpoint is to minimize the number of dirty pages to reduce the length of recovery time if the server crash. A checkpoint will occur after many database actions like detach, attach, or after any DDL statement, or we can issue manually checkpoint.

The frequency of checkpoint process is configure using a server setting “recovery interval” This setting specifies how long a recovery shoult take.