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

Procedure

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, choose CloudDBA > 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 Indexes 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 smaller than 100.
    Data Updated At The time at which the index usage statistics of the RDS instance were generated.
    Recollect Data If the index usage statistics are outdated, click Recollect Data. 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.
    Storage 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 nonclustered indexes that have the highest degree of fragmentation.
  • Index Information: 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 section.
    Table 3. Parameters in the Index Information 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 three 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.
    Seeks The number and percentage of search operations that are performed based on the index.
    Scans The number and percentage of scan operations that are performed based on the index.
    Bookmark Lookups 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.
    Creation Time 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 that is used to export the SQL statements that were used to create the index.
    Export File The button that is used to export the usage of the index as an Excel, CSV, or TXT file.