When the Domain Hierarchy Validation job runs its nightly validation (also seen on table upgrades) on large tables that are domain separated, severe performance issues can occur on the database such as longer response times and semaphore exhaustion across all nodes.
Steps to Reproduce
Note – Because the performance impact is dependent on many factors related to the database, this issue can be difficult to reliably reproduce.
Navigate to https://<instance_name>.service-now.com/sys_trigger_list.do?sysparm_query=name%3DDomain%20Hierarchy%20Validation
Open the Domain Hierarchy Validation scheduled job record and set the Next Action to a date/time in the past to trigger the job.
You might notice during the run time of this job that performance slows down on the instance.
If you are experiencing severe performance issues during the run time of the job, turn off the automatic Domain Hierarchy Validation job by setting it to On Demand. Because there are no outstanding issues that this job is expected to fix/validate on a regular basis, choosing not to run it on an automatic basis is safe.
- Navigate to https://<instance_name>.service-now.com/sys_trigger_list.do?sysparm_query=name%3DDomain%20Hierarchy%20Validation.
- Set the Trigger type to On Demand.
- If the job is currently running (State = Running), either go to All Active Transactions and kill the thread OR reach out to Customer Support to cancel the transaction or restart the node.
Review the Fixed In field and upgrade to fixed versions, which will provide:
- Optimizations made to this job
- A new system property, glide.sys.domain.validation_skip_tables, which enables you to exclude large tables by table name (for example, pa_scores, which does not need domain validation but is known to cause performance issues due to its table size).
Related Problem: PRB756920