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).
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.
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
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.
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.