Tuning Contextual Search (CXS) to avoid excessive database contention / high CPU usage


A large scale roll-out of a feature that includes Contextual Search (CXS) - particularly in a ServicePortal - can potentially overload a database. There are a number of factors that contribute to database impact from this feature:

The crux of the issue is the combination of three things:

1. The out-of-box setting for Text Search parallelism allows 5 simultaneous queries per user (glide.ts.query.parallelism). This is has been this way since at least 2011, but, prior to the increased use of Contextual Search by ServiceNow customers in ServicePortal implementations, the parallelism feature has been historically engaged through the global search or keyword search features that require the end user to manually execute a search.

2. CXS search is triggered every time a change to the trigger field is registered on the client - and it is also triggered every time an existing form with CXS is loaded. With Contextual Search, the search feature is engaged whether the end user wants to see the results or not and it is potentially triggered multiple times, once with each keypress (e.g. search terms: A, An, And, Andr, Andre, Andrew...).

3. CXS is inherently CPU intensive due to joining huge text search tables to the target tables and to the related access handling tables (e.g. service catalog/knowledge catalog). With other global search behaviors, on the other hand, all access handling is handled after the results are returned via Contextual Security/Access Control Lists (ACL's). Thus the traditional implementation of text search had queries that were easier for the database to process.

This KB is not written to imply that Contextual Search has inherently bad performance. However, in order to successfully implement CXS on a wide-scale, without impacting the database performance or user experience, care should be taken to observe the following recommendations.

Steps to Reproduce

There are no specific steps to reproduce this issue. However, the most commonly observed way that this issue manifests is when there is a large scale rollout of a Service Portal that includes Contextual Search in a Record Producer. This is done by cloning the following out-of-the-box widgets:

The widget for contextual search is "Contextual Search - Inline Results" sp_widget_b001d945d7a43200f2d224837e6103d0


Tuning Contextual Search

Low level of effort (in order of impact)
1. INCREASE THE TIME BETWEEN KEY PRESSES before triggering Contextual search (from 500ms to something like 2 or 3 seconds). There is a system wide property for cases where a record producer is used, com.snc.contextual_search.wait_time.
2. TURN OFF WILDCARD BEHAVIOR FOR CONTEXTUAL SEARCH. Open to "Contextual Search > Search Contexts" in the left nav. For each record, open it and uncheck the option "Enable wildcard searches" then click "Update" or "Save". The wildcard search is turned off automatically in versions after London, but must be manually turned off for each CXS Config for customers upgrading from prior versions (see KB0794120/PRB660840)
3. The default setting for CXS is to use a "key press" trigger. CHANGE TO AN "OFF FOCUS" TRIGGER. Most customers do not do this since it creates a potentially undesirable user experience and decreases the effectiveness of CXS since the results appear later in the process. However, if you can do it, this change dramatically decreases the impact of CXS on the database.
4. The default behavior of Contextual Search is that every time a form loads on an existing record (that has the contextual search field on the form), ServiceNow will re-do/trigger the contextual search on form load automatically. Often this is not desired since the search results will be the same as the last time the form was loaded and it will just be added overhead to the database. There is a property that will change this behavior. Changing the property would mean CXS is only triggered if and when the description field actually changes.
com.snc.contextual_search.widget.form.open_collapsed_existing_records property is false by default. Set this property to "true" to yield a performance improvement.
5. EMERGENCY ONLY - Reduce multi-threading of text search queries per user by reducing the glide.ts.query.parallelism property (This is a "bounded" system property and the default is 5, allowing no more than 10 even if admin sets it higher). By reducing the parallelism you can quickly reduce the number of threads allowed to execute CXS queries simultaneously from a single user. This has the undesirable effect of making all global search results slower and therefore is likely only acceptable in an emergency situation to avoid systemic impact and should be returned to the default value after the emergency has been mitigated. 

Medium level of effort
1. Contextual Search works best with key word behavior - only including a few important terms. If very large fields are being used for the trigger field for Contextual Search this can result in very poorly performing queries due to the high number of possible search terms that must be considered when looking for a match. Switch design of affected pages to use only short fields for triggering contextual search; ideally using only fields that are restricted to 100 characters or less.

High-ish level of effort
1. Add global stop words (common English words like "have" or "an") - this will reduce wasted resources spent on indexing and retrieving irrelevant words
2. All fields are automatically text indexed if their table is text indexed. You should turn off text indexing for fields where it does not make sense (e.g. workflow state...) - this will reduce wasted resources spent on indexing and retrieving irrelevant fields. See link below regarding 
3. Increase the number of dynamic, "auto", stop words by lowering the stop words threshold and changing them to "Neither Index Nor Query".

➢ Configuring auto stop words and regenerate text indexes (step by step explanation of above process)

➢ Zing Text Search - Automatic Stop Words (more details about how/when to set an auto stop word threshold)

➢  Review the Documentation site for information on various ways to tune text indexing

e.g. Removing text indexing form a single fieldZing text indexing and search engineRegenerate a text index for a tableEnable automatic stop words for a table

Related Problem: PRB1381563