This topic describes how to view the missing indexes of an ApsaraDB RDS for SQL Server instance by using CloudDBA in the ApsaraDB RDS console.

The Alibaba Cloud expert team summed up years of service experience that many customers encountered the following performance issues when using an ApsaraDB RDS for SQL Server 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.

CloudDBA of ApsaraDB RDS for SQL Server 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.

Procedure

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, choose CloudDBA > 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 Data If the index statistics of the RDS instance are outdated, click Recollect Data. In the message that appears, click OK. Then, ApsaraDB RDS starts to collect index statistics for the RDS instance again.
    Note After a few minutes, you can refresh the Performance Optimization page to view the new index statistics.
    Save as PDF File If you want to save the missing index 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
    Trend of Missing Index The trend of the missing indexes of the RDS instance within the last 24 hours.
    Last User Query 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 Cost Savings The average query CPU overhead that is caused by the missing indexes in the RDS instance.
    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 Charts of Missing Index Details section
    Parameter Description
    Table Name The name of the table. The value of this parameter consists of three parts:
    • Database name
    • Schema name
    • Object name
    Total Storage The storage space 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 Column Included in SELECT Clause 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.
    Performance Improvement (%) The average query performance improvement for the table after missing indexes are created.
    Last User Query 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.