Most of us are aware of AMP worker tasks, and some of us are even obsessive about not running out of this finite resource.  But that’s on the AMP.  What about the tasks supporting user work on the parsing engine (PE)?  Should we be just as vigilant about what’s happening with tasks at the PE level?

Not really.  Here’s why.

Each parsing engine has a pool of tasks called SQLDriver tasks that perform PE activities.  There are 128 of these tasks per PE, and similar to AMP worker tasks (AWT), they are initiated at startup time and stay active until the database shuts down.

You do not need to be concerned about running out of SQLDriver tasks, as only one will ever be used by a single session, and a PE is limited to 120 sessions.  There are a few additional SQLDriver tasks (8 more than the number of sessions allowed) for special internal purposes.  I am not aware of any issues involved in running out of SQLDriver tasks, so you can cross them off your monitoring list.

There a couple of interesting differences between AWTs and SQLDriver tasks I want to share with you:  

  1. When a transaction or a request is aborted due to an error or a user-generated abort, any active AWT running on behalf of that transaction or request will get terminated. Subsequently, a new AMP worker task will then be started to replace the one that was killed.  However, at any point in time, there could be fewer AWTs running on an AMP than the system setting specifies.  SQLDriver tasks, in contrast, never get terminated until a database restart.
  2. A session has access to only one SQLDriver task at a time.  All CPU work performed on the PE for one session and all of its requests is single-threaded through that one task.  This is different from AMP work, where all-AMP query steps have at least one AMP worker task active on its behalf for each AMP in the configuration.   

 

When you look at AMP CPU seconds for a request you are seeing an accumulation across all AMPs.  Because AWTs may be accumulating time on different CPUs in parallel, the wall-clock time for getting the AMP work could be less than the reported CPU seconds. 

On the other hand, PE CPU seconds usually take a longer amount of wall-clock time to consume than their reported CPU seconds because one SQLDriver task on a single CPU on one node is doing all the PE work.   

Discussion
abhijitvyas 4 comments Joined 08/06
28 Nov 2012

Thanks Carrie for the details and as always its useful. is this recorded any where in Resusage like SAWT & SPS for AWTs ?

sumank 1 comment Joined 02/12
29 Nov 2012

Hi Carrie,

I want the list of tables not being used since 3 months in porduction. In over environmnet the Last Access time stamp and Access count is not enabled so please help me how to get the unused Objects.

suman

carrie 385 comments Joined 04/08
30 Nov 2012

Abhijit,

The ResUsageSAWT table does not contain anything about parsing engine tasks, as its focus is AMP worker tasks exclusively.

The SPS table does produce information about priority scheduler activities on the parsing engine. Look for VprType = PE. I have not validated this, but the NumTasks field in the SPS table may represent the number of PE tasks active at the end of the logging interval, or at least include them among other things. Don't quote me on that though.

The simple thing to assume is that number of SQLDriver tasks being used at any point in time will equal the number of active sessions on that PE. You really do not have to monitor their levels.

Thanks, -Carrie

carrie 385 comments Joined 04/08
30 Nov 2012

Suman,

The Access Count functionality was designed to provide exactly the information you are asking for. If you choose not to use it, the only alternative I can think of is to start collecting object level DBQL data for "everything" and then parse the logged data for all the tables accessed, and compare it against a list of defined tables.

Why don't you ask this question on the Teradata technical forum and see if anyone has come up with an approach for doing this.

Thanks, -Carrie

danR 1 comment Joined 01/13
29 Jan 2013

Hi Carrie: The AWT , i'm assuming, has a control record ? - If so, is there documentation regarding the AWT 'control' record (for lack of a better word) - with explanations for each field.

danR

carrie 385 comments Joined 04/08
30 Jan 2013

Hi Dan,

AMP worker task work type definitions are not held anywhere in the database and there is no control record that describes them as far as I know. I'm assuming that that type of detail is considered internal information, and as such, is not externalized. However, you can look in the AMP worker task orange book for detail about these work types, if you have access to the orange book repository. It is documented there.

When you say "explanations for each field" I'm guessing you mean "for the different work types". Hopefully that is what you meant.

Thanks, -Carrie

jd3714@att.com 1 comment Joined 04/13
23 Apr 2013

Carrie -
So the PE provides an upper limit of 120 simultaneous user sessions executing a query (independent on how it is farmed out). Is this standard - what about a little unconventional setup - what about active vs inactive (idle) sessions - does this make a difference?

carrie 385 comments Joined 04/08
24 Apr 2013

Yes, the limit of 120 sessions per parsing engine is standard.  I don't know of any sites that have attempted to raise that limit.  Most sites that require a larger number of sessions add more parsing engines.  
 
Both active and inactive sessions will be accounted for in that 120 sessions per PE.   A session will be holding a session slot from its logon to its logoff, whether it has any active requests doing work or not.  Most of the time when sites express an interest in getting more sessions, they do so because so many of the ones they have are idle so much of the time.
 
Thanks, -Carrie

You must sign in to leave a comment.