How to: Using "Active Query Index Hints" to improve slow query execution | Improving Performance
This article is meant to help clarify when and how to add an active query index hint in ServiceNow.
ServiceNow Self Hosted customers using the MySQL database as well as 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.
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:
To make sure you are targetting a query that is impactful and in need for 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 assure 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.
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:
Looking at the where clause in the above SQL, a composite index on the following columns in the task table might be good:
This is just a guess right now, but see what indexes, if any, are being used currently.
Using the MySQL Explain Plan mechanism, notice how many rows the SQL query must access and what index is being used.
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.
See what columns on the task table the task_ref2 index is created from.
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 a 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 which covers all three of 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:
There is an existing composite index which provides all three of the columns we think might benefit from indexing.
Now that we know a possibly better index already exists, see if it speeds up our query, using a force index query hint.
This same query took 5.5 seconds earlier, using the index the database optimizer thought best. Using this newly found the 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 carefully review and try several different existing indexes and to review and rewrite the query itself if improvements cannot be found. It might just as well happen than an ignore index query hint would help as much or more than a force hint. Testing is key to finding the right solution.
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 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. Everything up until now has been to find a way to speed the 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 in order to access this active query index hint module.
Do the following to add the required form fields:
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.
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.
Confirming the improvement to the SQL execution time for the target can be done in a couple of ways.
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.