Excessive event service sql transactions

Document ID : KB000076595
Last Modified Date : 06/04/2018
Show Technical Document Details
Issue:
We see this sql statement run very frequently against the database:  exec sp_executesql N'SELECT c0.event_id c0_a27,c0.class_name c0_a9,c0.attribute_name c0_a6,c0.event_type_id c0_a30,c0.provider_id c0_a55,c0.workflow_process_def_name c0_a62,c0.last_update_date c0_a37,c0.tenant c0_a59,c0.days_after c0_a19,c1.event_cause_id c1_a3,c5.criteria_id c5_a3,c5.criteria_type_id c5_a5,c6.value c6_a17,c7.criteria_id c7_a3,c7.criteria_type_id c7_a5,c8.value c8_a17,c2.configuration_id c2_a1,c4.attribute_value c4_a2,c4.class_name c4_a3,c0.version_number c0_a61 FROM dbo.al_event_def

Is this normal behavior and how can we lessen the frequency?
Environment:
CA Asset Portfolio Management 14.x, 17.x
Cause:
This is normal event service behavior. 
The event service has a 'set' of queries that run against the al_event_def table that poll for events on the different objects: Model, Asset, Legal Document, Company, Contact, etc. There is one select query for each of these objects. They typically run within milliseconds of each other.

For example, you may see this query: exec sp_executesql N'SELECT c0.event_id c0_a27,c0.class_name c0_a9,c0.attribute_name c0_a6,c0.event_type_id c0_a30,c0.provider_id c0_a55,c0.workflow_process_def_name c0_a62,c0.last_update_date c0_a37,c0.tenant c0_a59,c0.days_after c0_a19,c1.event_cause_id c1_a3,c5.criteria_id c5_a3,c5.criteria_type_id c5_a5,c6.value c6_a17,c7.criteria_id c7_a3,c7.criteria_type_id c7_a5,c8.value c8_a17,c2.configuration_id c2_a1,c4.attribute_value c4_a2,c4.class_name c4_a3,c0.version_number c0_a61 FROM dbo.al_event_def ... run every few milliseconds.
 
Resolution:
Once the 'set' completes, then it uses the 'Interval between Event Occurrence check (in milli seconds)' time to WAIT before it starts the cycle over again.
To increase the wait time between intervals, set the 'Interval between Event Occurrence check (in milliseconds)', to a higher number under the Administration -> System Configuration / Event Service settings.  After making a change to the event service settings, stop the event service, perform an IISRESET and then restart the event service for the changes to take affect.