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
- 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 .
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.
NoteAfter 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.