Identify Poorly Performing Queries | Teradata Vantage - Identifying Poorly Performing Queries - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
upb1600054424724.ditamap
dita:ditavalPath
upb1600054424724.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantage™

When notified of delays by a user or an alert, you can use the Teradata Viewpoint Query Monitor portlet to find out if the delay is the result of a poorly performing query.

The Query Monitor portlet has many functions beyond what is shown in this example procedure. See Teradata® Viewpoint User Guide, B035-2206 for detailed setup and options.
  1. Monitor query activity using the By Session tab.
  2. Look for poorly performing queries using the following Query Monitor parameters:
    • High CPU Use% combined with low REQ I/O accesses.

      If the CPU divided by the I/0 is > 100, the query is probably bad.

    • High CPU Skew% with significant Delta CPU.
    • High spool usage.
    You can click on and sort the display by a parameter instead of by session number.
  3. Select a session row that shows CPU characteristics indicating a bad query to display the Details View, which provides information about the query, including the user and account that submitted the query.
  4. Select the SQL tab to display the SQL for the query. Examine the SQL for errors and poor construction.
  5. Select the Explain tab to display an abbreviated version of the step statistics and explain text (generated from a full EXPLAIN request), along with a listing of the active steps, completed steps, and steps yet to run.
  6. Check for the following in the Explain:
    • The steps required to execute the query
    • The type of operation being performed, and whether it includes a product join

      Often the query is stuck on the step where the product join occurs.

  7. You can copy the query from the SQL tab, run it from Teradata Studio, and request a full EXPLAIN to get more details and validate the source of the problem.