Skip to content

sql-server-plan-cache-object.md

Latest commit

 

History

History
56 lines (43 loc) · 3.34 KB

sql-server-plan-cache-object.md

File metadata and controls

56 lines (43 loc) · 3.34 KB
title SQL Server, Plan Cache object
description Learn about the Plan Cache object, which provides counters to monitor how SQL Server uses memory to store objects such as stored procedures and triggers.
author WilliamDAssafMSFT
ms.author wiassaf
ms.date 07/13/2021
ms.service sql
ms.subservice performance
ms.topic conceptual
helpviewer_keywords
Plan Cache object
SQLServer:Plan Cache

SQL Server, Plan Cache object

[!INCLUDE SQL Server]

The Plan Cache object provides counters to monitor how [!INCLUDEssNoVersion] uses memory to store objects such as stored procedures, ad hoc and prepared [!INCLUDEtsql] statements, and triggers. Multiple instances of the Plan Cache object can be monitored at the same time, with each instance representing a different type of plan to monitor.

This table describes are the SQLServer:Plan Cachecounters.

SQL Server Plan Cache counters Description
Cache Hit Ratio Ratio between cache hits and lookups.
Cache Hit Ratio Base For internal use only.
Cache Object Counts Number of cache objects in the cache.
Cache Objects in use Number of cache objects in use.
Cache Pages Number of 8-kilobyte (KB) pages used by cache objects.

Each counter in the object contains the following instances:

Plan Cache instance Description
_Total Information for all types of cache instances.
SQL Plans Query plans produced from an ad hoc [!INCLUDEtsql] query, including auto-parameterized queries, or from [!INCLUDEtsql] statements prepared using sp_prepare or sp_cursorprepare. [!INCLUDEssNoVersion] caches the plans for ad hoc [!INCLUDEtsql] statements for later reuse if the identical [!INCLUDEtsql] statement is later executed. User-parameterized queries (even if not explicitly prepared) are also monitored as Prepared SQL Plans.
Object Plans Query plans generated by creating a stored procedure, function, or trigger.
Bound Trees Normalized trees for views, rules, computed columns, and check constraints.
Extended Stored Procedures Catalog information for extended stores procedures.
Temporary Tables & Table Variables Cache information related to temporary tables and table variables.

Example

You begin to explore the query performance counters in this object using this T-SQL query on the sys.dm_os_performance_counters dynamic management view:

SELECT * FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Plan Cache%';

See also

Server Memory Server Configuration Options
SQL Server, Buffer Manager Object
Monitor Resource Usage (System Monitor)