Test index performance

After the Index Suggestion Engine (ISE) creates an index for a slow query, you can test the index to see if it improves the query time.

Before you begin

Role required: admin

About this task

This test is a quick assessment of the index and is not part of the 14-day index evaluation process. If you are evaluating an index suggestion on a sub-production instance, you can run this test after creating the index to get immediate results on index performance.
Note: Index test results from your sub-production instance may not necessarily predict similar performance results on your production instance.

Procedure

  1. Open the Index Suggestion record for the index to be tested:
    1. Navigate to System Diagnostics > Index Suggestion > Index Suggestions > In progress.
    2. In the Index Suggestions table, click the Table name for the index to be tested.
  2. In the Index Suggestion record, click Test Performance to start the performance test.
  3. When the Test Index Suggestion progress indicator shows that testing is complete, click Go to Result.
    Progress indicator window that shows the Query Execution Timer
  4. In the Query Performance Test Results form, compare the Average with index and Average without index fields to see if the index improved the query time.
    In the following example, the index improved the query time. The Forced check box is unchecked, indicating that the database is using the index. If the check box is checked, the database was forced to use the index during the test but is not using the index outside of the test.
    Test results of the Test Performance option, which checks the average query time with and without the index.
    Table 1. Query Performance Test Results
    Field Description
    Index Suggestion Name of the index suggestion.
    Created Date and time this performance test was run.
    Average with Index (ms) Query time using the index suggestion.
    Average without Index (ms) Query time before using the index suggestion.
    Forced Check box indicating whether the database is using the index. If this box is checked, the database was forced to use the index suggestion, but only for this test. If the box is unchecked, the database is using the index.
  5. Click the back arrow to navigate back to the Index Suggestion record for the table. The result of this index performance test does not change the index state recorded in the Index Suggestion record.