All Products
Search
Document Center

ApsaraDB RDS:View missing indexes

Last Updated:Mar 25, 2024

Database Autonomy Service (DAS) provides the missing index query feature. This feature allows you to view the missing indexes of an ApsaraDB RDS for SQL Server instance.

The Alibaba Cloud expert team summed up years of service experience and note that many customers encountered the following performance issues when they use an RDS instance: the CPU utilization of an instance is high, the IOPS of an instance is high, the query statement performance of an instance is low, and timeout of requests of an instance. The team found that more than 90% of the issues are caused by missing indexes.

DAS helps you find the missing indexes on RDS instances and export the script files that are required to create the missing indexes. Then, you can create these missing indexes during off-peak hours to solve the preceding performance issues.

Prerequisites

Your RDS instance does not run SQL Server 2008 R2 with cloud disks.

Procedure

  1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
  2. In the left-side navigation pane, choose Autonomy Services > Performance Optimization.

  3. Click the Missing Indexes tab, select the filter conditions in the Missing Index Details section, and then click Export Script.

  4. Execute the SQL statements provided in the exported script file to create indexes for tables on the RDS instance during off-peak hours.

Introduction to the Missing Indexes tab

  • Missing Index Overview: This section provides an overview of the missing indexes of the RDS instance. The following table describes the parameters in the Missing Index Overview section.

    Table 1. Parameters in the Missing Index Overview section

    Parameter

    Description

    Missing Indexes

    The total number of missing indexes of all tables that are created in the RDS instance and the number of indexes that can improve the performance of an instance by more than 80%.

    Missing Indexes Accessed in the Last 1 Day

    The number of accessed missing indexes of all tables that are created in the RDS instance within the last one day and the percentage of the accessed missing indexes in all the missing indexes.

    Missing Indexes Accessed in the Last 7 Days

    The number of accessed missing indexes of all tables that are created in the RDS instance within the last seven days and the percentage of the accessed missing indexes in all the missing indexes.

    Missing Indexes Accessed in the Last 30 Days

    The number of accessed missing indexes of all tables that are created in the RDS instance within the last 30 days and the percentage of the accessed missing indexes in all the missing indexes.

    Data Updated At

    The time when the missing index statistics of the RDS instance were generated.

    Recollect

    If the index usage statistics are outdated, click Recollect. In the message that appears, click OK. The system starts to collect the index usage statistics of the RDS instance again.

    Note

    After a few minutes, you can refresh the Performance Optimization page to view the updated index usage statistics.

    Save as PDF File

    If you want to save the performance statistics to your computer as a file, click Save as PDF File.

  • Charts of Missing Index Information: This section displays the trend of missing indexes of the RDS instance and the trends of performance improvement and resource consumption reduction that are caused by missing indexes. These trends are displayed in charts. The following table describes the parameters in the Charts of Missing Index Information section.

    Table 2. Parameters in the Charts of Missing Index Information section

    Parameter

    Description

    Missing Index Changes

    The trend of the missing indexes of the RDS instance within the last 24 hours.

    Last User Seek Time

    The number of missing indexes on which users searched on the RDS instance within the last 1 day, last 7 days, last 14 days, and last 30 days.

    Average Reduction in Query Cost

    The average query CPU overhead that is caused by the missing indexes in the RDS instance.

    Query Performance Improvement

    The performance improvement that is caused by the missing indexes of the RDS instance.

  • Missing Index Details: This section displays the details about all missing indexes of the RDS instance in a table. The following table describes the parameters in the Missing Index Details section.

    Table 3. Parameters in the Missing Index Details section

    Parameter

    Description

    Table Name

    The name of the table on which the index is created. The value of this parameter consists of the following parts:

    • Database names

    • Schema name

    • Object name

    Total Storage

    The storage that is allocated to the table.

    Records

    The number of records on which indexes are missing in the table.

    Total Pages

    The number of pages in the table.

    Indexes

    The number of indexes that are created on the table.

    Index Column Included in Equal Operation

    The column that is used for equivalent queries because indexes are missing on the columns in the table.

    Index Column Included in Not Equal Operation

    The column that is used for non-equivalent queries because indexes are missing on the columns in the table.

    Index Key Columns

    The column on which indexes are missing in the table.

    Seeks Performed by User

    The number of search operations that are performed for the missing indexes in the table.

    Scans Performed by User

    The number of scan operations that are performed on the table.

    Average Cost Savings

    The estimated average CPU overhead reduction for the table after missing indexes are created. The CPU overhead reduction is calculated in percentage.

    Performance Improvement (%)

    The average query performance improvement for the table after missing indexes are created.

    Last User Seek Time

    The most recent point in time when the user searches for the missing indexes in the table.

    Index Creation Statements

    The statements that are used to create missing indexes in the table.

    Export Script

    The button that is used to export the statements used to create missing indexes. You can select an appropriate time to execute the statements on the RDS instance to create missing indexes.

    Export File

    The button that is used to export the details about missing indexes to an Excel, CSV, or TXT file.