Variables That Affect Disk Space

Use the SPDSCMPF= macro variable to specify the amount of growth space, sized in bytes, to be added to a compressed data block.

SPDSCMPF=n

Default:0 bytes

Updating rows in compressed tables can increase the size of a given table block. Additional space is required for the block to be written back to disk. When contiguous space is not available on the hard drive, a new block fragment stores the excess, updated quantity. Over time, the table will experience block fragmentation.

When opening compressed tables for OUTPUT or UPDATE, you can use the SPDSCMPF= macro variable to anticipate growth space for the table blocks. If you estimate correctly, you can greatly reduce block fragmentation in the table.

Use the SPDSDCMP= macro variable to compress SPD Server tables that are stored on disk.

SPDSDCMP=YES|NO

Default:NO

Use in Conjunction with Table Option: IOBLOCKSIZE=

Corresponding Table Option: COMPRESS=

Arguments

When you set the SPDSDCMP= macro variable to YES, SPD Server compresses newly created tables by 'blocks' according to the algorithm specified. To control the amount of compression, use the table option IOBLOCKSIZE= to specify the number of rows that you want to store in the block. For a complete discussion, refer to IOBLOCKSIZE=.

Example

Before creating huge tables, you want to conserve disk space. Specify compression, and the default algorithm SPDSRLLC, at the beginning of your job.

%let SPDSDCMP=YES;  

Use the SPDSIASY= macro variable to specify whether to create indexes in parallel when creating multiple indexes on an SPD Server table.

SPDSIASY=YES|NO

Default:NO

Corresponding Table Option : ASYNCINDEX=

Arguments

You use the macro variable SPDSIASY= to choose between parallel and sequential index creation on SPD Server tables with more than one index. One advantage of creating multiple indexes in parallel is speed. The speed enhancements that can be achieved with parallel indexes are not free. Parallel indexes require significantly more disk space for working storage. The default SPD Server setting for the SPDSIASY= macro variable is set to NO, in order to avoid exhausting the available work storage space.

However, if you have adequate disk space to support parallel sorts, it is strongly recommended that you override the default SPDSIASY=NO setting and assign SPDSIASY=YES. You can substantially increase performance -- indexes that take hours to build complete much faster.

How many indexes should you create in parallel? The answer depends on several factors, such as the number of CPUs in the SMP configuration and available storage space needed for index key sorting.

When managing disk space on your SPD Server, remember that grouping index create statements can minimize the number of table scans that SPD Server performs, but it also affects disk space consumption. There is an inverse relationship between the table scan frequency and disk space requirements. A minimal number of table scans requires more auxiliary disk space; a maximum number of table scans requires less auxiliary disk space.

Your perform batch processing from midnight to 6:00 a.m. All of your processing must be completed before start of the next work day. One frequently repeated batch job creates large indexes on a table, and usually takes several hours to complete. Configure SPD Server to create indexes in parallel to reduce the processing time.

%let SPDSIASY=YES;
proc datasets lib=spds;
   modify a;
   index create x;
   index create y;
   modify a;
   index create comp=(x y) comp2=(y x);
   quit;

In the example above, the X and Y indexes will be created in parallel. After creating X and Y indexes, SPD Server creates the COMP and COMP2 indexes in parallel. In this example, two table scans are required: one table scan for the X and Y indexes, and a second table scan for the COMP and COMP2 indexes.

Use the SPDSSIZE= macro variable to specify the size of an SPD Server table partition.

SPDSSIZE=n

Default:16 Megabytes

Corresponding Table Option: PARTSIZE=

Affected by LIBNAME option: DATAPATH=

Arguments

Use this SPDSSIZE= macro variable option to improve performance of WHERE clause evaluation on non-indexed table columns.

Splitting the data portion of a server table at fixed-sized intervals allows SPD Server to introduce a high degree of scalability for non-indexed WHERE clause evaluation. This is because SPD Server launches threads in parallel and can evaluate different partitions of the table without file access or thread contention. The speed enhancement comes at the cost of disk usage. The more data table splits you create, the more you increase the number of files, which are required to store the rows of the table.

Scalability limits on the SPDSSIZE= macro variable ultimately depend on how you structure the DATAPATH= option in your LIBNAME statement. The configuration of the DATAPATH= file systems across striped volumes is important. You should spread each individual volume's striping configuration across multiple disk controllers and SCSI channels in the disk storage array. Your configuration goal, at the hardware level, should be to maximize parallelism when performing data retrieval.

The SPDSSIZE= specification is also limited by MINPARTSIZE=, an SPD Server parameter maintained by the SPD Server administrator. MINPARTSIZE= ensures that an over-zealous SAS user cannot arbitrarily create small partitions, thereby generating an excessive number of physical files. The default for MINPARTSIZE= is 16 Mbytes.

Note: The SPDSSIZE= value for a table cannot be changed after the table is created. To change the SPDSSIZE=, you must PROC COPY the table and use a different SPDSSIZE= (or PARTSIZE=) option setting on the new (output) table.

For an example using the table option, see PARTSIZE=.

%let SPDSSIZE=32;