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.