The Memory - Buffer Cache Hit Rate alarm becomes active when the ratio of physical reads to logical reads falls below a threshold.
A low Buffer Cache hit rate indicates that SQL Server is finding fewer pages already in memory, and therefore has to perform more disk reads. This is often caused by one of two possibilities: SQL Server has insufficient memory to work with, or SQL queries are accessing a very large number of pages in a non-sequential manner. The best figure will vary from one application to another, but ideally this ratio should be above 90%.
When this alarm occurs, you should:
If you have applications other than SQL Server running on this machine, and the Total memory gauge on the main Spotlight window shows that SQL Server is not using all the memory it could, then these applications could be taking memory away from SQL Server. 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).
Tip: If these parameters are not displayed in the SQL Server Configuration grid and you are connected to a SQL Server 2005 instance or later, turn the Show Advanced Options parameter on. You can do this in the Configuration grid.
Spotlight 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, Spotlight on SQL Server is not reporting the average hit rate since the SQL Server instance started.
Spotlight on SQL Server Alarms (page 1)
