Spool failures due to concurrency

Database

Spool failures due to concurrency

SpoolFailures_print.PNG

I'm facing spool failures due to concurrency of the queries , for an application ( Business objects - reporting queries)

 

We have a limit of 5 queries can run concurrenty in TASM.

When one query is spooling out ( 13TB allocated at profile level) , at times, some of the queries which runs parallelly are also getting spooled out at the time when bad query spooled out.

Most of the time, this query is highly skewed. but when spooled out, tuned queries also getting aborted with spool issue.

 

Question:

Generally when this situation will happen? is it happening because spool space is not releasing immediately after the spool out ?

 

We have implemented the TASM rules based on the estimated processing times. is there any things it can be resolved through workload management?

 

--Kiran

 

 

Tags (1)
4 REPLIES
Teradata Employee

Re: Spool failures due to concurrency

If the concurrent queries are run under the same userid, then the spoil for all concurrent queries is added together to check against the spool limit. The first one to get to the limit will be aborted. If the large consumer keeps consuming more spool after that, more queries under the same id could also get aborted.

To avoid this issue, run the tuned queries under a separate userid.

Re: Spool failures due to concurrency

Thank you very much for the prompt response.

 

Unfortunately , as this is a BO application, we don't have a control over execution of the queries. Multiple users can run the adhoc queries during the business window. As a long term, as you said,  we are planning to split to use multiple IDs, but with the course of time, we may fall into the same situation again as the # of users grow and create adhoc reports.

And I dont think there is a way to create a filter on the highly skewed queries ...

 

Kiran

 

 

 

 

 

Junior Supporter

Re: Spool failures due to concurrency

This also may be due to one query creating very skewed spool. Check on viewpoint if any step shows skew >90% and try to find the skewed join and tune it. So, you get spool issue either when spool is realy less and others have mentioned or when spool is skewed.

Thx !  Samir

Teradata Employee

Re: Spool failures due to concurrency

In BO (and in most tools) it is possible to provide QueryBands to identify the user, report, ... . These are placed into the DBQL log for later analysis and are also available for use in TASM/TIWM criteria for filters, classification,... .

 

In Teradata 16.10, there are new space management parameters which allow one to be more flexible about space usage for skewed queries. If the query is very badly skewed, this may not help but if it is already capable of running sucessfully by itself, it may be possible to give some flexibility on the skewed use of spool.