Estimates the number and size of dbspaces needed for a given total index size.
sp_iqestdbspaces ( db_size_in_bytes, iq_page_size, min_#_of_bytes, max_#_of_bytes )
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
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-23 lists the 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). |
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.
Using sp_iqestdbspaces with other system
stored procedures
Run sp_iqestjoin for all the table pairs you expect to join frequently.
Select one of the suggested index sizes for each pair of tables.
Total the index sizes you selected for all tables.
Run sp_iqestspace for all tables.
Total all of the RAW DATA index sizes returned by sp_iqestspace.
Add the total from step 3 to the total from step 5 to determine total index size.
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.
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.
