Buffer Cache Hit Ratio (Percent)

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.

Reduce the percentage of memory used by SQL Server

Once SQL Server is up for at least 30 minutes, use the following solutions if values lower than 85% regularly occur:

• Add more physical memory (RAM) to the computer.
• If your site makes use of extended stored procedures that are infrequently called, 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 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.
• Allowing SQL Server to consume more of the available memory, making sure that OS Paging does not increase.
• Limit SQL Server computers to performing only SQL Server work. Stop any unnecessary programs, such as allowing the computer to act as either a primary or backup domain controller.
• If the computer is running multiple instances of SQL Server, then consider placing each instance on a separate physical computer.