You can find whats stored in the buffer pool (data cache) using below query :
From here :
select
count(*)as cached_pages_count,
obj.name as objectname,
ind.name as indexname,
obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
inner join
(
select object_id as objectid,
object_name(object_id) as name,
index_id,allocation_unit_id
from sys.allocation_units as au
inner join sys.partitions as p
on au.container_id = p.hobt_id
and (au.type = 1 or au.type = 3)
union all
select object_id as objectid,
object_name(object_id) as name,
index_id,allocation_unit_id
from sys.allocation_units as au
inner join sys.partitions as p
on au.container_id = p.partition_id
and au.type = 2
) as obj
on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind
on obj.objectid = ind.object_id
and obj.index_id = ind.index_id
where bd.database_id = db_id()
and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc
Excellent reference : Inside the Storage Engine: What’s in the buffer pool? by Paul Randal.
How, in future, SQL determine if page, which Storage Engine asks, is already in memory?
To balance CPU and I/O throughput to achieve good performance and maximize hardware utilization, SQL Server includes 2 asynchronous I/O mechanisms - sequential read ahead and random prefetching
When SQL Server has to scan a large table, the storage engine will initiate the read ahead mechanism to ensure that pages are in memory and they are ready to scan before they are needed by the Query Optimizer.
If you are using Enterprise edition, then there is a mechanism called advanced scanning or merry-go-round scanning that will allow multiple tasks to share full table scans. This advance scanning avoids users to compete for buffer space and taxing other resources on the server.
And which algorithm it uses to keep some free space for new pages?
SQL Server (and Sybase as well :-) ) uses LRU (Least Recently Used) algorithm to keep track of pages that are aged in the buffer pool. It increments a counter every time a page is referenced and decrements the counter every time the lazy writer process sweeps the page.
Any worker thread will check the memory status of Buffer Pool to make sure that there are healthy number of Free pages to honor incoming new requests.
Note that : SQL Server will always keep a minimum number of free pages on the free list, so that it can serve incoming requests without any significant delay. The amount of free space is calculated based on Buffer Pool and amount of incoming requests.
IF there are no free buffers or very little left then :
- If Max Memory is reached then
- If Max Memory is not reached then
- SQL Server will commit more reserved pages into the buffer pool.
You can use DMV (in 2005 and up) - sys.dm_os_buffer_descriptors to find the pages in the buffer pool.
Also, there are Memory Manager surface area changes in SQL Server 2012.
KB 2663912 :
Starting with SQL Server 2012, Multi-Page allocations and CLR allocations are also included in memory limits that are controlled by max server memory (MB) and min server memory (MB). This change provides a more accurate sizing ability for all memory requirements that go through the SQL Server memory manager. Carefully review your current max server memory (MB) and min server memory (MB) values after you upgrade to SQL Server 2012.
Highly recommend to read :
Best Answer
When data is in the buffer pool it is compressed. The data within the row/column needs to be decompressed when the row/column is read. The storage engine team (the guys that wrote this stuff) did a blog post about this which has some good info in it. (Yes I stole the above line from the blog post.)