Ways the Analyst Pack Tools Work Together - Teradata Index Wizard

Teradata Index Wizard User Guide

Teradata Index Wizard
Release Number
English (United States)
Last Update
Product Category
Teradata Tools and Utilities

Ways the Analyst Pack Tools Work Together

  • Identify the queries or workload to be analyzed for performance tuning. Logging onto Teradata DBQL is one easy way to selectively capture SQL queries at query run time.
  • Capture query plans into the Query Capture Database (QCD). For individual queries or a small set of queries, use the Teradata Visual Explain Launch QCF feature. For bigger workloads, the Teradata Index Wizard Workload Definition feature can be used to define the workload and capture query plans for all queries in the workload.
  • Optionally, export the captured query plans, system cost parameters, statistics, data demographics, random AMP samples, and object definitions from the production or target system to a test system using Teradata System Emulation Tool. This step is not necessary, but enables off loading of the analysis activities from the production to the test environment.

  • Find out the statistics and data demographics for the tables involved in the workload using Teradata Statistics Wizard. Statistics recommended by TSWiz for the tables/columns in the workload can be applied immediately. At this point, you can optionally recapture the plans for the workload and compare them using Teradata Visual Explain's query or bulk compare feature. Doing this allows you to visualize and measure the improvements achieved by implementing the Teradata Statistics Wizard recommendations.
  • Perform index analysis for the workload using Teradata Index Wizard. In the case of analysis on a test system, the index recommendations can be validated back on the production system using the Validate Recommendations feature. This will simulate the indexes without actually creating them, and produce a query plan as though the indexes were present. Validation also collects sampled statistics as part of the process, thus ensuring up-to-date statistics are available for the proposed indexes.
  • Analyze the output of the Teradata Index Wizard reports to understand the recommendations' impact on performance (estimated performance improvement), and cost to implement (in terms of disk space and creation time). Utilize Teradata Visual Explain to compare pre- and post-recommendation plans (post recommendation plans are automatically generated and stored for each query in the workload during Index Validation). Implement the recommendations on the production system to achieve the performance improvement desired, or alternatively, use the what-if analysis mode to identify potential performance improvements of your own proposed secondary indexes.