The Alibaba Cloud team analyzes the performance issues occur during instance usage and identifies that more than 90% of the performance issues are caused by missing indexes. The performance issues include high CPU utilization, high IOPS usage, low query performance, and request timeout. Database Autonomy Service (DAS) provides the missing index query feature. This feature helps you find the missing indexes on your ApsaraDB RDS for SQL Server instance and allows you to export the script that is required to create the missing indexes. Then, you can run the script during off-peak hours to resolve the preceding performance issues.
Prerequisites
Your RDS instance does not run SQL Server 2008 R2 with cloud disks.
Your RDS instance resides in one of 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, and UAE (Dubai).
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.
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 a long period of time is required to update index usage statistics or real-time index usage statistics are required, click Recollect. In the message that appears, click OK. The system asynchronously collects the index usage statistics of the RDS instance again. After a few minutes, you can refresh the 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 and wait for the system to generate a 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.
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.
Parameter | Description |
Table Name | The name of the table on which the index is created. The value of this parameter consists of the database name, schema name, and 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. The query performance improvement is calculated in percentage. |
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 can be 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. |
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, select the filter conditions in the Missing Index Details section, and then click Export Script.
What to do next
Execute the SQL statements provided in the exported script file to create indexes for tables on the RDS instance during off-peak hours.