OOTB BR "Update Standard Change Version stats" impacting database performance - Known Error

OOTB BR "Update Standard Change Version stats" impacting database performance Description After bulk closing a large number of Change Requests (thousands) it is likely that a spike in database response times will be experienced along with delays in processing of background/scheduled jobs.

Checking the "sys_trigger" table will show a large number of records with the name "ASYNC: Update Standard Change Version st" and type "Run Once".

Steps to Reproduce

This is a difficult one to reproduce as to slow down the query that is being executed many times you need a high volume of standard Change Requests but the issue can be demonstrated with the steps below:

1. On an OOB instance create a Standard Change via the Standard Change Catalog module and take a note of the Change Request number 2. In a new browser tab go to table "sys_trigger" and filter the list where name starts with "ASYNC: Update Standard Change" 3. In another new tab, run the following background script replacing with the appropriate value:

var chgGr = new GlideRecord("change_request"); chgGr.get("number", " ");

for (var i = 0; i chgGr.setValue("close_code", chgGr.getValue("close_code") === "successful" ? "unsuccessful" : "successful"); chgGr.update(); }

4. Quickly switch to the tab showing the list of "sys_trigger" records and refresh the list - you will see a number of jobs related to the updates performed on the Change Request and continuing to refresh the list will see the number decrease until all jobs have been processed

Workaround The workaround is to replace the existing business rules Update Standard Change Version stats and Update Standard Version stats on delete with a daily job that will provide the same updates to the statistics fields on the Standard Change Templates and their associated versions but on a defined schedule instead of every time a standard Change Request with a close code is updated.

Steps to do this are:

Download the attached file Update set containing workaround for PRB1553239.xml In the target instance, go to module Retrieved Update sets , click the Import Update Set from XML link and choose the XML file downloaded in step 1 Open update set PRB1553239 workaround from the list Click the Preview Update Set button and if necessary resolve any conflicts with the files being loaded Click the Commit Update Set button to apply the updates to the instance After applying this update set you will find that:

business rules Update Standard Change Version stats and Update Standard Version stats on delete are now inactive script include StdChangeUtils now contains some new and updated functions to support the calculation of the standard change statistics via a scheduled job new scheduled job Calculate Standard Change Template and Template Version statistics now exists and is set to run daily at 02:00 Related Problem: PRB1553239