Cache
This widget contains monitors specific to the current status of the cache. The icons of the listed performance counters in the Statistic Name column change colors to reflect the listed counter's current state. Clicking any performance counter in the Statistic Name column takes you to the Performance Counter Details page for that performance counter.
If the value of a listed performance counter crosses the Warning threshold, the chart for the listed counter displays a yellow background. If the value of a listed monitor crosses the Critical threshold, the chart for the listed counter displays a red background. Hovering over any time period within the chart displays detailed information about that time period in the tooltip. Hovering over a counter gives you detailed information about that performance counter.
| Counter | Expert Knowledge |
|---|---|
|
Cache Used/min |
This displays the number of times the cache object was looked up. This is not incremented when parameterized queries find a plan in the cache. However, this can be incremented multiple times when using Showplan. Good plan reuse is one sign of a healthy system. Compiling a query plan can be a CPU-intensive operation, especially with complex queries, so reuse is encouraged. Potential Issue: If the returned value is low, this can indicate ineffective reusing of plan cache which could cause a CPU spike. Conversely, if the returned value is high for a large number of plans, this could indicate that your cache space is being taken up with plans that were compiled and used once to execute an ad hoc query, then never used again. Resolution: A database setting that can be helpful to plan reuse is forced parameterizaton. You can set this using the following command: |
|
Cache Object Counts |
This performance counter returns the number of cache objects in the cache. 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. Potential Issue: High numbers of total cached objects use portions of the physical memory available to a SQL instance on a per database basis. This can result in one database cache impacting the performance of other local databases due to memory contention. Resolution: Increase the memory available to SQL services, reduce the number of databases on this instance of SQL, or examine the volume of ad hoc queries running against the server. |
|
Cache Objects in Use |
This performance counter returns number of cache objects in use. 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. Potential Issue: High numbers of cached objects in use consume the memory available to a SQL server at a higher rate than non-active objects on a per database basis. This can result in one database cache impacting the performance of other local databases due to memory contention. Resolution: Increase the memory available to SQL server, reduce the number of active objects, consolidate stored procedures, consolidate and convert ad hoc queries to stored procedures where possible, or reduce the number of databases on the server. |
Zooming
You can have the chart show a predetermined time period of data by clicking on any one of the three Zoom buttons in the head of the Zoom column. Alternatively, you can have the chart show a specific date range by dragging the mouse over an area of the chart. The time and date displayed at the top of the widget shows the amount of data currently loaded into the chart. This can be changed by clicking Edit from within the widget.
The Value from Last Poll column shows the most recent data available for the listed statistic.