15.00 - Reserving Disk Space for Spool - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Reserving Disk Space for Spool

Teradata Database uses spool space as temporary storage for result rows that are returned for user requests. To ensure that space is always available for spool, you should consider reserving about 15% to 20% of the total available disk space as spool space. For example, you can create a database with a name like spool_reserve, that is not be used to contain tables:

     CREATE DATABASE spool_reserve AS
     PERM = 2000000*(HASHAMP()+1)

where the specified value for PERM space is roughly 15% to 20% of the total available disk space for the system, which is based on the multiplier of 2,000,000 bytes.

This specification uses the constant expression 2,000,000 * (HASHAMP()+1) to calculate the number of AMPs in the current system and then scales the PERM space for the spool_reserve database to that size.

The application of the (HASHAMP()+1) constant expression to specifying disk space requirements is particularly useful for spool space, because the more AMPs in a configuration, the more thinly spread the data, which means that more spool space is required per AMP.

You should always consider defining the spool space for a database, user, or profile using a constant expression to scale the amount of spool space assigned based on the number of AMPs on the system.

For example, you might specify the amount of spool space for database nivv as in the following CREATE DATABASE request.

     CREATE DATABASE nivv AS SPOOL = 2e5 * (HASHAMP() + 1);

Note: After creating the database, the spool space does not change if you add more AMPs to your system because the size is based on the number of AMPs in the system at the time the database, user, or profile is created, not on the current configuration.

Also be aware that spool space is the only disk space requirement for profiles that you can specify using a constant expression. You can use constant expressions to specify permanent, temporary, and spool space for databases and users.

This guarantees that data tables never occupy more than roughly 80% to 85% of the total disk space. Because no data should be stored in the spool_reserve database, Teradata Database can use its permanent space as spool space when necessary.

Data warehousing applications should consider reserving still more of the total disk space as reserved spool space because their SQL requests tend to generate larger spool files. Tactical and OLTP applications can reserve less reserved spool space because their requests tend to generate smaller spool files.