SQL Server: Cache Manager Object
The Cache Manager object provides counters to monitor how Microsoft® SQL Server™ uses memory to store objects such as stored procedures, ad hoc and prepared Transact-SQL statements, and triggers. Multiple instances of the Cache Manager object can be monitored at the same time, with each instance representing a different type of plan to monitor.
| Cache Manager instance | Description |
|---|---|
| Ad hoc SQL Plans | Query plans produced from an ad hoc Transact-SQL query, including auto-parameterized queries. SQL Server caches the plans for ad hoc SQL statements for later reuse if the identical Transact-SQL statement is later executed. |
| Misc. Normalized Trees | Normalized trees for views, rules, computed columns, and check constraints. |
| Prepared SQL Plans | Query plans that correspond to Transact-SQL statements prepared using sp_prepare, sp_cursorprepare, or auto-parameterization. User-parameterized queries (even if not explicitly prepared) are also monitored as Prepared SQL Plans. |
| Procedure Plans | Query plans generated by creating a stored procedure. |
| Replication Procedure Plans | Query plans of a replication system stored procedure. |
| Trigger Plans | Query plans generated by creating a trigger. |
These are the SQL Server Cache Manager counters.
| SQL Server Cache Manager counters | Description |
|---|---|
| Cache Hit Ratio | Ratio between cache hits and lookups. |
| Cache Object Counts | Number of cache objects in the cache. |
| Cache Pages | Number of 8-kilobyte (KB) pages used by cache objects. |
| Cache Use Counts/sec | Times each type of cache object has been used. |
For more information about caching query plans, see Execution Plan Caching and Reuse.