15.10 - Planning New Workloads for Analysis - Teradata Workload Analyzer

Teradata Workload Analyzer User Guide

prodname
Teradata Workload Analyzer
vrm_release
15.10
category
User Guide
featnum
B035-2514-035K

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

  • 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 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 Table 6.

     

    Table 6: 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