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.
| 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 | Percentage of pages found in the cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since SQL Server was started. After a long period of time, the ratio moves very little. Because reading from the cache is less expensive than reading from disk, you want the ratio to be high. The higher this value is, the better. Generally, you can increase the cache hit ratio by increasing the amount of memory available to SQL Server. |
| Cache Object Counts | Number of objects found in the cache. After a long period of time, the count moves very little. |
| Cache Pages | Number of pages used by objects in the cache. After a long period of time, the value moves very little. |
| Cache Use Counts/sec | Number of times per second that each type of object in the cache has been used. The higher this value is, the better. After a long period of time, the count moves very little. |
For more information about caching query plans, see Execution Plan Caching and Reuse.
| Server Memory Options | SQL Server: Buffer Manager Object |