The Procedure Cache view allows you to view each item cached, the size, and the SQL text associated. Graphs below the cache listing provide you with a breakdown of the key Procedure Cache statistics that can help you analyze performance bottlenecks on your SQL Server instance.
The Usage column displays the number of times the associated cache object is used since inception. The References column displays the number of other cache objects referencing the associated cache object, with "1" as the base.
Access the Procedure Cache view
You can open the Procedure Cache view of the SQL Diagnostic Manager Resources tab by selecting the appropriate SQL Server instance, and then clicking Resources > Procedure Cache.
Procedure Cache tab charts
The following charts are available on the Procedure Cache tab:
Cache Size by Object Type
Displays the amount of the procedure cache used by various objects including:
- Adhoc Query
- Check Constraint
- Default Constraint
- Extended Procedure
- Prepared Statement
- Replication Procedure
- Rule
- Stored Procedure
- System Table
- Trigger
- User Table
- View
Object Types
- Use the drop-down button to select either Hit Ratio, Object Count, or Use Rate.
- Hit Ratio offers a chart of the cache hit ratios of the individual object types in memory through the last 25 refreshes. For example, you can view the hit ratio of the extended procedure object.
- Object Count displays the number of objects using the procedure cache. Use Rate displays the rate at which each of the object types is used in the procedure cache.