Common Query Problems | Teradata Vantage - 17.00 - 17.05 - Recommendations for Common Query Problems - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval

You can monitor queries using the Teradata Viewpoint Query Monitor portlet and other tools and utilities to identify poorly performing queries. Poor query performance is usually due to:

Query Problem Result
Stale statistics The optimizer uses statistics to plan query execution. If the statistics are out of date, the optimizer may create a poor plan.

Recommendation: Review tables to see whether statistics are out of date, and recollect statistics if required, as shown in Improving Query Performance Using COLLECT STATISTICS: Application DBAs.

Badly skewed data Causes table rows to be distributed unevenly among AMPs and queries to execute unevenly, which slows processing time.

Recommendation: Monitor table skew. Redefine the primary index if required. See Finding and Fixing Skewed Tables.

Poor query construction Poor SQL syntax or missed punctuation can result in bad queries.

Recommendations:

Concurrency issues Some queries are delayed or prevented from executing because of lock contentions, or the relative priorities of concurrent queries.

Recommendations:

  • Identify the cause of a delayed query. See Investigating Query Blocks and Delays.
  • Release a query from the throttle delay cue. See Controlling Poorly Performing Queries.
  • Order the release of requests from the throttle delay queue based on workload priority, instead of first in, first out. See the General tab in the Teradata Viewpoint Workload Designer portlet.
  • Change the priority for a database operation or user.
  • Establish more detailed concurrency controls using TASM.