SQL basic information

Terms

The basic concepts of SQL diagnostics are as follows:

• TopSQL: SQL statements with the longest overall response time within a specified time range.

• SlowSQL: SQL statements whose execution time exceeds 500 ms.

• Suspicious SQL: SQL statements that are filtered based on rules and algorithms and may cause performance issues.

TopSQL

TopSQL refers to SQL statements with the longest overall response time within a specified time range.

• Click a quick filter, such as Full Table Scan or Multi-partition Scan, to quickly identify SQL statements that require optimization.

• In the Operation column, click View Samples to view the sample SQL statement.

• In the Operation column for an SQL statement, click and select Set Throttling to set a throttling rule, or select multiple SQL statements to set throttling rules in a batch.

• Click + before the SQL text to view the Last Error and Error Summary for the Statistical Period.

• Select Aggregate IN queries in the upper-right corner of the list to display SQL statements aggregated by IN query. This does not affect the statistical data. You can select multiple SQL statements, including aggregated IN queries, to set throttling rules in a batch.

• Click View Optimization Suggestions in the upper-right corner of the list to open the Optimization Center.

• Click TopSQL Comparison in the upper-right corner of the list to compare the performance of an SQL statement across different time periods or on different nodes.

  • Comparison of different time periods: Compare and analyze the running status of the same SQL statement on the same node during a baseline period and a comparison period. You can then view the Detailed Data Comparison and Performance Trend Comparison information.

  • Comparison of different nodes: Compare the execution status of the same SQL statement on a baseline node with other nodes. You can then view the Detailed Data Comparison and Response Time Trend Comparison.

  • Single SQL comparison across different time periods: You can compare the performance of a single SQL statement or an aggregated SQL query between a baseline period and a comparison period, and view the Total Database Time and Total Executions.

  • Single SQL comparison across different nodes: Compare and analyze the running status of a single SQL statement or an aggregated SQL query on different analysis nodes. You can then view the Total Database Time and Total Executions information.

    Note

    • The same SQL statement refers to a statement with the same database and SQL ID.

    • When you compare different nodes, a result is returned only if the same SQL statement is executed on the different nodes.

    • You can also enter an SQL ID and select ... > Single SQL Comparison from the Operation column to compare the SQL statement across different time periods and nodes.

• Click Custom Columns in the upper-right corner of the list to add custom metric columns.

  1. In the dialog box that appears, set the Column Expression to an expression such as (@avgCpuTime * @avgDiskReads) / 2.

  2. Enter a Column Name.

  3. Click OK.

• If the CPU load on a node is too high, you can first filter by Database and Node, and then sort the SQL statements by CPU Time in descending order. This helps you identify the SQL statements that require the most optimization.

SlowSQL

SlowSQL refers to SQL statements whose execution time exceeds 500 ms.

• Click a quick filter, such as Full Table Scan or Multi-partition Scan, to find SQL statements that require optimization.

• To view the SQL statement, click View Samples in the Operation column.

• In the Operation column for an SQL statement, click and select Set Throttling to set a throttling rule. You can also select multiple SQL statements to set throttling rules in a batch.

• Select Aggregate IN queries in the upper-right corner of the list to aggregate SQL statements that use IN queries. This does not affect the statistical data. You can select multiple SQL statements, including the aggregated IN queries, to set throttling rules in a batch.

• To customize the slow SQL threshold, select ... > Modify Slow SQL Threshold  in the upper-right corner of the list. In the dialog box that appears, modify the slow SQL threshold and click OK .

Suspicious SQL

Suspicious SQL refers to SQL statements that are filtered based on rules and algorithms and may cause performance issues. The following table lists common diagnostic results and suggested actions:

The Suspicious SQL tab displays information such as SQL Text, SQL ID, Database, and Diagnostic Result.