Big table 50MB containing just 1000 records ( a few small fields each record)

  • Iulian -207023

    SSCertifiable

    Points: 7509

    Hello,

    I have a big table 50MB containing just 1000 records ( a few small fields each record).

    On indexes I have one primary key and one clustered index, which I rebuilt

    but the table size stays the same, big

    What could be the explanation? Any hint is welcomed.

    Thanks,

    Iulian

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Do you have any columns of this type??

    text, ntext, varchar(max), varbinary(max).

    Do you have other indexes besides those 2?

    Why is having that table at 50MB a problem (other than being odd).

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    Is the fill factor on the indexes very low?

    Do you have any columns of datatype TEXT, NTEXT, IMAGE, NVARCHAR(MAX), VARCHAR(MAX), or VARBINARY(MAX)?

  • ChrisM@home

    SSC-Insane

    Points: 24260

    Iulian -207023 (11/23/2010)


    Hello,

    I have a big table 50MB containing just 1000 records ( a few small fields each record).

    On indexes I have one primary key and one clustered index, which I rebuilt

    but the table size stays the same, big

    What could be the explanation? Any hint is welcomed.

    Thanks,

    Iulian

    Post the table CREATE statement.

    CREATE TABLE Big (Firstname VARCHAR(MAX)), ....


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Iulian -207023

    SSCertifiable

    Points: 7509

    I have a few columns: nvarchar(255) and a few with varchar(50) could this be the cause?

    50MB is not such a bog size I just can not explain how 1000 rows need so much space.

    It might be that along the time operations made the table grow but, if so, how to take back that space?

    Thanks,

    Iulian

  • Iulian -207023

    SSCertifiable

    Points: 7509

    I am sorry Chris I don't have the table structure at hand.

    Thanks,

    Iulian

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Iulian -207023 (11/23/2010)


    I am sorry Chris I don't have the table structure at hand.

    Thanks,

    Iulian

    Need to see the build script and DDL to truly answer your question, or at least tell you where to look. Could be anything from a Fillfactor = 5% to an unexpected LOB field, to strangeness in the index.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Iulian -207023

    SSCertifiable

    Points: 7509

    Thanks Craig you already helped me.

    I will check tomorrow the options you mentioned: LOB fields - as far as I remember there is none - but holly double check :-), fill factor and review indexes.

    Thanks all,

    Iulian

  • Jeff Moden

    SSC Guru

    Points: 996645

    Iulian -207023 (11/23/2010)


    I am sorry Chris I don't have the table structure at hand.

    Thanks,

    Iulian

    Heh... you know how much the table is using for space and can't generate the script for the table or even run sp_help on the table? 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Iulian -207023

    SSCertifiable

    Points: 7509

    Hi Jeff, it was about 50MB - not sure exactly.

    Now the size is 0.28MB - which is great.

    I dropped one unclustered index and left only the primary key index

    then created a clustered index for the columns I know are most used in WHERE clause.

    I settled the fill factor to 85%.

    I also checked the LOB fields - here it was OK, no LOB fields.

    It seems that table size does not change immediately, might this be because of the statistics update frequency?

    Thanks a lot,

    Iulian

  • Leo.Miller

    SSChampion

    Points: 12855

    Iulian -207023 (11/24/2010)


    It seems that table size does not change immediately, might this be because of the statistics update frequency?

    Iulian

    If you are using sp_spaceused you will often get out of date values. sp_spaceused is updated when update stats runs, and if the table changes don't cause an update stats event you will have old figures.

    Cheers

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • Iulian -207023

    SSCertifiable

    Points: 7509

    Thank you Leo,

    I was looking at table properties, but I will try also sp_spaceused in the future.

    Regards,

    Iulian

  • Jeff Moden

    SSC Guru

    Points: 996645

    Iulian -207023 (11/24/2010)


    Hi Jeff, it was about 50MB - not sure exactly.

    Now the size is 0.28MB - which is great.

    I dropped one unclustered index and left only the primary key index

    then created a clustered index for the columns I know are most used in WHERE clause.

    I settled the fill factor to 85%.

    I also checked the LOB fields - here it was OK, no LOB fields.

    It seems that table size does not change immediately, might this be because of the statistics update frequency?

    Thanks a lot,

    Iulian

    Ummm... I'm confused. I thought you said the table already had a clustered index.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Iulian -207023

    SSCertifiable

    Points: 7509

    That is really great, thanks a lot Jeff for leading me here.

    Cheers,

    Iulian

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply