All Products
Search
Document Center

ApsaraDB RDS:View the index usage statistics of an ApsaraDB RDS for SQL Server instance

Last Updated:Jun 08, 2023

This topic describes how to view the index usage statistics of an ApsaraDB RDS for SQL Server instance by using Database Autonomy Service (DAS) in the ApsaraDB RDS console. The statistics include the usage of indexes and the degrees of fragmentation in indexes.

Prerequisites

Your RDS instance does not run SQL Server 2008 R2 with cloud disks.

Procedure

  1. 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.
  2. In the left-side navigation pane, choose Autonomy Service > Performance Optimization.

  3. Click the Index Usage tab.

Introduction to the Index Usage tab

  • Index Overview: This section provides an overview of the index usage statistics of the RDS instance. The following table describes the parameters in the Index Overview section.

    Table 1. Parameters in the Index Overview section

    Parameter

    Description

    Total Index Size

    The total number of indexes that are created in the RDS instance.

    Total Index Storage

    The amount of storage space that is used by all indexes in the RDS instance.

    Fragmentation Percentage Exceeds 30%

    The number of indexes whose degrees of fragmentation exceed 30%.

    Index Seeks Less Than 100

    The number of indexes on which the number of search operations is less than 100.

    Data Updated At

    The point in time at which the index usage statistics of the RDS instance were generated.

    Recollect

    If the index usage statistics are outdated, click Recollect. In the message that appears, click OK. ApsaraDB RDS 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 index usage statistics of the RDS instance to your computer as a file, click Save as PDF File.

  • Charts of Index Information: This section displays the index usage statistics of the RDS instance in charts. The following table describes the parameters in the Charts of Index Information section.

    Table 2. Parameters in the Charts of Index Information section

    Parameter

    Description

    Fragmentation Percentage

    The distribution of the degrees of fragmentation in all indexes.

    Usage

    The distribution of the usage of all indexes.

    Space Changes

    The changes to the amount of storage space that is used by all indexes.

    TOP Fragmentation Percentage

    The degrees of fragmentation in the top 10 clustered indexes and top 10 nonclustered indexes that have the highest degree of fragmentation.

  • Index Information Table: This section displays the details about the usage of all indexes in the RDS instance. The following table describes the parameters in the Index Information Table section.

    Table 3. Parameters in the Index Information Table 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 name

    • Schema name

    • Object name

    Index Name

    The name of the index.

    Fragmentation Percentage

    The degree of fragmentation in the index.

    Size (MB)

    The amount of storage space that is used by the index.

    Maintenance Operation

    The maintenance operation that is recommended for the index.

    Reason

    The reason why the maintenance operation is recommended for the index.

    Priority

    The priority of the maintenance operation.

    Pages

    The number of pages that are occupied by the index.

    Search

    The number and percentage of search operations that are performed based on the index.

    Scan

    The number and percentage of scan operations that are performed based on the index.

    Bookmark Lookup

    The number and percentage of key lookup operations that are performed based on the index.

    Update

    The number and percentage of update operations that are performed on the index.

    Primary Key

    Indicates whether the index is a primary key index.

    Disable

    Indicates whether the index is disabled.

    Column

    The columns on which the index is created.

    Fill Factor

    The fill factor of the index.

    Created At

    The time at which the index was created.

    Statistics Update Time

    The most recent time at which the statistics of the index were updated.

    Export Script

    The button for exporting the SQL statements that were used to create the index.

    Export File

    The option for exporting the usage of the index as an Excel, CSV, or TXT file.