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.

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.

Now, let us 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, 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 (http://blog.SQLAuthority.com)

12 thoughts on “SQL SERVER – Data Pages in Buffer Pool – Data Stored in Memory Cache

  1. 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.

    Like

  2. 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”

    Like

  3. @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,

    Like

  4. 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.

    Like

  5. 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…

    Like

    • 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,

      Like

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

  7. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s