Have you ever wondered how SQL Server stores data in its memory cache? During SQL Server trainings, I often get asked if there is a way to know how much data from a table is stored in the memory cache. Additionally, people often inquire about the storage of data from tables with multiple indexes. Are the data pages stored multiple times in the memory cache or just once?

In this article, we will explore a query that can help us understand what kind of data is stored in the cache. Let’s dive in!

USE AdventureWorks
GO

SELECT COUNT(*) AS cached_pages_count, 
       name AS BaseTableName, 
       IndexName, 
       IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN (
    SELECT s_obj.name, 
           s_obj.index_id, 
           s_obj.allocation_unit_id, 
           s_obj.OBJECT_ID, 
           i.name IndexName, 
           i.type_desc IndexTypeDesc
    FROM (
        SELECT OBJECT_NAME(OBJECT_ID) AS name, 
               index_id, 
               allocation_unit_id, 
               OBJECT_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_NAME(OBJECT_ID) AS name, 
               index_id, 
               allocation_unit_id, 
               OBJECT_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 s_obj
    LEFT JOIN sys.indexes i ON i.index_id = s_obj.index_id AND i.OBJECT_ID = s_obj.OBJECT_ID
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY cached_pages_count DESC;
GO

When we run the above query, we can observe the output which consists of four columns:

• Cached_Pages_Count: This column lists the number of pages cached in the memory.
• BaseTableName: This column lists the original base table from which the data pages are cached.
• IndexName: This column lists the name of the index from which the pages are cached.
• IndexTypeDesc: This column lists the type of index.

Now, let’s perform another experiment. Please note that you should not run this test on a production server as it can significantly reduce the performance of the database.

DBCC DROPCLEANBUFFERS

The above command will drop all the clean buffers, allowing us to start fresh. Now, let’s run the following script and check the execution plan:

USE AdventureWorks
GO

SELECT UnitPrice, ModifiedDate
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID BETWEEN 1 AND 100
GO

When we examine the execution plans, we can see the usage of two different indexes. Now, let’s run the script that checks the pages cached in SQL Server. It will give us the following output:

It is clear from the resultset that when more than one index is used, data pages related to both or all of the indexes are stored in the memory cache separately.

I hope you found this article helpful in understanding how SQL Server stores data in its memory cache. In the next article, we will explore what data is cached and what is not cached using a few undocumented commands.

Let me know your thoughts on this article. I thoroughly enjoyed writing about this subject, as it is one of my favorites.