Suspect Query Formulas | Query Formula Approaches | Teradata Viewpoint - 17.10 - Suspect Query Formulas - Teradata Viewpoint - Teradata Workload Management

Teradata® Viewpoint User Guide

Product
Teradata Viewpoint
Teradata Workload Management
Release Number
17.10
Release Date
October 2021
Content Type
User Guide
Publication ID
B035-2206-101K
Language
English (United States)

Formulas and Default Settings

Queries are flagged as suspect according to the suspect query formulas listed below. The tolerance value controls how many queries are flagged as suspect.

When the tolerance is zero, any queries that consume 1 AMP CPU second or greater and exceed the thresholds are flagged as suspect. It is normal for queries that consume smaller amounts of resources to exceed these thresholds and, increasingly so, on larger systems. The tolerance value prevents these smaller consuming queries from being flagged as suspect if they are not too far above the threshold in proportion to the resources they consume. The larger the Teradata system, the larger the tolerance values need to be to make sure queries that are performing normally are not identified as suspect.

Metric Formula Suspect Query Formula Default Threshold Default Tolerance
CPUSkew 100 * (1 - (AMPCpuTime/(MaxAMPCPUTime * NumOfActiveAMPs))) CPUSkew > 100 - ((100 - Threshold) * AMPCPUTime)/(Tolerance + AMPCPUTime) 40 10
IOSkew 100 * (1 - (TotalIOCount/(MaxAmpIO * NumOfActiveAMPs))) IOSkew > 100 - ((100 - Threshold) * TotalIOCount)/(Tolerance * 20,000 + TotalIOCount) 40 10
PJI (AMPCPUTime * 1000)/TotalIOCount PJI > (10 * Tolerance)/(AMPCPUTime - Tolerance + 1) + Threshold) AND AMPCpuTime >= Tolerance 3 10
UII TotalIOCount/(AMPCPUTime * 1000) UII > (200,000 * Tolerance)/(TotalIOCount - (Tolerance * 1000) + 1) + Threshold) AND TotalIOCount >= Tolerance * 1000 20 10

Approaches to Obtaining an Optimal Tolerance Value

There are two different approaches to obtaining the optimal tolerance value.

With the first approach, you can use a visualization tool to scatter plot queries by CPU Skew or PJI versus AMPCPUTime, or IO Skew or UII versus TotalIOCount for a single day's worth of DBQL data. Then graph the corresponding suspect query formula on top of the scatter plot. Queries above the suspect query line are flagged as suspect. Adjust the tolerance in the suspect query formula to shape the line so the visual outliers are above the line, but the majority of queries are below the line.

With the second approach, you can run a SQL query that counts the number of rows that exceed the value of the suspect queries formulas for a days worth of DBQL data and adjust the tolerance values so that an actionable number of queries are returned.