OOTB query generating alerts
select pzEntryKey as pzEntryKey, pzEntryType as pzEntryType, pzExpirationTime as pzExpirationTime, pzPVStream as pzPVStream from pr_sys_cache_entry where pzInsKey in (?, ?, ?)
Analysis of the query indicates that, although it appears to be optimally written, it still runs above the execution time threshold, which may be impacting overall application performance.
MS SQL Server 11.0.3000.0 64bit
row count for the pr_sys_cache_entry table - 1,039,640
Indexes on pr_sys_cache_entry
- pr_sys_cache_entry_PK (Clustered)
- cache_entry_imp (Non-Unique, Non-Clustered)
No Contraints defined
DB Alert: DB-394
Steps to Reproduce
1. It appears that the OOTB query execution time is greater than 500 ms, the default KPI value.
2. It is being used by all the application generating alert regular basis.
3. Average KPI: 0 hr 0 min 0.89 sec.
The root cause of this problem is defect/misconfiguration in the PRPC operating environment. Issue seems to be related to overall hardware performance.
This issue is resolved through the following local change: Customer upgraded some hardware and the frequency of this alert dropped off. Only 2 in the last alert log.
Two items where upgraded the SAN bandwidth was increased and the memory on the SQL server