Our analysis of common issues for ApsaraDB RDS users shows that missing indexes cause over 90% of performance problems, such as high CPU utilization, high IOPS, poor query performance, and application timeouts. The Missing Indexes feature in Database Autonomy Service (DAS) identifies these indexes and generates scripts to create them. You can run these scripts during off-peak hours to optimize database performance and resolve these issues.
Prerequisites
-
This feature is not available for ApsaraDB RDS for SQL Server 2008 R2 instances that use cloud disks.
-
This feature is available only for ApsaraDB RDS for SQL Server instances in the following regions: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Shenzhen), China (Heyuan), China (Guangzhou), China (Chengdu), China (Hong Kong), Singapore, or UAE (Dubai).
Page overview
Missing index overview
This section provides an overview of the missing indexes on your ApsaraDB RDS instance.
|
Parameter |
Description |
|
Total missing indexes |
The total number of missing indexes across all tables in the instance, and the number of those that can improve performance by more than 80%. |
|
Accessed in the last day |
The number of times missing indexes were accessed in the last day, and their percentage of the total. |
|
Accessed in the last 7 days |
The number of times missing indexes were accessed in the last 7 days, and their percentage of the total. |
|
Accessed in the last 30 days |
The number of times missing indexes were accessed in the last 30 days, and their percentage of the total. |
|
Data updated at |
The last time the data on the Missing Indexes page was generated. |
|
Recollect |
If the Data updated at timestamp is outdated or you need real-time data, click Recollect and confirm the action. The system asynchronously collects data in the background. After a few minutes, refresh the page to view the results. |
|
Save as PDF |
To save the page to your local device, click Save as PDF and wait for the PDF file to be generated. |
Missing index charts
This section provides charts that visualize missing index trends, potential performance improvements, and resource savings.
|
Parameter |
Description |
|
Missing index trend |
A trend chart that shows the number of missing indexes over the last 24 hours. |
|
Last user seek time |
The total number of user seeks for missing indexes over the last day, week, two weeks, and month. |
|
Average query cost reduction |
The estimated average reduction in query cost after creating the missing indexes. |
|
Query performance improvement |
The estimated performance improvement after creating the missing indexes. |
Missing index details
This section provides detailed information for each missing index in your instance.
|
Parameter |
Description |
|
Table name |
The fully qualified name of the table, which consists of the database name, schema name, and table name. |
|
Total storage |
The total storage space allocated to the table. |
|
Records |
The total number of records in the table. |
|
Total pages |
The total number of pages used by the table. |
|
Number of indexes |
The number of existing indexes on the table. |
|
Equality columns |
Columns that are used in equality predicates (for example, |
|
Inequality columns |
Columns that are used in inequality predicates (for example, |
|
Included columns |
Columns that are recommended for the |
|
User seeks |
The number of seek operations that would have used this index. |
|
User scans |
The number of scan operations performed on the table. |
|
Average cost saving |
The estimated percentage of reduction in the average CPU cost after creating the index. |
|
Performance improvement (%) |
The estimated percentage of improvement in the average query performance after creating the index. |
|
Last user seek time |
The last time a user query would have used this index for a seek operation. |
|
Index creation statement |
The SQL statement that you can use to create the missing index. |
|
Export script |
Exports the creation statement for the missing index. You can run the script on the corresponding ApsaraDB RDS instance during off-peak hours. |
|
Export file |
Exports the missing index details to an Excel, CSV, or TXT file. |
Procedure
Log on to the ApsaraDB RDS console and 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 instance ID.
-
In the left-side navigation pane, choose .
-
Click the Missing Indexes tab. In the Missing Index Details section, select your desired filters, and then click Export script.
Next steps
During off-peak hours, run the statements in the exported script on the ApsaraDB RDS instance.