Use this Knowledge Script to monitor the percentage at which a requested data page is retrieved from the SQL Server data cache without performing physical reads from disk. This script raises an event if the cache hit ratio falls below the threshold you specify.
SQL Server instance
The default interval for this script is Once every hour.
Set the following parameters as needed:
|
Description |
How to Set It |
|---|---|
|
General Settings |
|
|
Job Failure Notification |
|
|
Raise event if job fails unexpectedly? |
Select Yes to raise an event if the SQLServer_CacheHitRatio job fails unexpectedly. The default is Yes. |
|
Event severity when job fails unexpectedly |
Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the job fails unexpectedly. The default is 5. |
|
Additional Settings |
|
|
Event Details |
|
|
Event detail format |
Select the format in which you want to display the event detail. You can select from HTML Table or Plain Text. The default is HTML Table. |
|
Monitor Cache Hit Ratio |
|
|
Event Notification |
|
|
Raise event if cache hit ratio is below threshold? |
Select Yes to raise an event when the cache hit ratio falls below the threshold. The default is Yes. |
|
Event severity if cache hit ratio is below threshold |
Set the event severity level, from 1 to 40, to indicate the importance of an event in which the cache hit ratio falls below the threshold. The default is 5. |
|
Threshold -- Minimum cache hit ratio |
Specify the minimum percentage that requested data pages can be retrieved in the data cache before an event is raised. Ideally this percentage should be set relatively high, because the more frequently SQL Server uses the data cache, the better your database performance. When SQL Server accesses information in the data cache less frequently than the threshold you specify, for example only 50% of the time, an event informs you that database performance has deteriorated. The default is 90%. |
|
Data Collection |
|
|
Collect data for cache hit ratio? |
Select Yes to collect data for charts and reports. If enabled, data collection returns the cache hit percentage. The default is unselected. |
|
Custom data stream legend |
Specify a custom data stream legend to append with the default data legend for the job that is visible in the console. You can specify a maximum of 128 alphanumeric characters in a string, including special characters. The default is none. |