| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | ||
|---|---|---|---|---|---|---|---|---|---|---|
SQL Server, Plan Cache object |
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. |
MikeRayMSFT |
mikeray |
12/04/2023 |
sql |
performance |
reference |
|
[!INCLUDE SQL Server]
The Plan Cache object provides counters to monitor how [!INCLUDE ssNoVersion] uses memory to store objects such as stored procedures, ad hoc and prepared [!INCLUDE tsql] 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 [!INCLUDE tsql] query, including auto-parameterized queries, or from [!INCLUDE tsql] statements prepared using sp_prepare or sp_cursorprepare. [!INCLUDE ssNoVersion] caches the plans for ad hoc [!INCLUDE tsql] statements for later reuse if the identical [!INCLUDE tsql] 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. |
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%';