ApsaraDB RDS:Troubleshoot the issues that cause high CPU utilization of an ApsaraDB RDS for SQL Server instance

Causes

In most cases, a sudden increase in CPU utilization is caused by the following issues:

• The number of query requests suddenly increases. For example, your workloads suddenly increase or the cache penetration occurs at the data caching layer.

• The CPU overhead for query requests suddenly increases. For example, new query requests are processed by using an inefficient method or the execution plans of some query statements change.

• The frequency at which the system compiles execution plans for query statements significantly increases. For example, if a large amount of data needs to be cached, the number of execution plans that are cached and the cache hit ratio significantly decrease. In this case, existing execution plans cannot be reused, and the system must frequently compile execution plans for SQL statements. As a result, the overall CPU overhead of your RDS instance significantly increases.

• A parameter sniffing issue occurs because the cached execution plans used for most workloads are not optimal.

Issue analysis

In the left-side navigation pane of the instance details page, choose Autonomy Services > Performance Optimization. On the Performance Insight tab of the page that appears, view the following metrics to identify the causes of the increase in the CPU utilization of your RDS instance.

Examples

The following figure shows a sample case.

The CPU utilization statistics show that the increase in CPU utilization occurs from 09:10 to 09:20 and from 09:30 to 09:40. However, the value of the QPS metric does not increase during these periods of time. The value of the QPS metric increases after 09:40. Therefore, the increase in CPU utilization is not caused by an increase in the number of query requests.

The value of the Sqlcompliations metric does not increase during these periods of time, and the absolute value of this metric is low. Therefore, the increase in CPU utilization is not caused by the CPU overhead that is required to compile execution plans for query statements.

The value of the Page_Lookups/sec metric increases at the same rate as CPU utilization during these periods of time. Therefore, the increase in CPU utilization may be caused by the high CPU overhead that is required to process some query requests during these periods of time.

You must identify the query statements that require high CPU overhead during these periods of time. If the value of the Page_Lookups/sec metric increases, CPU utilization also increases. Some query statements may require high CPU overhead for execution but medium CPU overhead for logical read operations. Therefore, you must analyze the query statements that are executed during these periods to troubleshoot the increase in CPU utilization.

Causes

The most common cause of a sudden increase in CPU utilization is the inefficiency of the method that is used to execute query statements. You can use the Average Active Sessions (AAS) metric of the Performance Insight to identify and analyze the query statements that are executed by using an inefficient method.

Issue analysis

The system checks active sessions every 10 seconds and records the SQL statements, query hash values, execution plans, and wait events of active query requests. In most cases, when a query statement that requires high CPU overhead is running, the value in the Wait Category column of the query statement on the Waits tab is CPU.

The SQL Hash column on the SQL tab displays the hash values that are generated after SQL statements are structured based on parameters. The hash values are used to mark and aggregate SQL statements that use the same structure. This way, the system can classify and aggregate SQL statements based on the structures of the SQL statements. You can query the latest statistics of an SQL statement from the sys.dm_exec_query_stats system view based on the value in the query_hash column of the SQL statement.

Identification

• Click the hyperlink in the SQL Hash column on the SQL tab to view the AAS statistics of the SQL statement.

• Click Analyze in the Execution Plan column on the SQL tab to view the execution plan of the SQL statement. You can also view the optimization suggestions that are generated by autonomy services.

The preceding optimization suggestions are suitable for SQL statements with simple structures. If the SQL statements on your RDS instance have complex structures, we recommend that you further analyze and test the execution plans of these SQL statements based on the preceding optimization suggestions. For more information about the AAS metric, see Use the performance insight feature.

Causes

You can use the ASS metric of the autonomy services to identify the SQL statements that cause an increase in CPU utilization during a specific period of time. However, information such as the execution frequency, average CPU overhead, and overall CPU overhead of various SQL statements, is not provided. If you want to optimize the overall CPU utilization of your RDS instance, we recommend that you obtain the details about the SQL statements that consume the most CPU resources.

Issue analysis

SQL Server can automatically aggregate information about objects such as SQL statements and stored procedures. SQL Server also provides system views, such as sys.dm_exec_query_stats and sys.dm_exec_procedure_stats for you to view information about the objects. Then, you can identify the SQL statements that consume the most resources. The resources can be of various types.