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.
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:
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.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
.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. |
SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >
|