This topic describes how to view the performance insight data of an ApsaraDB RDS for SQL Server instance by using CloudDBA in the ApsaraDB RDS console. The performance insight feature supports instance-level load monitoring, association analysis, and performance optimization. It helps you evaluate loads and troubleshoot performance issues to make the RDS instance more stable.

Scenarios

Performance insight can be used in the following scenarios:

  • Profiling performance metrics

    Performance insight monitors the key metrics of an RDS instance and displays the status and trend of the loads on the instance. You can identify the sources and distribution of loads over a specific time range from the trend charts of the key metrics.

  • Evaluate loads

    ApsaraDB RDS for SQL Server provides a trend chart of average active sessions (AAS) to eliminate the need for you to analyze the complicated trend charts of various metrics. The AAS trend chart shows all the crucial performance data that you may need to evaluate the sources and bottlenecks of loads. For example, you can identify whether the RDS instance is heavily loaded due to high CPU utilization, lock-waiting, or I/O latency, and identify the SQL statements that cause heavy loads.

    Note The AAS metric indicates the average number of active sessions in the RDS instance over a specific time range. The changes to the value of the AAS metric reflect the changes of loads on the RDS instance. Therefore, the performance insight feature uses AAS as a key metric to measure loads on the RDS instance.
  • Find the sources that cause performance problems

    You can analyze the AAS trend chart and the multidimensional load source details to determine whether a performance problem is caused by improper instance specifications or the database architecture. You can also identify the SQL statements that cause the performance problem.

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 Performance Insight tab and select a time range. Filter criteria

Introduction to the Performance Insight tab

  • Performance Metric

    This section displays the trend charts of key metrics over a specific time range to help you understand the status and resource bottlenecks of loads on the RDS instance.

    You can select or customize a different time range to view the trend charts of key metrics over that time range.

    Note By default, the performance insight feature displays the data within the last 5 minutes. The default monitoring frequency is also 5 minutes. We recommend that you change the monitoring frequency of the RDS instance to 1 minute. If you do not change the monitoring frequency to 1 minute, only a data point is displayed in the trend charts. For more information, see Set the monitoring frequency of an ApsaraDB RDS for SQL Server instance.
    Trend charts of key metrics
  • Average Active Sessions(AAS)

    This section displays the AAS trend chart. After you identify the status of loads on the RDS instance based on the trend charts of key metrics, you can view the AAS trend chart to further identify the sources of loads.

    Note The max Vcores metric indicates the maximum number of cores that can be used by the RDS instance. The value of the max Vcores metric determines the processing capability of the RDS instance.

    The AAS trend chart helps you identify the sources of loads on the RDS instance. In the example shown in the preceding figure, you can identify the following sources of loads in three typical stages:

    1. In the first stage, all the loads come from User Sleep sessions.
    2. In the second stage, the number of User Sleep sessions gradually decreases, and a majority of the loads come from Sending Data sessions.
    3. In the third stage, the number of Sending Data sessions gradually decreases, and a majority of the loads come from Searching rows for update sessions.

    This example proves that the AAS trend chart whose data is updated in real time can help you intuitively obtain the sources, distribution, and change patterns of the loads on an RDS instance.

  • Load sources from multiple dimensions

    You can learn the trend of the loads for an ApsaraDB RDS for SQL Server instance by analyzing the trend chart of AAS. You can find the specific SQL statements that cause performance bottlenecks, and the related users, hosts, and databases.

    This section displays the details about the sources of loads from multiple dimensions in a table. You can use the table to identify the SQL statements that cause heavy loads. You can also use the table to identify the AAS of each SQL statement.

    The performance insight feature supports seven types of AAS. You can select an AAS type from the AAS Type drop-down list in the upper-right corner of the Average Active Sessions(AAS) section.

    AAS type Description
    SQL The AAS trends of the top 10 SQL statements.
    Waits The AAS trends of wait categories of the active sessions.
    Users The AAS trends of the users that have logged on to the RDS instance.
    Hosts The AAS trends of the hostnames or IP addresses of the clients from which the users log on to the RDS instance.
    Commands The AAS trends of different types of SQL statements.
    Databases The AAS trends of the databases in which your workloads run.
    Status The ASS trends of active sessions.