The Procedure Cache Hit Ratio (Percent) alert provides the ratio of procedure cache hits to procedure cache lookups.This value indicates the reuse of an execution plan from memory as opposed to a compilation from disk.

Manage your Procedure Cache Hit Ratio

If you regularly encounter values below 80% once SQL Server is actively running for at least 30 minutes, then consider one of the following remedies:

  • Make sure that all T-SQL statements (whether in a stored procedure, trigger or ad hoc statement) that reference objects fully qualify the object referenced. For example: SELECT * FROM Northwind.dbo Employees is a fully-qualified object reference whereas SELECT * FROM Employees is a poorly-qualified object. You can reuse the execution plans of fully-qualified objects "as is," whereas plans where you either cannot reuse the not fully-qualified objects or, if they are reused, then they are subject to a highly restrictive COMPILE lock while SQL Server determines if all of the objects referenced in the T-SQL code have the same owners as the execution plan currently in cache. Both of these situations consume a significant amount of CPU time.
  • Add more physical memory (RAM) to the computer.
  • If your site makes use of extended stored procedures that are not called very often, then after calling them, issue a DBCC DLLNAME (FREE). Once an extended stored procedure is called, it remains in memory until SQL Server is shut down even if it is never called again! This is tremendously wasteful in terms of available memory and a DBCC DLLNAME (FREE) releases that memory for use by both the procedure cache as well as the Data Pages which has a significant positive effect on both the Procedure Cache Hit Ratio, as well as the Buffer Cache Hit Ratio. In order to view the extended stored procedures currently loaded in memory by SQL Server, execute SP_HELPEXTENDEDPROC.
  • Allow SQL Server to consume more of the available memory (making sure that OS Paging does not increase).
  • Stop unnecessary programs (such as the Print Spooler or activities such as the database server acting as either a BDC or PDC) from running.
  • If the computer is running multiple instances of SQL Server, consider placing each instance on a separate physical computer.

To enable alerting when this metric is outside its established baseline, click the Baseline Thresholds Enabled (as percentage of baseline) check box in the Alert Configuration window.



IDERA | Products | Purchase | Support | Community | Resources | About Us | Legal