Planning New Workloads for Analysis - Teradata Workload Analyzer

Teradata Workload Analyzer User Guide

Product
Teradata Workload Analyzer
Release Number
17.00
Published
September 2019
Language
English (United States)
Restricted Access
TTU-17.00-EAP
Last Update
2019-06-07
dita:mapPath
vjd1544831946946.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2514
Product Category
Teradata Tools and Utilities

The keys to developing service level goals and distributing resources adequately to achieve those goals are the following:

  • Identifying new candidate workloads
  • Segmenting them into manageable pieces
  • Assigning them appropriate priorities

Teradata WA enables administrators to understand their resource-intensive workloads at a high level. Instead of identifying workloads on the SQL statement level, Teradata WA defines them by account or application. This isolates workloads in a way that is more relevant to the business of the enterprise. Administrators can associate resource demands with “who” is generating the work or by “what” work is being done. When Database Administrators know these critical factors, they can make informed choices about allocating resources in the best interests of the organization.

For example, a mission-critical application that is not resource-intensive might be a single workload and a single service-level goal with a high percentage of resources allocated, while ad-hoc applications can be grouped together and supported at a lower percentage of service.

In addition to identifying workloads from a business perspective, it is important to find a way to segment the workloads for effective management. If the majority of work is attributable to a single application, it is nearly impossible to break that work down and distribute resources across the work according to its priority. In this case, organizing a workload according to accounts might be more reasonable. For example, the DBA might identify the user groups, departments, or divisions performing the work and assign priorities based on the needs of those groups. These priorities would determine the allocation of resources to them.

More important workloads should receive a greater share of system resources so that they process before others. In addition, workloads expected to process quickly should be set at a higher priority, while slower ones should be set at a lower priority.

In this way, administrators use not only a technical but also a business orientation to determine workload selection when creating new workload recommendations using Teradata WA.

Teradata WA performs analysis using the following three fundamental criteria, with each representing a greater level of granularity:

  • “Who” is requesting the work?
  • “What” are the request’s performance characteristics?
  • “Where” is the request targeted?

“Who” is the account or application that initiated the queries, “what” are performance-related characteristics of the queries, and “where” is the database against which the queries run. Based on these criteria, some obvious associations can be made between similar queries to assign them to the same workloads for the sake of efficiency. However, for best results, look next at the query components and characteristics at finer levels of detail, to ensure they are appropriately for greatest optimization.

The data Teradata WA uses to tune workloads for management according to criteria and granularity level is described in the following table.

Comparison of Granularity Levels and Classification Criteria
Granularity Level Type of Criteria Includes
Low Who
  • Account
  • Account String
  • Application
  • Client IP Address
  • Client ID for logon
  • Profile
  • Username
  • QueryBand
Medium Who, What The Who criteria plus:
  • Type of statement (SELECT, DDL, DML, or Collect Stats), individually or in combination
  • UtilityType
  • AMP usage (one or a fewer at most)
  • Minimum and maximum estimated row counts, including final row counts
  • Minimum and maximum estimated CPU time
  • Datablock Selectivity
  • IPE
  • Memory Usage
  • Maximum Step Time
High Who, What, Where The Who and What criteria plus:
  • Databases
  • Tables
  • Views
  • Macros
  • Stored Procedures
  • Function
  • Method