Cache Hit Ratio (CacheHitRatio)
- Displays the percentage of requests for data that the Microsoft SQL Server can satisfy from memory (data cache) rather than reading the data from a disk.
- PATROL for Microsoft SQL Server uses the SQL Server:Buffer Manager > Bufer Cache Hit Ratio performance counter to calculate the value of this parameter.
Recommendations
- Because reading from cache requires fewer resources than reading from disk, a high cache hit ratio is preferable. The higher this value is, the better. Generally, you can increase the cache hit ratio by increasing the amount of memory available to SQL Server and by rewriting queries that perform a lot of physical input and output.
- If you have your SQL Server set to use memory dynamically, it should automatically grow or shrink the buffer cache as necessary to prevent paging. If the CacheHitRatio parameter goes into alarm and you have the SQL Server set to use memory dynamically, you may want to check your maximum setting. Keep in mind, however, that your maximum setting should leave enough memory for the operation of all other programs running on that computer.
Recovery actions
- When the CacheHitRatio parameter goes into an alarm state, it generates a pop-up window that contains information about the processes that are currently performing the highest volume of physical I/O.
- Other recovery actions for this parameter are controlled by the settings in the ARA Default Actions dialog box. By default, whenever this parameter crosses a warning or an alarm threshold, the ARA (automatic recovery action) feature does the following:
- Executes the ara_MSSQL.cmd script (if one exists). See Setting up the ara_MSSQL command for instructions on how to create a recovery action in the ara_MSSQL.cmd file.
- Creates an output file in the PATROL_TEMP directory .
- Annotates the parameter warning or alarm.
- Triggers an event in the PATROL Event Manager.
To turn off these automatic recovery actions or to add other actions, see Customizing automatic recovery actions.
Default parameter attributes
Attribute | Default value |
BMC PATROL properties | |
Application class | |
Command type | PSL |
Platform | Microsoft Windows |
Icon style | graph |
Unit | percentage of requests for data |
Border range | <0, >100 alarm |
Alarm1 range | 0 to 50, alarm after two times |
Alarm2 range | 50 to 70, warn after two times |
Scheduling (poll time) | not applicable |
Active at installation | yes |
Parameter type | consumer |
Value set by | |
BMC ProactiveNet Performance Management properties | |
Monitor type | SQL Server Performance |
Key Performance Indicator | Yes |
Monitor for abnormalities | No |
Graph by default | Yes |
Availability | No |
Response time | No |
Normal distribution | Yes |
Statistical | Yes |
Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*