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.

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)

SQL Memory, SQL Scripts
Previous Post
SQL SERVER – ORDER BY ColumnName vs ORDER BY ColumnNumber
Next Post
SQLAuthority News – My Evaluation of Singapore SharePoint Conference

Related Posts

5 Comments. Leave new

  • devender bijania
    December 29, 2010 2:32 pm

    can we add mention a column in this list which define the space size which is occupied in memory??

    Reply
  • Hi Pinal,
    Thanks for the g8 info.
    Can we configure the data cache and how?

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

    Reply
  • HI Pinal,
    is it bad if cached_pages_count is higher for a particular table all the time? Thanks.

    Reply

Leave a Reply