The Plan Cache object provides counters to monitor how SQL Server uses memory to store objects such as stored procedures, ad hoc and prepared Transact-SQL 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 Transact-SQL query, including auto-parameterized queries, or from Transact-SQL statements prepared using sp_prepare or sp_cursorprepare. SQL Server caches the plans for ad hoc Transact-SQL statements for later reuse if the identical Transact-SQL 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.|