fbpx

Teradata Spool Space Introduction

What is Teradata Spool Space?

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

How can we increase the spool space in Teradata?

It is important always to have a spool reserve. Options to make more Teradata Spool Space available to the system are compressing common values, removing fallback protection, and removing indexes. Finally, there is always the possibility of a system upgrade.

What should I do if my system no longer has enough space?

Here the simple rule applies: it is better to be out of spool than out of permanent space because if there is not enough permanent space available, it will typically be a DML statement, which leads to a rollback. SELECT statements mainly use Spoolspace, and rollbacks are, therefore, rather rare.

How is Teradata 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 essential to understand that Teradata never holds data and spool space on one cylinder simultaneously. 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 Teradata 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 avoid 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.

What must be considered for NOPI Teradata tables?

NOPI tables are ideal for improving loading times with bulk load utilities. But the transactional insert into statement can create skewed tables as rows AMP locally, which can cause a no more spool space problem. Skew can be avoided by redistribution of the rows with HASH BY RANDOM. The second Explain Plan in the figure below distributes the rows equally to all AMPs (“redistributed randomly to all AMPs”) in Step 1:

spoolspace

Why do I have a “no more spool space” problem after a Teradata upgrade although the query worked before?

This can happen if the number of AMPs is higher after the upgrade, but the disk space has not been increased proportionally. Then there is less disk space available for each AMP. The following query can check the number of AMP:

SELECT MAX(VPROC) FROM DBC.TableSizeV;

What is Phantom Spool and Leftover Spool?

For Phantom Spool, the table DBC.DatabaseSpace shows that Spool is in use, but no space on the disk is occupied.
Leftover Spool is the Teradata spool space that remains after a request has ended.

How can Phantom Spool Space be released?

The easiest way (without using the console) is to execute the following procedure:

call SYSLIB.FixCurrentSpace(‘S’, ‘Y’, ‘N’, null, cnt, err);

We can use this procedure to solve phantom spool problems and correct inconsistencies in the DBC.DATABASESPACE table.

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
    They are used for volatile tables.
  • Persistent
    Persistent Spool Space survives a system restart or the crash of a node. It can be defined per session or the whole system for the critical workload.

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

Ensure up-to-date statistics

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

Choose a good Primary Index

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

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).

Release Spool Space as soon as possible

Drop Volatile Tables immediately when they are no longer needed.
Often you 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).

Here is a link to the Teradata Spool space documentation:
https://docs.teradata.com/reader/ueCMQAxljdET5klb6rbF1g/1Ui60fKd7ae50Aggfgl57A

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

DWH Pro Admin

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

  • If you don’t have access to remote console utility and dont have access to primary node then how we can release the phantom spool space ?

    • You can try:

      call SYSLIB.FixCurrentSpace(‘S’, ‘Y’, ‘N’, null, cnt, err);

      Details are here:

      FixCurrentSpace

      Thanks for the question. I have included it in the article because I think it might be interesting for others as well.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >