Sizing Needs | Database Design | Teradata Vantage - 17.10 - Sizing Spool Space - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

Vantage uses spool space as temporary storage for result rows that are returned for user requests. Because spool space is a form of temporary space, it is frequently overlooked in capacity planning, yet it is critical to the operations of the database. Spool space needs vary from table to table, user to user, application to application, and with frequency of use. Very large systems use even more spool than smaller systems. Spool rows have a maximum length of approximately 1MB.

Spool falls into these categories:
  • Intermediate
  • Output
  • Persistent
  • Volatile

Intermediate Spool Space

Intermediate spool results are retained until no longer needed. You can determine when intermediate spool is flushed by examining the output of an EXPLAIN. The first step performed after intermediate spool has been flushed is designated “Last Use.”

Output Spool Space

Output spool results are the final information returned for a query or the rows updated within, inserted into, or deleted from a base table. The length of time output spool is retained depends on the subsystem and various system conditions, as described in this table:

Subsystem/Condition When Output Spool Is Released
BTEQ Last spool response.
Embedded SQL The open cursor is closed.
  • ERQ received
  • Function terminated
Session terminates asynchronously due to any number of conditions, including the following.
  • Job abort
  • Timeout
  • Logoff
At the time the termination occurs.
System restart At the time the restart occurs.

Persistent Spool Space

When Redrive protection is enabled, the database stores responses for sessions that participate in Redrive in persistent spool tables. Persistent spools are not deleted following a Teradata restart or node failure. Persistent spools are retained until the SQL request completes and the application has fully received the response. For details about Redrive protection, see Teradata Vantage™ - Database Administration, B035-1093.

Volatile Spool Space

The system uses volatile spool space for volatile tables. This is necessary because volatile tables do not have a persistent stored definition.

Sources of Spool Space

Spool space is taken only from disk cylinders that are not being used for data. Data blocks and spool blocks cannot coexist on the same cylinder.

When spool is released, the file system returns the cylinders it was using to the free cylinder list.

Spool Limits

If you find that queries do not run because they run out of spool space, then increase the spool assignment for the user or database having the problem using the MODIFY USER or MODIFY DATABASE statements, respectively. For the syntax and usage notes for these statements, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. If out of spool space errors are occurring due to uneven data distribution across AMPs, use global space accounting to avoid some of these errors. Global space accounting dynamically allocates and deallocates space to and from AMPs as needed while the underlying tables of the database or user are modified. For more information about global space accounting, see About Global Space Accounting.

The maximum size for a spool row is approximately 1MB. This larger row size enhances DML operations that are limited by small spool rows.

The amount of spool space allocated to each user and database is assigned at CREATE USER or CREATE DATABASE time.

Guidelines for Allocating Spool Space

Unless you reserve a pool of spool space, the space available for spool tends to disappear quickly. When applications consume all the spool space allocated for a system, processing halts.

A large Teradata customer in the retail business uses the following method as a guideline for allocating spool space:

  1. Create a special database to act as a spool space reservoir.

    Allocate 20% of the total user space in the system for this database.

  2. Assign roughly 0.25% of the total space to each user as an upper limit, ensuring that each receives at least as much space as the size of the largest table they access concurrently.
Consider the following factors to perform finer tuning of database, user, or profile spool allotment.
  • Query workload types.

    Decision support queries generally require more spool than OLTP and tactical queries.

  • Average spool use per user.
  • Number of concurrent users.
  • Number of concurrent queries permitted for any one user.

    Spool space is cumulative per user.

  • Query size

    The smaller the query, the less spool space required. If a particular user only performs small queries, then allocate less spool space to that user.

    If a user performs many large queries, then allocate more spool to that user. With the exception of runaway queries, allocating more spool space to a user is never harmful as long as system resources are not wasted.

  • Database size

    The more AMPs in the configuration, the more thinly spread the data, so the more spool is required per AMP. Because of this, you should consider defining the spool space for a database or user using a constant expression to scale the amount of spool space assigned based on the number of AMPs on the system.

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

    where the specified value for PERM space is roughly 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.