TechNet
Export (0) Print
Expand All

SQL Server, Plan Cache Object

 

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 countersDescription
Cache Hit RatioRatio between cache hits and lookups.
Cache Object CountsNumber of cache objects in the cache.
Cache PagesNumber of 8-kilobyte (KB) pages used by cache objects.
Cache Objects in useNumber of cache objects in use.

Each counter in the object contains the following instances:

Plan Cache instanceDescription
_TotalInformation for all types of cache instances.
Sql PlansQuery 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 PlansQuery plans generated by creating a stored procedure, function, or trigger.
Bound TreesNormalized trees for views, rules, computed columns, and check constraints.
Extended Stored ProceduresCatalog information for extended stores procedures.
Temporary Tables & Table VariablesCache information related to temporary tables and table variables.

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

Show:
© 2016 Microsoft