SQL SERVER – Data Pages in Buffer Pool – Data Stored in Memory Cache

This will drop all the clean buffers so we will be able to start again from there. Now, run the following script and check the execution plan of the query.

Have you ever wondered what types of data are there in your cache? During SQL Server Trainings, I am usually asked if there is any way one can know how much data in a table is stored in the memory cache? The more detailed question I usually get is if there are multiple indexes on table (and used in a query), were the data of the single table stored multiple times in the memory cache or only for a single time?

Here is a query you can run to figure out what kind of data is stored in the cache.

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

Now let us run the query above and observe the output of the same.

Solarwinds

SQL SERVER - Data Pages in Buffer Pool - Data Stored in Memory Cache pagecached

We can see in the above query that there are four columns.

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

Now, let us do one more experience here. Please note that you should not run this test on a production server as it can extremely reduce the performance of the database.

DBCC DROPCLEANBUFFERS

This will drop all the clean buffers and we will be able to start again from there. Now run following script and check the execution plan for the same.
USE AdventureWorks
GO
SELECT UnitPrice, ModifiedDate
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID BETWEEN 1 AND 100
GO

The execution plans contain the usage of two different indexes.

SQL SERVER - Data Pages in Buffer Pool - Data Stored in Memory Cache pagecached1

Now, let us run the script that checks the pages cached in SQL Server. It will give us the following output.

SQL SERVER - Data Pages in Buffer Pool - Data Stored in Memory Cache pagecached2

It is clear from the Resultset that when more than one index is used, datapages related to both or all of the indexes are stored in Memory Cache separately.

Let me know what you think of this article. I had a great pleasure while writing this article because I was able to write on this subject, which I like the most. In the next article, we will exactly see what data are cached and those that are not cached, using a few undocumented commands.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Find Largest Supported DML Operation – Question to You
Next Post
SQLAuthority News – Interview with SQL Server MVP Madhivanan – A Real Problem Solver

Related Posts

11 Comments. Leave new

  • Thanks for this post. I wondered about this a long time ago but I finally find the answer from you.

    Reply
  • Hi

    iam new to DBA…and i want to know if a user make a transation whether data first reside in the buffer pool or T-log file……….

    as per my knowledge it first reside in the buffer pool and lazy writer scans the buffer pool,if it finds the dirty pages it will forces the dirty pages to t-log and phsyical data file i.e disk…………is iam right?

    if iam wrong plz let me the process of data storage.

    Reply
  • Neeraj Verma
    July 8, 2010 12:27 pm

    hi
    Nice articles, thank for this post, but I am waiting your next article where you will show “what data are cached and those that are not cached”

    Reply
  • Hey Pinal,

    This is good script which is useful for me.

    Thanks,

    Reply
  • @neeraj What exactly you are looking is

    you want all data in cache is that possible? think logically man,

    If so then it has copy again into the cache to display it to you

    thats burden .

    You need to run this script check any queries related to result are running if they causing problem.

    Thanks,

    Reply
  • If an index shows up in the results of that query, are all pages from the index always contained in the buffer cache, or might there only be a fraction?

    Reply
  • Nice one Pinal – this is exactly the scrpt I was looking for to try and sort out some memory issues

    Reply
  • Chiranjib Saha Roy
    July 18, 2012 5:02 pm

    Hi,
    You are very interesting. I have learnt a lot from you… thanks for providing such kind of useful article with example. I have one query(question). Is SQL Server hold such kind of technology like soft parse (included in Oracle). Waiting for your reply. Thanks in advance.

    Reply
  • I have one system running two instances of 2008 R2. I want to dedicate 8GB of memory to the buffer pool on one of the instances. Is there a way to set this in SSMS? via a script? Thanks in advance…

    Reply
    • Hi Mickey, try this query on the instance you want to change,

      SP_CONFIGURE ‘SHOW ADVANCED OPTION’,1
      RECONFIGURE
      SP_CONFIGURE ‘min server memory (MB)’, 8192
      RECONFIGURE

      ..check this if it helps and reply me,

      Reply
  • Wallace Houston
    December 19, 2013 2:04 am

    Pinal, I have run the (1st) query and see the listing. As expected the most-used table in our database is at the top. The cached pages count is 295,436. How do I know whether that’s good or bad? It is a clustered index.

    Reply

Leave a Reply

Menu