SQL SERVER – Plan Cache and Data Cache in Memory

I get following question almost all the time when I go for consultations or training. I often end up providing the scripts to my clients and attendees. Instead of writing new blog post, today in this single blog post, I am going to cover both the script and going to link to original blog posts where I have mentioned about this blog post. It is about what sits in the plan cache and data cache in memory.

Plan Cache and Data Cache, Part 1: Plan Cache in Memory

USE AdventureWorks
GO
SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
ORDER BY cp.size_in_bytes DESC
GO

Further explanation of this script is over here: SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script

Data Cache in Memory

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

Further explanation of this script, which completes the plan cache and data cache picture, is over here: SQL SERVER – Get Query Plan Along with Query Text and Execution Count

What These Two Caches Hold

A quick note on what these scripts show you. The plan cache keeps compiled execution plans, so SQL Server does not have to compile the same query again and again. The data cache, also called the buffer pool, keeps 8 KB data pages that were read from disk, so the next read comes from memory instead. When I look at both caches together, I get a good picture of where memory is going on a server.

SELECT DB_NAME(database_id) AS DatabaseName,
       COUNT(*) * 8 / 1024 AS CachedMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY CachedMB DESC;

One caution. On a server with a lot of memory, sys.dm_os_buffer_descriptors returns one row for every page in the buffer pool, so the data cache script can take a while and use CPU. Run it at a quiet time, never in a loop, and save the output when you want to compare before and after a change. If you only want a summary, the short query above shows how much of the data cache each database uses, in megabytes.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.