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.
Plan Cache in Memory
USE AdventureWorks
GO
SELECT , 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 is over here: SQL SERVER – Get Query Plan Along with Query Text and Execution Count
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
can we add mention a column in this list which define the space size which is occupied in memory??
Hi Pinal,
Thanks for the g8 info.
Can we configure the data cache and how?
hello pinal,
iam getting an error sql time out servernot responding ,with one of branch database, application are same in all branches , so i think it might be the problem with the data base.
HI Pinal,
is it bad if cached_pages_count is higher for a particular table all the time? Thanks.
No. Not really.