Memory - Procedure Cache Hit Rate Alarm
The Memory - Procedure Cache Hit Rate alarm is raised when the ratio between the number of times SQL Server looks for a plan in the Procedure Cache, and the number of times it does not find a required plan in the Procedure Cache, exceeds a threshold.
A low Procedure Cache hit rate indicates that SQL Server is finding fewer of the query execution plans it needs already in memory, and therefore has to perform more compiles. These extra compilations will degrade SQL Server performance by causing extra CPU load.
To prevent this alarm being caused by adhoc SQL requests (which often produce non-reusable execution plans), Spotlight on SQL Server removes adhoc plan statistics from this alarm.
When this alarm is current, you should:
- Check the Call Rates chart on the Summary page of the SQL Activity drilldown for a high number of Re-Compiles. Follow the suggestions listed under the Compiles - Percentage Recompilations alarm. See "Compiles - Percentage Recompilations Alarm" (page 1) for more information.
- On the Memory drilldown, select the Procedure Cache tab and then use the Hit Rate and Use Rate counters on the Object Types chart to identify which types of objects are causing the problem.
- Ensure SQL Server is configured to use as much physical memory as possible, as this alarm can be caused by an insufficient amount of memory being available for SQL Server to use. If your SQL Server Max Memory parameter setting has been changed from the default of 0, consider increasing the value, or setting it back to 0. (You can check this setting on the Configuration drilldown).
- Consider increasing your SQL Server Min Memory parameter setting so that SQL Server gets more memory. (You can check this setting on the Configuration drilldown). If you have applications other than SQL Server running on this machine, and the Total Memory gauge on the home page shows that SQL Server is not using all the memory it could, then these applications could be taking memory away from SQL Server.
- Consider adding more physical RAM to the server.
- Use SQL Profiler to trace Cache Insert events to see what stored procedures are being compiled.
- View the Procedure Cache page on the Memory drilldown to see the list of objects in the cache.
Spotlight on SQL Server calculates its hit rates using a differential sampling method. The hit rate shown is for the last few sample periods only. Unlike most SQL Server monitors, it is not reporting the average hit rate since starting the SQL Server instance.
Related Topics
Spotlight on SQL Server Alarms (page 1)
SQL Activity Drilldown (page 1)
Memory Drilldown (page 1)
Configuration Drilldown (page 1)
