| title | SQL Server, Plan Cache Object | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/14/2017 | ||
| ms.prod | sql | ||
| ms.prod_service | database-engine | ||
| ms.reviewer | |||
| ms.technology | performance | ||
| ms.topic | conceptual | ||
| helpviewer_keywords |
|
||
| ms.assetid | 225e2b02-8d2f-4f29-9eba-f5847c36ea99 | ||
| author | julieMSFT | ||
| ms.author | jrasnick |
[!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 Pages | Number of 8-kilobyte (KB) pages used by cache objects. |
| Cache Objects in use | Number of cache objects in use. |
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. |
Server Memory Server Configuration Options
SQL Server, Buffer Manager Object
Monitor Resource Usage (System Monitor)