View the missing indexes of an ApsaraDB RDS for SQL Server instance - ApsaraDB RDS

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

The RDS instance does not run SQL Server 2008 R2 with standard SSDs or enhanced SSDs (ESSDs).

Procedure

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.
In the left-side navigation pane, choose Autonomy Service > Performance Optimization.
Click the Missing Indexes tab, select the filter conditions in the Missing Index Details section, and then click Export Script.
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 storage information 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 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 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 data table. The value of this parameter consists of the following parts: Database name 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 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. 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 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.