Why do I get “No more spool space” error?
What is spool space?
Spool space is a temporary area, that can be used to store the part-results during query processing, as well as volatile tables. All free space in the database that are not allocated by PERM data, technically can be used for spool area, since a PERM data does not want to allocate that area.
Each database users may have a “spool limit” that restricts the user to allocate more spool area at a time, than its limit. Keep in mind that all active sessions of a username must share the spool limit together.
Teradata is a massive parallel system, therefore the spool limit must be interpreted on AMP level:
Eg: 100AMP system, a user with 10G spool limit means: 100M spool/AMP
What is spool space limit good for?
This limitation method is a quite simple way to cut those queries from the system that would suck too much resources. There is no exact relationship between high spool usage and ineffective query, but statistically the correlation is high.
Practically: a bad query is being kicked off before is consumes too much resources unnecessarily.
No more spool space scenarios
System ran out of spool space
If many “big spool limit” users run high spool queries parallel, then this rare situation can yet occure.
Multiple session of the user are active together
Improper execution plan
- “Duplication to all AMPs” of a non-small set of records
The root cause is typically missing or stale statistics. Either system thinks that much less records will be duplicated than the real (sometimes billions of records get in this kind of spools), or knows this exactly, but on the other branch of the query there are more low quality estimations, and this execution seems to be cheaper.
- Redistribution of records by a hash that causes skewed distribution
Check the corresponding blog post: Accelerate skewed joins
- Retrieve huge amount of records into spool (locally or redistributed onto the AMPs)
Specific query structures imply this execution, like: join to a view that “union all”-s big tables.
Increasing the spool limit will not solve the problems in the most cases.
Too big task
This is the only case when raising spool limit is the solution. But first you have to understand that the task is really big. PRISE Tuning Assistant is a good tool for identify this in a minute.