Description
The Index Suggestion Engine (ISE) can generate an index suggestion for a selected slow query. When you request an index suggestion for a slow query, the ISE analyzes the query and recommends an index that can improve the query execution time.
If you choose to use the index suggestion and create the index, the ISE continues to review the effectiveness of that index during a 14-day evaluation period. The ISE provides details on the index during the evaluation, including recommendations for managing the index.
- Generate an index suggestion for a slow query.
- Review index suggestions for slow queries in your instance.
- Export an index suggestion to a non-production instance for evaluation and testing.
- Schedule an index for creation.
- Monitor the effectiveness of an index during the index evaluation period.
- Test index performance (this test is an immediate performance assessment of the index).
- Drop an index that does not optimize query performance, as recommended by the ISE.
Release or Environment
All Releases London or later.
Cause
As the table grows, indexes are used to improve performance. This KB will walk you through the process to create and test Indexes.
Resolution
First, use this link that will show you today's slow queries:
https://<INSTANCE_NAME>.service-now.com/nav_to.do?uri=%2Fsys_query_pattern_list.do%3Fsysparm_clear_stack%3Dtrue%26sysparm_query%3Dwindow_endISEMPTY%255Ewindow_startISEMPTY%255ElastONCurrent%2520hour%2540javascript%253Ags.beginningOfCurrentHour()%2540javascript%253Ags.endOfCurrentHour()%26sysparm_userpref_module%3D9d967a108f30300072a89cfde0f92389%26sysparm_list_mode%3Dgrid%26sysparm_offset%3D
This will show you the queries that are providing the biggest impact to you as of the current hour. You can re-run your slow queries so that this query can easily find them.
Select one of the queries that you need to tune and it will take you to the page that shows the query and the Java Stack Trace that creates it.
Press the Suggest Index Button;
Chances are you will see a warning that states "Unable to suggest an index for this query - The following tables from this SQL statement do not have column statistics: <table Selected>. You can wait for the Collect Column Stats job to run, or you can collect statistics now.
Once the Stats are collected you can click on the create index button. This takes you to a page where you can schedule the creation of the index.
When you click on "Schedule" a pop-up shows up asking to do it now or schedule for later, if you click now the process will start (you should perform this off hours).
Once the process is complete, you will return to the Index Suggestion Screen and at the bottom of the screen is the listing of the new index you created.
From this page you can run a performance test to see how the index is working on your query and / or if you wish to drop it you can do this here.
You can check the status of your indexes by typing "index suggestion" (no quotes) in the Filter Navigator. If you chose to create the index later, you can come here to check to see if they have been created, if the system is evaluating them or if the process is complete.
Additional Information
For the complete documentation on this process click over to Index Search (ISE) Documentation