Skip to page contentSkip to chat
ServiceNow support
    • Community
      Ask questions, give advice, and connect with fellow ServiceNow professionals.
      Developer
      Build, test, and deploy applications
      Documentation
      Find detailed information about ServiceNow products, apps, features, and releases.
      Impact
      Accelerate ROI and amplify your expertise.
      Learning
      Build skills with instructor-led and online training.
      Partner
      Grow your business with promotions, news, and marketing tools
      ServiceNow
      Learn about ServiceNow products & solutions.
      Store
      Download certified apps and integrations that complement ServiceNow.
      Support
      Manage your instances, access self-help, and get technical support.
How to: Using "Active Query Index Hints" to improve slow query execution - Support and Troubleshooting
  • >
  • Knowledge Base
  • >
  • Support and Troubleshooting (Knowledge Base)
  • >
  • How to: Using "Active Query Index Hints" to improve slow query execution
KB0647687

How to: Using "Active Query Index Hints" to improve slow query execution


12017 Views Last updated : Jul 11, 2025 public Copy Permalink English (Original)
  • English (Original)
  • Japanese
KB Summary by Now Assist

Issue

This article is meant to help clarify when and how to add an active query index hint in ServiceNow.

Release

All releases

Resolution

Audience 


ServiceNow self-hosted customers using the MySQL database, and ServiceNow technical support engineers needing to improve a query when the database optimizer picks a suboptimal execution plan.

 

WARNING: Customers who are hosted by ServiceNow can refer to this information for general knowledge but will NOT be able to perform many of the steps listed below.  It is highly recommended that you create a case with ServiceNow support if experiencing slow query performance within a hosted environment.

 

Topics


  • Identify Slow Query
  • Confirm slowness on the DB
  • Determine possible index(es)
  • Get the Explain Plan 
  • Compare existing indexes
  • Test for improvement with index hint
  • Configure the Active Query Index Hint form
  • Define the Query Hint
  • Confirm the improvement

 

Identify the slow Query:

Background:

Some users complain of slow home pages and have identified a slow query coming from a home page using the Slow Queries Module, as below: 

Slow Queries Module

 
TIP:

To make sure you are targetting a query that is impactful and in need of optimization, consider filter options that show Average execution time > 2000ms and Execution count > 500 and an Example Java stack trace starts with Default-thread, meaning it is an interactive user transaction. Ordering the list by Total execution time assures you see the most significant query first.

Open the target query and look at the Last sighting field to ensure the query is still impacting performance and is in need of improvement. Copy the SQL in the Example field and save it in your favorite simple editor of text editor of choice. Make sure not to use MS Word or any other Rich editor.

SQL in the Example field

 

Confirm slowness on the DB

Access the MySQL database used for the instance in question and execute the same SQL query (copied from above) to confirm the query is slow:

 Execute the same SQL query

 

Determine possible index(es):

Looking at the where clause in the above SQL, a composite index on the following columns in the task table might be good:

assignment_group
state
assigned_to

This is just a guess right now, but see what indexes, if any, are being used currently. 

Get the Explain Plan:

Using the MySQL Explain Plan mechanism, notice how many rows the SQL query must access and what index is being used.

MySQL Explain Plan

 

Note that while an index is being used (task_ref2), we are still accessing 2 million rows to bring back 0 rows in our result.  Doing this takes the database 5.5 seconds.

Compare existing indexes:

See what columns on the task table the task_ref2 index is created from.

task_ref2 index

 

NOTE: Tables in MySQL Version 5.6 are limited to 64 total indexes. In this example, there are already 62 indexes on the task table.  Some indexes are composite (multi-column), and some are single-column, but there is a 64 total index limit we can't exceed.

Already near the maximum number of allowable indexes on the target table, look for a better already existing index. 

Reviewing the existing indexed columns in the task table, there is an existing index that covers all three columns from the where clause we suspected might be better. The currently used index, task_ref2 on, has ONLY the assigned_to column and active, as shown above.

We noted earlier that a possible index based on the where clause might be a composite index on the following columns:

assignment_group
state
assigned_to

There is an existing composite index that provides all three of the columns we think might benefit from indexing.  

Composite index

 

Test for improvement with index hint:

Now that we know a possibly better index already exists, see if it speeds up our query, using a force index query hint.

Index query hint

This same query took 5.5 seconds earlier, using the index the database optimizer thought best. Using this newly found composite index reduces the query time by about 4 seconds for this frequently used query, which is a significant improvement.  This is a case where a query hint would clearly help performance.

NOTE: This query is not ideal given the multiple OR conditions and the IS NULL.  Often it is necessary to review and try several different existing indexes carefully and to review and rewrite the query itself if improvements cannot be found. It might just as well happen that an ignore index query hint would help as much or more than a force hint. Testing is key to finding the right solution.

Configure the Active Query Index Hint form (if required):

NOTE: PRB1439443 - Active Query Index Hints do not work on tables with truncated aliases. For example, a physical table named x_nuvo_f8s_work_order with a logical name x_nuvo_facilities_work_order would not be able to receive an index hint. As a workaround,d you can just use an Active Query Rewrite.  

Now that we have confirmed an existing index helps the query execute faster, we can create an active query index hint within the ServiceNow instance. Up until now, everything has been to find a way to speed up query execution.  Here we implement the corrective action on the platform. 

In the filter navigator, type Active Query Index Hints and click the New button. If the page looks nearly black, like below, it will have to be configured. If the form has more fields, scroll down to the next section.

NOTE: Self-hosted customers will need to request maint user credentials from ServiceNow to access this active query index hint module.

Active query index hint module

 

Do the following to add the required form fields:

  1. Navigate to Configure > Form Layout
    Navigate to Configure > Form Layout
  2. Add the following selected fields:

    Add selected fields

  3. Save the changes to the form layout above, and the empty form should look like the following:
    Save the changes

 

Define the Active Query Index Hint:

From the Slow Queries module, we identified the slowest and most frequent query (a /home.do call) and targeted this for improvement.  We then found that forcing an index hint improved the query execution time significantly, and now we will define an Active Query Index Hint.

  1. Enter the Example (target) query, the Table name, Hint type, Index name, and Correlation name like the example below.
    Example (target) query

    Be aware of the following:
    • The yellow highlighted area above shows the resulting force hint by columns and not the name. This is a quirk of the form and should still work as expected. Once saved, the index hint example will show the index name - see below.
    • The index name selection pop-up gives the option to search by index name but will display the columns once selected.
    • The Correlation name is the table alias from the query itself.
  2. Once the Table name, Hint type, Active flag, and other details have been entered, save or submit the hint.  Note there is a validation process that occurs, and upon successful save, you will see a blue success message like the one below:
    Success message
  3. Compare the example rewrite SQL (shown in red above) to that which was earlier tested at the database:
    Example rewrite SQL

 

NOTE: Spacing, copy and paste of special characters and embedded carriage returns are all significant and can cause the platform query hint to not be implemented properly.  Spaces must be maintained if the example query had extra spaces, the query hint must have the same.  This is why a simple text editor works best, as special characters will not be inadvertently inserted into the hint.

Confirm the improvement:

Confirming the improvement to the SQL execution time for the target can be done in a couple of ways.

  1. If the source of the query is known, for example, a specific filter, report, or widget on a page, we can enable Debug SQL (Detailed) and confirm that the platform is properly substituting the original version of the query with that which we defined with the force index hint above:
    • From our original query at the beginning of this article, we know the source was a /home.do call.  In this case, the user homepage and widget were known, and we can quickly confirm that the platform is properly substituting the query via the debug output:
      Debug output

      The original timing is immediately available by setting the earlier defined index hint to be inactive (or having verified it firsthand before the index hint)

      Original query, slow execution
  2. If the source of the query is not clearly understood, we can simply watch the Slow Query module using the same parameters we originally used to identify the target query and check that the last sighting displays a time prior to our index query hint.  The new forced index query should also be visible with a very recent Last sighting time stamp, using appropriate filter conditions within the Slow Queries module.

    This example shows the original and the new version of the query with the force index hint displayed:
    Original and the new version of query

NOTE: This same process of verification and hint creation can be used for ignore, force, and use hint types.  This note could apply equally to each.

 

 


The world works with ServiceNow.

Sign in for more! There's more content available only to authenticated users Sign in for more!
Did this KB article help you?
Did this KB article help you?

Attachments

Attachments

  • pg_create_hint.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • Pasted image.png
  • eng_types.jpg

How would you rate your Now Support digital experience?

*

Very unsatisfied

Unsatisfied

Neutral

Satisfied

Very satisfied

Very unsatisfied

Unsatisfied

Neutral

Satisfied

Very satisfied

What can we improve? Please select all that apply.

What are we doing well? Please select all that apply.

Tell us more

*

Do you expect a response from this feedback?

  • Terms and conditions
  • Privacy statement
  • GDPR
  • Cookie policy
  • © 2025 ServiceNow. All rights reserved.