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

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

Thanks Satheesh !!
Enthusiast

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

Hi Shrity, GSC pointed to a knowledge article KAP2B5BF6 which had some explanation on why the CPU Usage is > 100%. I thought I'll let you know about this. Thanks.
Enthusiast

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

Hi Shrity,

Very nice article!

Anyway, how about a system with high Node CPU Skew? Which metrics do we need to check to be able to identify suspect queries?

I appreciate your response.

Thanks,

Cesar

Teradata Employee

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

Thanks Cesar, That's a good question. One way I could think off is by identifying if there is a Node CPU Skew. This can be done by looking at System Health portlet, if you see high Node CPU Skew you can go to Node Resource portlet to find the Node that is skewed and then drill down to skewed node to see the Vprocs that is skewed. In Viewpoint 14.10 we have HOT AMP report in query monitor, the AMP that you see skewed in Node Resource portlet should be listed in HOT AMP report. You can drill down to the AMP and see the session that are logged on to the AMP and find the session that is skewed.

Enthusiast

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

Hi Shrity

What should I do for system slowness issue. I also did system throttle settings.But sometimes delays goes so high. What should a DBA do for this? I consider Impact CPU, CPU use ,  Req CPU parametrs for responsible queries but I want to know how to use them step by step.

This is one instance when system slowness issue comes

358   339             26         0       308       0       3        19      2

All    Not idle      Active    Block delay  abort  resp    idle    parse

how to investigate this slowness issue, Would you plz throw some light how it can be resolved.

Thanks in advance

Teradata Employee

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

I don't have a straight forward answer to resolve slowness issue. Neither I think there is any rule of thumb. System slowness can be for multiple reasons, few that I can think of..

1. One Query or set of queries consuming lot of resources 

2. Several queries getting blocked (This is not true in your case)

3. Node parallel efficiency is impacted (Node resource portlet can help understanding this)

4. Bad TASM rules 

5. System is in flow control.. Out of AWTs..

6. etc..

What I see is that you have only 26 active sessions but 308 sessions in delay queue.. You may start with checking if I am completly utilizing my system resource, using system health portlet. If not, then revisit your TASM rules so see why you have 308 queries in delay queue. If yes, then what are the active queries doing, are there any bad long running queries hogging up all the resources (if system health shows high CPU utilization check for High impact CPU, High CPU Use..)...  Other than that I can't say much without looking at your system.. Sorry I was not of much help here..

Thanks

Shrity

Enthusiast

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

Thanks Shrity so much.

Would u plz tell me what is requests (count) in Session Info in query monitor overview tab. Is it also related to slowness issue? Means culprit queries can have high requests count? 

Teradata Employee

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

Requests (count) in Session Info in query monitor is number of requests (Queries) submitted by the session. If a session is consuming high resource and request count is high you know why resource consumption by that session is high, but if a session is consuming high resource and request count is low 2 or 3 you may want to look at those queries.

Enthusiast

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

Is there a way to display the query based on SQL Text. There are some adhoc queries run by users which we want to kill as soon as they come to the system. We can't track DBQL because it only registered in DBQL after the completed execution. Is there a way in viewpoint we can highlight them based on sQL text pattern match instead going through each SQL.

Teradata Employee

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

There is no way you can highlight query based on SQL txt. If you could attach a queryband to the query then you can either filter the query using workload management rule or sort it in the datagrid of query monitor portlet.