When SQL Server reads pages it stores them in an area of memory called the buffer cache, things like memory pressure can then cause items to get removed from the buffer cache. I wrote the below script to check what’s in the cache at any given time, it’s scoped to the database you are running it in…

SELECT 
    o.name [Table],
    i.name [Index],
    (COUNT(*) *8)/1024 [SizeMB]
FROM
    sys.allocation_units au 
    INNER JOIN sys.dm_os_buffer_descriptors bd ON au.allocation_unit_id = bd.allocation_unit_id
    INNER JOIN sys.partitions p ON  
        /* allocation_unit types : 
            0=Dropped, 
            1 = In Row, 
            2=LOB, 
            3=Row-Overflow data*/
        (au.[type] IN (1,3) AND au.container_id = p.hobt_id) 
        OR (au.[type] = 2 AND au.container_id = p.partition_id)
    INNER JOIN sys.objects o ON p.object_id = o.object_id
    LEFT JOIN sys.indexes i ON i.object_id = o.object_id AND p.index_id = i.index_id
 WHERE 
    o.type NOT IN ('S','IT') 
GROUP BY
    i.name,
    o.name

Backup Status Results

I find this really useful when looking at things like buffer hit ratios to then get an idea as to what in the buffer cache could be causing memory pressure.