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

Teradata® Viewpoint User Guide

Product
Teradata Viewpoint
Teradata Workload Management
Release Number
17.10
Published
February 2022
Language
English (United States)
Last Update
2022-06-28
dita:mapPath
lks1628075605455.ditamap
dita:ditavalPath
tky1501004671670.ditaval
dita:id
B035-2206
lifecycle
previous
Product Category
Analytical Ecosystem

Formulas and Default Settings

Queries are flagged as suspect according to the suspect query formulas listed here. 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 far from 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 before 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 before the line, but the majority of queries are after 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.