Killer queries: Track them, find them, fix them

Viewpoint
Teradata Viewpoint is Teradata's strategic and innovative SOV (single operational view) for Teradata DB, Aster, and HDP Hadoop systems management and monitoring that enables Teradata's Unified Data Architecture (UDA).
Teradata Employee

Killer queries: Track them, find them, fix them

With increased workload complexity, it is common for a system to run in full capacity. There is always a need to find resource consuming sessions and bad running queries. The intention of this article is to discuss a few ways to identify resource consuming sessions or bad running queries using Teradata Viewpoint.

The quickest way to identify resource consuming session is to look at Top Session By menu. In the preference section of Query Monitor portlet, click display tab and select Top Session Graph. The Top Session By menu will then be displayed on the Query Monitor portlet: select the appropriate metric in the drop down box and the bar next to it shows sessions with the largest value. The wider the bar, the more resources the session is consuming. Mouse over on the top of the bar to see the session value and click on the bar to see the session details. (See Figure 1).

Figure 1

Another way to monitor resource consuming session is to select the desired metric in the Configure Column option of Query Monitor and then sort the column in descending order. For example: to identify the top CPU consuming session, in the Configure column option select CPU USE. Sort the CPU USE column in descending order. (See Figure 2). The Set button in Configure Column can be used to set a threshold value. Queries consuming resources above the threshold value will then be shown in red.

Figure 2

If it appears that there are too many queries in Query Monitor to look at and you only want to look at the queries that exceeded certain value, in the Preference option specify the threshold value in criteria tab and then select By Session - My Criteria to see only those queries that exceed the threshold. 

To identify the bad running queries for tuning, in the preferences section of the Query Spotlight portlet select appropriate criteria and define the threshold for bad queries. All the queries that exceed the threshold will be displayed in Query Spotlight. Select Duration in Configure Column and sort by it. Click on the session and start analyzing it.

Below are some tips to analyze queries in the Query Spotlight portlet.

  1. The width of the time bar indicates the total elapsed time for a query. To zoom in to a step, adjust the width of the time bar.
  2. The snapshot tab shows a snapshot of the metric value for a point in time indicated by the slider bar.
  3. The moving slider bar will refresh the stats in the snapshot and when the metric value is in red, it indicates that at that point in time  themetric value exceeded the threshold.  (See Figure 3). Enabling Rewind at that point in time and using other monitoring portlets will provide a holistic view of the system situation at that point in time.

Figure 3

In summary, there are many ways to identify bad running queries or top resource consuming queries using Viewpoint. Top session By, My Criteria and the Configure Column option in Query Monitor can be used to identify resource consuming sessions running on the system at that point in time, and using the Query Spotlight portlet along with the Rewind feature can be used to identify and troubleshoot any resource consuming sessions or bad/long running query in the past.

43 REPLIES
Enthusiast

Re: Killer queries: Track them, find them, fix them

Neat article and nice quick tips to identify bad queries on the fly while monitoring the box.
Teradata Employee

Re: Killer queries: Track them, find them, fix them

Thanks Subbu !!

Re: Killer queries: Track them, find them, fix them

Good one. Thanks for sharing the information.
Enthusiast

Re: Killer queries: Track them, find them, fix them

"Queries consuming resources above the threshold value will then be shown in red"- What is the threshold value that we need to put here. Generally we use the Request CPU to monitor the Viewpoint.
Enthusiast

Re: Killer queries: Track them, find them, fix them

the CPU use shows the value as percentage. Could you please elaborate about this value.
Teradata Employee

Re: Killer queries: Track them, find them, fix them

REQ CPU shows number of CPU seconds used by the current request so far. This is a good metric when we want to see query that consumed maximum amount of CPU so far.

CPU USE is the CPU used during the last sample period divided by the total available CPU (system-wide) during that sample period. This means it is Percent of the total amount of available CPU used by the query. This is a good metric to identify query that is currently consuming high CPU.

It is possible for a query to have high REQ CPU but low CPU USE. Usage of REQ CPU and CPU USE depends on what we are trying to achieve. I usually use REQ CPU when I try to find out bad running queries for tuning purpose. But use CPU USE when my system is running high on CPU usage and need to identify session that is currently consuming high CPU.

Let me know if you have any question. :-)
Enthusiast

Re: Killer queries: Track them, find them, fix them

Thanks for the answer !!

Re: Killer queries: Track them, find them, fix them

Good tips on using viewpoint to track the queries.
Teradata Employee

Re: Killer queries: Track them, find them, fix them

Thanks Surya !!