67 views

Description

When deleting or reclassifying cmdb_ci records, an unnecessary amount of queries are triggered. This is due to the migration of cmdb to TPP because there is no optimization to touch only the necessary tables. This issue can become dramatically worse if the cmdb_ci reference field does not have an index because the unnecessary queries will have dramatically worse performance.
Example of impacted query
2018-02-25 05:12:14 (150) worker.1 worker.1 Time: 0:10:04.231 id: ey_1[glide.10] for: UPDATE task SET `business_service`= NULL WHERE task.`business_service` = '39034d950c332000adffa6480116c689' /* <db_name>103, gs:glide.scheduler.worker.1, tx:f68b4a74db201700fc1676d9bf961917 */
Note – task.business_service is the only OOB cmdb_ci reference field on task and is indexed OOB. If this index is removed, this problem can be exacerbated. A couple of columns also have been added via plugins (task.service_offering) or custom that should be indexed.

Steps to Reproduce

  1. Create a new cmdb_ci_win_server record.
    2. Create an incident and choose your new CI as the Configuration Item.
    3. Navigate to the cmdb_ci_win_server record (with Debug SQL (Detailed) on).
    4. Change the class to something else.
    An update will occur to task.business_service.
    (NOTE: Sometimes you have to change the class a second time to see it in the debug sql.)

Example problematic query:

2018-02-25 05:12:14 (150) worker.1 worker.1 Time: 0:10:04.231 id: <db_name>_1[glide.10] for: UPDATE task SET `business_service`= NULL WHERE task.`business_service` = '39034d950c332000adffa6480116c689' /* <db_name>103, gs:glide.scheduler.worker.1, tx:f68b4a74db201700fc1676d9bf961917 */

Workaround

For immediate relief, add an index on the cmdb_ci reference field that appears in the WHERE clause of slow UPDATEs.

Table: task

Field: Name of cmdb_ci reference (i.e., business_service OR service_offering)

Either reach out to ServiceNow Customer Support for assistance or see the product documentation topic Create Table Index.

NOTE - You might see a similar symptom but no slow UPDATEs. If instead you see hundreds of seemingly unnecessary queries against sys_dictionary whenever any CI is deleted (or reclassed) and this happens for the second and subsequent CI that is deleted, the "relationships" cache might not be large enough. Check whether the cache is full by looking for the information about syscache_relationships in xmlstats.do.  If it is full, increase value for the glide.cache.size.syscache_relationships system property:

If it is not defined, set the property to 3000 (double the default size of 1500). Then flush that cache with GlideCacheManager.flush("syscache_relationships").  The *next* delete (or reclassing) will be slow, and then subsequent ones should improve.


Related Problem: PRB1258222

Seen In

Istanbul

Intended Fix Version

London

Safe Harbor Statement

This "Intended Fix Version" information is meant to outline ServiceNow's general product direction and should not be relied upon in making a purchasing decision. The information provided here is for information purposes only and may not be incorporated into any contract. It is not a commitment, promise, or legal obligation to deliver any material, code, or functionality. The development, release, and timing of any features or functionality described for our products remains at ServiceNow's sole discretion.

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2018-07-19 06:12:47
Published:2018-07-19