Results 1 to 4 of 4

Thread: sp_estspace

  1. #1
    Join Date
    Jan 2003
    Location
    Bandung, Indonesia
    Posts
    2

    Question Unanswered: sp_estspace

    I've used Sybase 11. I just try this :

    query 1:
    sp_spaceused employee

    result 1:
    name rowtotal reserved data index_size unused
    ---------- --------- ---------- --------- ----------- ------
    employee 176164 53716 KB 22022 KB 31646 KB 48 KB

    query 2:
    sp_estspace employee, 176164

    result 2:
    name type idx_level Pages Kbytes
    ------------------ ------------ --------- ------------ ------------
    employee data 0 8128 16255
    PK_employee nonclustered 0 2518 5034
    PK_employee nonclustered 1 42 84
    PK_employee nonclustered 2 1 2
    idx_employee_1 nonclustered 0 1875 3750
    idx_employee_1 nonclustered 1 25 50
    idx_employee_1 nonclustered 2 1 2
    idx_employee_2 nonclustered 0 1603 3204
    idx_employee_2 nonclustered 1 19 38
    idx_employee_2 nonclustered 2 1 2
    idx_employee_3 nonclustered 0 1520 3038
    idx_employee_3 nonclustered 1 17 34
    idx_employee_3 nonclustered 2 1 2
    Total_Mbytes
    -----------------
    30.76
    name type total_pages time_mins
    ----------------- ------------ ------------ ------------
    PK_employee nonclustered 2561 13
    idx_employee_1 nonclustered 1901 11
    idx_employee_2 nonclustered 1623 10
    idx_employee_3 nonclustered 1538 10

    My question is why is different ? (sp_spaceused=53716 KB, sp_estspace=30.76 MB). How can i use sp_estspace for estimating space required for table? Thx.

    handy

  2. #2
    Join Date
    Sep 2002
    Location
    Germany, near Aachen
    Posts
    120

    Re: sp_estspace

    > My question is why is different ? (sp_spaceused=53716 KB, sp_estspace=30.76 MB).
    > How can i use sp_estspace for estimating space required for table?

    The difference can be caused by the table structure (varchar cols) and the distribution of the data on the pages.

    sp_estspace takes varchar columns with an average length into account, so if your varchar columns are more than half full, your table will occupy more space.
    You can tell sp_estspace which columns will be more than half full on average.

    If there are lots of inserts and deletes in your table, it may be that some pages contain only one row where there could be more. This also leads to a more space.

    Check the optdiag output for the table. It will show you the average rowlength and average rows per page.

  3. #3
    Join Date
    Jan 2003
    Location
    Bandung, Indonesia
    Posts
    2

    Question Re: sp_estspace

    Bernd,

    >The difference can be caused by the table structure (varchar cols) and
    >the distribution of the data on the pages.

    >sp_estspace takes varchar columns with an average length into
    >account, so if your varchar columns are more than half full, your table
    >will occupy more space.
    >You can tell sp_estspace which columns will be more than half full on
    >average.

    But there are no varchar cols in my table. Here is the structure :
    create table employee
    (column01 char(21) NOT NULL
    ,column02 char(10) NULL
    ,column03 char(7) NULL
    ,column04 char(10) NULL
    ,column05 char(18) NULL
    ,column06 smallint NULL
    ,column07 smallint NULL
    ,column08 smallint NULL
    ,column09 float NULL
    ,column10 float NULL
    ,column11 char(7) NULL
    ,column12 datetime NULL
    ,column13 smalldatetime NULL
    ,constraint PK_employee primary key nonclustered (column01)
    )
    on 'default'
    go

    Index :
    create index idx_employee_1 on employee(column03, column04) --nonclustered
    create index idx_employee_2 on employee(column06, column04) --nonclustered
    create index idx_employee_3 on employee(column02) --nonclustered
    go

    >If there are lots of inserts and deletes in your table, it may be that
    >some pages contain only one row where there could be more. This
    >also leads to a more space.

    Only insert transaction in my table, no delete and update.

    Thanks.
    handy

  4. #4
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78

    Lightbulb

    Try to use "sp_estspace" with:

    fill_factor
    is the index fillfactor. The default is null, which means
    that Adaptive Server uses its default fillfactor.

    cols_to_max
    is a comma-separated list of the variable-length
    columns for which you want to use the maximum length instead
    of the average. The default is the average declared length of the
    variable-length columns.


    sp_estspace employee, 176164, null, "column02,column03,column04,column05,column06,colu mn07,column08,column09,column10,column11,column12, column13"
    go

    sp_estspace employee, 176164, 75, "column02,column03,column04,column05,column06,colu mn07,column08,column09,column10,column11,column12, column13"
    go


    You have too many null columns, and may be "sp_estspace" use average size.

    Do you insert and then update rows?

    I think that:
    When a column is null, Sybase don't reserve space.
    This is a heap table, so if you insert one row with a null value,
    there are no free space for the next update into the page.
    The next time, you update the null value, you will need spaces for a bigger row, and you will lose the previous row spaces.

    Bye bye

    Sebastian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •