Teradata Spool Space 101: Understanding, Managing, and Troubleshooting

Teradata Spool Space Introduction

What is Teradata Spool Space?

Teradata uses Spool Space as a temporary workspace to store intermediate and final result sets. This space is only used temporarily and is released when it is no longer needed or automatically at the end of the session. Later in this article, we will explain the four categories of Spool Space.

How can we increase the spool space in Teradata?

Maintaining sufficient spool reserve is critical. To enhance Teradata Spool Space accessibility, implement compression for frequent column values, eliminate fallback protection, and delete indexes. Furthermore, consider system upgrades a viable solution to increase Spool Space capacity.

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

Remember that running out of spool space is better than running out of permanent space. Insufficient permanent space mostly affects DML statements, leading to costly rollbacks. However, SELECT statements use spool space, causing no rollbacks.

How is Teradata Spool Space assigned to a User?

During the user creation process, maximum spool space allocation can be established.

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

The assigned spool space is the highest limit for a user and is uniformly spread out among all AMPs.

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

As Teradata apportions the total spool space among the number of AMPs, each AMP possesses only a portion of the available space. In the case of a skewed query, an AMP may lack sufficient space, leading Teradata to terminate the request. Generally, the risk of encountering this issue increases with the number of AMPs within the system as the space per AMP decreases when the number of AMPs increases without up-scaling storage simultaneously.

How is Spool Space managed internally by Teradata?

Understanding that Teradata does not concurrently store data and spool space on the same cylinder is crucial. Consequently, Teradata swiftly releases cylinders once they are no longer needed for spool space usage.

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

SELECT * from DBC.DATABASESWHERE DATABASENAME=’DWHPRO’;

Why is there even a Teradata Spool Space Limit?

Establishing this limit is crucial to abort requests that consume excessive resources. Although high spool space usage does not necessarily imply a poorly constructed query, there is a strong correlation between suboptimal queries and increased spool space consumption.

What “no more spool space” Scenarios are there?

The absence of available cylinders on the Teradata system could be a contributing factor. It is imperative to avert a scenario where all cylinders reach capacity. Typically, the DBA establishes a distinct database on each Teradata system to allocate and reserve space, thus circumventing the depletion of free cylinders.

Excessive parallel sessions by a single user can also deplete available spool space. Queries being skewed can also result in “no more spool space.”

What must we consider for NOPI Teradata tables?

NOPI tables can speed up loading times when utilizing bulk load utilities. Nevertheless, transactional INSERT INTO statements into NOPI tables may result in skewed data as rows are allocated to AMPs locally, potentially causing the “no more spool space” problem. To avoid skew, the rows can be redistributed using HASH BY RANDOM. Step 1 of the second Explain Plan illustrated below ensures an equal row distribution across all AMPs (“redistributed randomly to all AMPs”).

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

This problem may arise if the number of AMPs increases after a system upgrade, but the disk space is not proportionally scaled. Consequently, each AMP will have less disk space available. To verify the number of AMPs, you can use the following query:

SELECT MAX(VPROC) FROM DBC.TableSizeV;

What are Phantom Spool and Leftover Spool?

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

How can Phantom Spool Space be released?

The simplest method, without resorting to the console, is to perform the following procedure:

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

This procedure resolves phantom spool issues and rectifies inconsistencies in the DBC.DATABASESPACE table.

Types of Teradata Spool Space

• Intermediate: It 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: Teradata uses volatile spool space for volatile tables.
• Persistent: Persistent Spool Space is used for critical workloads and survives a system restart or a node crash. We can define the critical workload per session or the whole system.

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

As previously stated, “no more spool space” can be caused by various circumstances. Here are some suggestions for preventing this issue:

Ensure up-to-date statistics

Up-to-date statistics are crucial for the optimizer to avoid excessive data transfer between AMPs.

Choose a good Primary Index.

Select a primary index for your tables that evenly distributes the rows among all AMPs.

Prevent “no more spool space” on the system level

Avoid spool space depletion by implementing a spool reserve database.

Release Spool Space as soon as possible

Immediately drop volatile tables when they are no longer necessary to avoid unnecessary blockage of spool space and cylinders that Teradata could use as permanent space. Though it is expected to happen automatically at the end of the session, relying on this is not advisable.

Use Multivalue Compression

Using multivalue compression optimizes data block compression in main memory, reducing necessary spool space. This technique particularly benefits tables with limited value variety.

Use the appropriate data types

Select appropriate data types to encompass all domain values while minimizing space usage. For instance, opt for a BYTEINT rather than an INTEGER column when possible, as the latter consumes four times more spool space (unless the column is compressed). Similarly, use a VARCHAR column instead of a CHAR column to conserve spool space (assuming the column is uncompressed).

Primary Index Access instead of Full Table Scans

The workload must prioritize primary index accesses, particularly utilizing UPI or USI access to prevent the need for spool space. The PDM is often the optimal tool for avoiding spool space complications. Additionally, Row Partitioning can effectively decrease full table scans by targeting specific partitions.

Use the DATABLOCKSIZE parameter to create smaller data blocks

For primarily tactical workloads, smaller data blocks can prevent the need for spooling.

Use columnar table design

Columnar tables can significantly reduce required spool space when the access pattern is appropriate, as only necessary columns are spooled. However, precise analysis and adaptation to the workload are necessary when utilizing columnar tables. A comparable outcome can be obtained by vertically partitioning tables and decreasing the number of columns.

To prevent acute space issues resulting from skew, Teradata offers new features that can efficiently manage permanent space:

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