1.3.2 Insufficient data cache
The insufficient data cache affects the performance of the Microsoft SQL Server. To monitor the insufficient data cache, use the Cache Usage alarm in the monitoring template which PFM - Agent for Microsoft SQL Server provides. The Cache Usage alarm enables you to monitor how many times the cache hits occur instead of loading data from the storage disk
The following table explains the Cache Usage alarm in the monitoring template:
Alarm name |
Record used |
Field used |
Abnormal condition |
Warning condition |
Values measured |
---|---|---|---|---|---|
Cache Usage |
PI_SERV |
Cache Hit % |
Cache Hit % < 85 |
Cache Hit % < 95 |
The data cache can be insufficient. |
- Diagnosis:
-
If the value of Cache Hit % field is lower than 85 %, the data cache can be insufficient.
The insufficient data cache lowers the performance of the search and update process.
- Prescription:
-
Either increase the value of the max server memory option or add the physical memory to the system so that the size of the data cache increases.
For details about the Cache Usage alarm, see Cache Usage in 4. Monitoring Template.