The Collect Table Per Hierarchy Stats job and Collect Table Stats jobs can lead to daily flushing of the TableDescriptor cache and performance degradation, transaction delays occurring when attempting to load any form for the first time ie. modification of forms, creating record producers, setting up rules, etc.
The delays often are accompanied by a small pop-up window at the top of the screen that provides "transaction delay-___ sec"
In the transaction logs you will see a high cache build time at the end of the transaction.
2019-07-26 12:11:30 (626) Default-thread-2 D164B48F1BB63B8042FB0D01CD4BCB3C txid=8484b8431bf6 EXCESSIVE *** End #2982253 /slushbucket.do, user: abc.xyz , total time: 0:00:22.486, processing time: 0:00:22.486, SQL time: 0:00:07.351 (count: 11,142), ACL time: 0:00:00.028, Cache build time: 0:00:20.033, source: 22.214.171.124 null
Note the high Cache Build time: Cache build time: 0:00:20.033
Steps to Reproduce
This is hard to reproduce on a non-production system. However, it can be approximated by creating many tables on any instance and simulating enough traffic.
- Have an instance with around 10000 or more tables
- Run the "Collect Table Per Hierarchy Stats job" by executing from sys_trigger as maint
- Review the localhost logs and observe full TD cache flush
- If the load can be simulated during the period immediately before and after the cache flush you can observe several threads will need to call createSchema0 leading to a longer response time while building the necessary cached entries.
This issue is under review. If you are experiencing this problem, contact ServiceNow Technical Support.
Meanwhile to avoid the issue, you can safely have these jobs set to run every 30 days instead of every day.
NOTE: These jobs collect recordCount() from multiple tables and it should not typically matter as we can do that once every month.
Related Problem: PRB1309544