Generate an index suggestion for a slow query

Use the Index Suggestion Engine (ISE) to generate an index suggestion for a slow query.

Before you begin

Role required: admin

About this task

When the ISE generates an index suggestion for a slow query, it reviews query metrics and information from tables in the query. If the ISE does not have the information required to generate an index suggestion, the system displays messages informing you about the data needed. These messages typically provide links for obtaining more information, such as updated query metrics or gathering column statistics from tables in the query. Sometimes the ISE may be unable to suggest an index for the query and lets you know why the suggestion cannot be generated.

Procedure

1. Navigate to All > System Diagnostics > Stats > Slow Queries.
2. In the Slow Queries table, click the query record for which the index suggestion is to be generated.
3. In the Slow Queries record, click Suggest Index.
   If the ISE needs more information, the system may display one or more messages with details for obtaining that information. After you respond to each message, click Suggest Index. Repeat this process until the ISE has the information required to generate the index suggestion.

   For example, the ISE may need:

   • Recent metrics—If the system displays a message indicating that query metrics are outdated, the message provides a link for obtaining the most recent metrics.
     • Click the link to get the updated query metrics.
     • Click Suggest Index.
   • Column statistics—If the column statistics do not exist for the query, the system displays a message asking you to schedule the Collect Column Stats job. You can choose to run the job at the default time when the system is less busy, or you can have the system collect the statistics immediately.
     • If you schedule the Column Stats job to run later, the ISE generates the index suggestion after it collects the column statistics. After the column stats job runs, return to the Slow Queries record and click Suggest Index.
     • If you choose to collect column stats immediately, the system displays the Collect Column Statistics progress indicator while it gathers column statistics. In the Slow Queries record, click Suggest Index.

   When the ISE successfully generates an index suggestion, the Index Suggestion record for the query shows the index State as Suggested.

4. If you want to continue working with the index suggestion, select the next processing step

   Option	Description
   Export	Downloads the index suggestion as an .xml file so that you can export it to a non-production instance for testing and evaluation.
   Ignore	Sets the index suggestion state to Ignore, which indicates that the index suggestion is not to be used. Index suggestions with an Ignore state are listed in the Index Suggestions > Done view of the Index Suggestion table.
   Schedule creation	Opens the Schedule Index Creation window for scheduling index creation.