SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-28
Planning for Temporary File Requirements
Planning for Temporary File Requirements
When conducting joins and various other operations, SQL/MP creates and uses
temporary files. These files exist during the course of an operation and are used for
storage during intermediate steps in the operation. When the operation is complete,
the temporary files are deleted.
For both serial and parallel operations, the SQL compiler determines the size and
location of the necessary temporary files. When it creates a temporary file, the SQL
compiler allocates a primary extent of 32 pages and then allocates secondary extents
as needed. The secondary extent size is either 512 pages (if SQL estimates the file will
be less than 1 GB in size) or 1024 pages (if SQL estimates the file will be larger than 1
GB in size).
The SQL compiler creates each temporary file with a MAXEXTENTS value of 978
extents, permitting a potential maximum length of up to approximately two GBs. Of
course, the maximum file length is also constrained by the amount of free space
actually available on the disk where the temporary file is stored. This value differs at
various run times.
The SQL compiler controls all phases of temporary file allocation automatically. You
can, however, use DEFINEs to influence the location and SYNCDEPTH of these files.
The =_SQL_TM_sys_vol class of system DEFINEs lets you redirect temporary table
creation from one volume to another or change the SYNCDEPTH associated with
temporary tables. This DEFINE helps avoid file-system error 122, which occurs when a
volume becomes full or when DP2 takes over after a processor failure and affects
temporary tables created with a SYNCDEPTH of 0 (zero). For more information, see
the SQL/MP Reference Manual.
To avoid encountering file-system error 43, UNABLE TO OBTAIN DISK FILE SPACE
FOR FILE EXTENT, allow enough free space to remain on your disk for ORDER BY,
GROUP BY, DISTINCT, and join operations. A good guideline is to keep up to half the
total disk space free for these operations.
An additional way to decrease the chance of encountering file-system error 43 is to
request parallel execution. When you specify the CONTROL EXECUTOR statement
with PARALLEL EXECUTION ON, SQL/MP examines all disks on the system and
attempts to spread the temporary files evenly among these disks. This type of
balancing promotes the availability of disk space for temporary file extents.