sp_iqestdbspaces procedure

Function

Estimates the number and size of dbspaces needed for a given total index size.

Syntax

sp_iqestdbspacesdb_size_in_bytes, iq_page_size,
min_#_of_bytes, max_#_of_bytes )

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Description

Displays information about the number and size of dbspace segments based on the size of the database, the IQ page size, and the range of bytes per dbspace segment. This procedure assumes that the database was created with the default block size for the specified IQ page size (or else the estimate is incorrect). Table 7-21 lists the sp_iqestdbspaces parameters.

Table 7-21: sp_iqestdbspaces parameters

Name

Datatype

Description

db_size_in_bytes

decimal(16)

Size of the database in bytes.

iq_page_size

smallint

The page size defined for the IQ segment of the database (must be a power of 2 between 65536 and 524288; the default is 131072).

min_#_of_bytes

int

The minimum number of bytes per dbspace segment. The default is 20,000,000 (20MB).

max_#_of_bytes

int

The maximum number of bytes per dbspace segment. The default is 2,146,304,000 (2.146GB).

Usage

sp_iqestdbspaces displays four types of recommendations, depending on how much of the data is unique:

min If there is little variation in data, you can choose to create only the dbspace segments of the sizes recommended as min. These recommendations reflect the best possible compression on data with the least possible variation.

avg If your data has an average amount of variation, create the dbspace segments recommended as min, plus additional segments of the sizes recommended as avg.

max If your data has a high degree of variation (many unique values), create the dbspace segments recommended as min, avg, and max.

spare If you are uncertain about the number of unique values in your data, create the dbspace segments recommended as min, avg, max, and spare. You can always delete unused segments after loading your data, but creating too few can cost you some time.

StepsUsing sp_iqestdbspaces with other system stored procedures

  1. Run sp_iqestjoin for all the table pairs you expect to join frequently.

  2. Select one of the suggested index sizes for each pair of tables.

  3. Total the index sizes you selected for all tables.

  4. Run sp_iqestspace for all tables.

  5. Total all of the RAW DATA index sizes returned by sp_iqestspace.

  6. Add the total from step 3 to the total from step 5 to determine total index size.

  7. Use the total index size calculated in step 6 as the db_size_in_bytes parameter in sp_iqestdbspaces.

Results of sp_iqestdbspaces are only estimates, based on the average size of an index. The actual size depends on the data stored in the tables, particularly on how much variation there is in the data.

Sybase strongly recommends that you create the spare dbspace segments, because you can delete them later if they are unused.

Example

sp_iqestdbspaces 12000000000, 65536, 500000000, 2146304000

dbspace files

Type

            Size

Msg

                 1

min

2146304000

                 2

min

2146304000

                 3

min

  507392000

                 4

avg

2146304000

                 5

max

2053697536

                 6

spare

1200001024

This example estimates the size and number of dbspace segments needed for a 12GB database. Sybase IQ recommends that you create a minimum of 3 segments (listed as min) for the best compression, if you expect little uniqueness in the data. If the data has an average amount of variation, 1 more segment (listed as avg) should be created. Data with a lot of variation (many unique values, requiring extensive indexing), may require 1 more segment (listed as max). You can ensure that your initial load succeeds by creating a spare segment of 1200001024 bytes. Once you have loaded the database, you can delete any unused dbspace segments.