Considerations for Assigning ASE Variables to Different Workloads - Teradata Database

Teradata Database Administration

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Each ASE assignment depends on the type of usage being performed by the user ID. This has implications related to user ID assignment.

In general, workloads can be broadly grouped into three categories as follows:

  • Multisession/Multirequest
  • This workload can be identified by work typically done by MultiLoad, FastLoad, TPUMP or multisession BTEQ. These types of activities are normally used for database maintenance. Each session used handles multiple requests over time. The workload for this type of work tends to be more predictable and stable. It runs regularly and processes the same way each time it runs.

  • Single Session, nontactical
  • This workload is typically initiated through a single session BTEQ, SQL Assistant, MicroStrategy, or other query-generating tool. Ad hoc users, or single session BTEQ jobs in the batch process can generate this type of activity. The single session may generate one or many requests. The requests may be back to back, or there may be hours of idle time between them. Typically, the requesting user has very broad and informal response time expectations.

  • Single Session, tactical
  • This workload is similar to the Single Session workload category except that there is typically a very clear definition of response time and the response time requirements normally range between less than a second to a few seconds.

    The following ASE variables are to be used for each of the workload categories listed above along with the rationale for selecting the ASE variables.

  • Multisession, Multirequest
  • For this workload, usage information need not be captured at the request level. Workloads in this category can

  • Processes the same request over and over again across the multiple sessions it establishes (such as TPUMP and multisession BTEQ).
  • Generate multiple internal requests that are not easily correlated to specific user generated activity (as is the case with MultiLoad and FastLoad).
  • As a result, capturing usage detail at the request level typically does not provide especially meaningful information. Therefore, the recommended standard is to capture usage at the session level using the '&S' ASE variable. The account string for User Ids performing this workload category would have the following format:

    Account String Format: $XX$_&S

    Length: 12-15 characters (depending on PG length)

    Capturing session level information for this workload category provides several benefits, including:

  • All usage for a given job can be more easily captured. Furthermore, the job level usage can then be grouped to associate all batch processing to an application.
  • All usage for a given job step can be obtained. This can facilitate performance analysis for batch processes.
  • Session usage within a multisession utility can be better analyzed to determine the optimal number of sessions to log on to the system.
  • Single Session, nontactical
  • For this workload, request level usage detail is desired. This type of activity is typically the most difficult to manage and control in a mixed workload, data warehouse environment. They also typically represent the greatest opportunity for optimization. Although request level detail requires some minor additional overhead to capture, the benefits of gaining additional visibility into the impact of each request outweighs the increased overhead in data collection. The account string for user IDs performing this workload category would have the following format:

    Format: $XX$_&I

    Length: Up to 128 characters

    Capturing request level information in this manner has numerous benefits, including:

  • Usage associated with each SQL request can be identified. By applying specific metrics such as total CPU used, total IO used, CPU skew percent, Disk to CPU ratio, and so forth, problem requests can quickly and easily be identified and addressed.
  • Request level usage detail can be correlated to SQL statements in DBQL to greatly simplify performance-tuning efforts. DBQL captures the date and time of the request as well as the session and request number of the request.
  • Performance tuning can become much more quantitative and definitive by comparing usage statistics for alternative query approaches. Capturing the consumption at the individual request enables this benefit.
  • Usage can be accumulated to the session level to provide same level aggregations and analysis to multisession, multirequest processing. As such, the same benefits can also be achieved.
  • Single Session, tactical
  • For this workload, high-speed performance and minimal response time are the primary objectives. Even if the Teradata Active EDW is not currently servicing this type of request, it is important to account for this type of work within the standard. Typically, this workload tends to be very predictable in nature with queries typically designed to be single AMP retrievals. For this workload, capturing information at the request level is unnecessary for two reasons. First, the transactions are well defined and repeated over and over again. Second, the additional overhead required to record usage for each request would represent a meaningful portion of the overall work performed on behalf of the transaction. In other words, the additional overhead could materially impact request response time.

    As a result, the account string for this workload can, as one option, target usage detail at the session level. The assumption in this case is that applications requiring high-volume, low response time requests will take advantage of session pooling to avoid the overhead of continually logging on and logging off. The account string for User Ids performing this workload category would have the following format:

    Format: $XX$_&S

    Length: 12-15 characters (depending on PG length)

    Since this is the same ASE strategy as employed for the multisession, multirequest workload, all the same benefits would accrue. In addition, as it pertains to this particular workload category, the following benefits could also be achieved:

  • Usage by session could assist in determining the optimal number of sessions to establish for the session pool.
  • CPU and/or IO skew by session could help identify possible problems in the data model for the primary index retrievals.