As a SQL Server professional, you may often come across questions related to plan and data cache. In this blog post, we will discuss these concepts and provide you with useful scripts to retrieve and manage them.

Plan Cache in Memory

The plan cache in SQL Server is a memory area that stores the compiled execution plans for queries. These plans are reused to improve query performance. To retrieve the plan cache information, you can use the following script:

USE AdventureWorks;
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;

This script will provide you with the size in bytes and the plan handle for each compiled plan in the cache. You can further explore the details of this script in the blog post “SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script”.

Data Cache in Memory

The data cache in SQL Server is a memory area that stores the pages of data from database tables and indexes. These pages are cached to improve data access performance. To retrieve the data cache information, you can use the following script:

USE AdventureWorks;
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 AS IndexName, i.type_desc AS 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;

This script will provide you with the count of cached pages, base table name, index name, and index type description. You can find a detailed explanation of this script in the blog post “SQL SERVER – Get Query Plan Along with Query Text and Execution Count”.

Understanding the plan and data cache in SQL Server is crucial for optimizing query performance. By utilizing these scripts, you can retrieve valuable information about the cached plans and data pages, allowing you to make informed decisions for query tuning and optimization.