Support Article
OOTB query generating alerts
SA-3226
Summary
Query:
select pzEntryKey as pzEntryKey, pzEntryType as pzEntryType, pzExpirationTime as pzExpirationTime, pzPVStream as pzPVStream from pr_sys_cache_entry where pzInsKey in (?, ?, ?)
Observation:
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.
------------------------
WAS 7.0.0.27
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
------------------------
attached
- pegarules
- alert
- inventory
Error Messages
DB Alert: DB-394
Steps to Reproduce
Further info:
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.
Root Cause
The root cause of this problem is defect/misconfiguration in the PRPC operating environment. Issue seems to be related to overall hardware performance.
Resolution
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
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.