A Quick Way To Solve The Teradata Spool Space Problem | DWHPRO

A Quick Way To Solve The Teradata Spool Space Problem

Teradata Spool Space Introduction

What is Teradata Spool Space?

Spool Space is a workspace, which Teradata uses to store temporary result sets and final result sets of a request. Spool Space is only used temporarily and released when it is no longer needed or the session ends. There are 4 types of Spool Space which we will explain at the end of this article.

How is Spool Space assigned to a User?

The spool space can be assigned when a user is created:

CREATE USER DWHPRO_ADMIN FROM DWHPRO AS
PERMANENT = 800000000 BYTES,
SPOOL = 200000000 BYTES,
PASSWORD = ******;

The assigned spool space represents the upper limit for this user. The spool space is distributed evenly among all AMPs. teradata spool space

I get the Error Message “No more spool space in user…” although there should be enough space available. Why?

Since the total spool space is divided by the number of AMPs, each AMP has only a fraction of the space available.

If a query is skewed, it can happen that an AMP does not have enough space available and the request cannot be executed.

As a general rule, the more AMPs a system has, the greater the risk.

How is Spool Space managed internally by Teradata?

It is important to understand that Teradata never holds data and spool space on one cylinder at the same time. Therefore, cylinders are released immediately as soon as they are no longer needed as spool space.

How can I find out how much Spool Space my User has?

SELECT * from DBC.DATABASES
WHERE DATABASENAME='DWHPRO';

Why is there even a Spool Space Limit?

This limit is needed to cancel requests that have a too high consumption of resources. Even if high spool space usage does not always mean a bad query, the correlation between bad queries and high spool space usage is high.

What “no more spool space” Scenarios are there?

One reason might be that there are no more free cylinders on the Teradata system. This must be prevented at all costs. Usually, a separate database is created to “reserve” the space to prevent this.

“No more spool space” can also be caused by too many parallel sessions of the same user.

Finally, “no more spool space” can also be triggered by skewed queries or volatile tables.

4 Types of Teradata Spool Space

  • Intermediate
    Will be released as soon as it is no longer needed. Intermediate Spool Space is required by derived subqueries, for example. The execution plan provides information about when spool space is released.
  • Output
    The Output Spool Space holds the result of an SQL request.
  • Volatile
    Used for volatile tables.
  • Persistent
    Persistent Spool Space survives a system restart or the crash of a node.

Solve The Teradata Spool Space Problem – Failure 2646 No more spool space

As already mentioned, “no more spool space” can be triggered by different scenarios. Here are some ideas on how to avoid this problem:

Solve The Teradata Spool Space Problem – Failure 2646 No more spool space

  1. Ensure up-to-date statistics

    Current statistics are important so that the optimizer does not copy unnecessarily large amounts of data between the AMPs.

  2. Choose a good Primary Index

    Choose a primary index for your tables that distributes the rows evenly across all AMPs.

  3. Prevent “no more spool space” on system level

    Prevent the system from getting into a situation where spool space is no longer available (e.g. through a spool reserve database).

  4. Release Spool Space as soon as possible

    Drop Volatile Tables immediately when they are no longer needed.
    Often you simply rely on this to happen automatically at the end of the session.
    This unnecessarily blocks spool space (but also cylinders that could be used as permanent space, for example).

Teradata SQL Tuning – 6 Golden Rules you must never forget!
Winning Tactics For Teradata Sample Statistics
DWH Pro Admin
 

>