Planning New Workloads for Analysis

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 1.

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