Over 90% of SQL Server performance issues—high CPU utilization, elevated IOPS, slow queries, and request timeouts—trace back to missing indexes. Database Autonomy Service (DAS) analyzes your ApsaraDB RDS for SQL Server instance and surfaces these gaps so you can act on them: identify which indexes are missing, export the index creation script, and run it during off-peak hours to restore performance.
Prerequisites
Before you begin, make sure that:
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, or UAE (Dubai)
The Missing Indexes tab
The Missing Indexes tab is divided into three sections.
Missing index overview
| Parameter | Description |
|---|---|
| Missing indexes | Total number of missing indexes across all tables in the instance, including the count of indexes that could improve performance by more than 80% |
| Missing indexes accessed in the last 1 day | Count and percentage of missing indexes that were accessed within the past day |
| Missing indexes accessed in the last 7 days | Count and percentage of missing indexes accessed within the past 7 days |
| Missing indexes accessed in the last 30 days | Count and percentage of missing indexes accessed within the past 30 days |
| Data updated at | Timestamp when the missing index statistics were last generated |
| Recollect | Asynchronously re-collects index usage statistics. After clicking OK in the confirmation dialog, wait a few minutes, then refresh the page to see updated data. Use this when statistics are stale or you need real-time data. |
| Save as PDF File | Downloads the current performance statistics as a PDF file |
Charts of missing index information
| Parameter | Description |
|---|---|
| Missing index changes | Trend of missing indexes over the past 24 hours |
| Last user seek time | Number of missing indexes that users searched against, broken down by the past 1 day, 7 days, 14 days, and 30 days |
| Average reduction in query cost | Average query CPU overhead caused by the missing indexes in the instance |
| Query performance improvement | The performance improvement caused by the missing indexes of the instance |
Missing index details
| Parameter | Description |
|---|---|
| Table name | Fully qualified table identifier: database name, schema name, and object name |
| Total storage | Storage allocated to the table |
| Records | Number of rows in the table that lack index coverage |
| Total pages | Number of data pages in the table |
| Indexes | Number of indexes already created on the table |
| Index column included in equal operation | Columns used for equivalent queries because indexes are missing on the columns in the table |
| Index column included in not equal operation | Columns used for non-equivalent queries because indexes are missing on the columns in the table |
| Index key columns | Columns recommended as index key columns |
| Seeks performed by user | Number of seek operations performed against this table's missing indexes |
| Scans performed by user | Number of scan operations performed on the table |
| Average cost savings | Estimated reduction in average CPU overhead (as a percentage) after the missing index is created |
| Performance improvement (%) | Estimated query performance gain (as a percentage) after the missing index is created |
| Last user seek time | Most recent time a user performed a seek on this table's missing indexes |
| Index creation statements | SQL statements that create the recommended index |
| Export script | Exports the index creation SQL as a script file for later execution |
| Export file | Exports the missing index details to an Excel, CSV, or TXT file |
View and export missing index recommendations
Log on to the ApsaraDB RDS console. On the Instances page, select the region where your instance resides, then click the instance ID.
In the left-side navigation pane, choose Autonomy Services > Performance Optimization.
Click the Missing Indexes tab.
In the Missing index details section, select the filter conditions, then click Export script
What's next
Run the exported SQL script on your RDS instance during off-peak hours to create the missing indexes and resolve performance issues.