The Buffer Cache Hit Ratio (Percent) metric calculates the percentage of data pages SQL Server found in memory without reading the data from the disk subsystem. This value is measured by reading the 'buffer cache hit ratio' from sysperfinfo
. The higher this value is the more efficiently the system is running.
Once SQL Server is up for at least 30 minutes, use the following solutions if values lower than 85% regularly occur:
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 again called, which then wastes available memory. A DBCC dllname (FREE)
releases that memory for use by both the procedure cache and Data Pages, which has a significant positive effect on both the Procedure Cache Hit Ratio and the Buffer Cache Hit Ratio. Execute sp_helpextendedproc
to view the extended stored procedures currently loaded in memory by SQL Server.SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >
|