16.10 - Managing Spool Space - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

Managing spool space allocation for users can be a method to control both space utilization and potentially bad (that is, non-optimized) queries.

Spool Space and Perm Space

Spool space is allocated to a user. If several users are active under the same logon and one query is executed that exceeds the limit of the spool space allocated, all active queries for that user that require spool will likewise be denied additional spool and will be aborted.

If space is an issue, it is better to run out of spool space than to run out of permanent space. A user requesting additional permanent space will do so to execute queries that change tables (inserts or updates, for example). Additional spool requests are almost always done to support a SELECT. Selects are not subject to rollback.

To configure this, see “Cylinders Saved for PERM” in Utilities.

Spool Space Accounting

Spool space for users is updated in the DatabaseSpace table. However, given the possibility of phantom spool, you may need to do one of the following things to clear spool space for users that have logged off:
  • Run the Update Space utility or execute the SQL stored procedure FixCurrentSpace, which performs the same functions as the Update Space utility; see FixCurrentSpace Procedure
  • Execute the SQL stored procedure FixAllocatedSpaceSpace to fix the inconsistencies between the AMP-level space allocations in the DBC.DatabaseSpace table and the global-level space allocations in the DBC.GlobalDBSpace table; see FixAllocatedSpace Procedure

After running Update Space or FixCurrentSpace, spool space values in DBC.DatabaseSpace reflect actual spool usage.

Phantom spool cases are those in which the DBC.DatabaseSpace table indicates that there is spool, although no spool exists on the disk. Phantom spool cases are not the same as “left-over spool” cases. Left-over spool cases are those in which spool is actually created and exists on the disk, although a request completed its execution.

Using Spool Space as a Trip Wire

Lowering spool space may be a way to catch resource-intensive queries that will never finish or that will run the entire system out of free space if the user is allocated a very high spool space limit.

In the interest of system performance, do not allocate high spool space limits to all users and, in general, be very conservative in setting spool space limits.

Consider defining a spool skew factor for databases and users when requests are likely to be skewed across AMPs. However, remember that when skew is non-zero, the system needs to perform more background work to manage the spool limit globally. To maintain system performance, do not give spool space unlimited skew. For more information on skew factors and global space accounting, see About Global Space Accounting.