Spool, Perm, temp space management

NOTE: There is no concept of table space in Teradata.

There are three different types of space defined in Teradata:

 

Perm Space Spool Space Temp Space
Permanent space is the space which is utilized for all databases. It is used to hold all the data of the databases. This space isn’t pre-allocated.  NOTE: Teradata’s Spool and Temp Space is the perm space that is not currently being used. Spool space is used to hold data for Intermediate Query results. Once the query is complete, the space is released. It only holds data. It is active up to the current session only. Spool space is same as that of the root, unless specified. If there is no limit defined for a particular database or user, limits are inherited from parents. In theory, a user could use all unallocated space in the system for their query. The best way to estimate spool usage is to run explains on all of your queries. Just remember that this is an estimate of spool usage not the actual spool usage. Temporary space is the amount of space which can be used to create temporary tables – either volatile tables or global temporary tables. Data is active up to the current session only. Tables created in Temp Space will survive a restart. Temp space is the unused permanent space.
The amount of permanent space is divided by the number of AMPs. Whenever per AMP limit exceeds, an error message is generated. Spool space is divided by the number of AMPs. Whenever per AMP limit exceeds,
the user will get a spool space error.
Temp space is also divided by the number of AMPs

 

The perm space can be further divided into:

 

Maxperm PeakPerm Currentperm
Maxperm is the total permanent space specified for that user/database.In other words, MaxPerm is the total amount of bytes available for data storage in a database. PeakPerm is the maximum permspace ever utilised after it has been last reset.In other words, PeakPerm is the peak amount of bytes that have ever been stored in a database. Currentperm is the current space which is being utilized by database/user/table.In other words, CurrentPerm is the actual amount of used bytes in a database.

Leave a Reply

Your email address will not be published. Required fields are marked *