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

Question

Post reply

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

Iulian -207023

SSCertifiable

Points: 7639
More actions
November 23, 2010 at 3:38 pm

#388295

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

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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: 7639 More actions · Answer 4

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: 7639 More actions · Answer 5

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

Thanks,

Iulian

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

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: 7639 More actions · Answer 7

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: 1004406 More actions · Answer 8

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.

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

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 9

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: 13051 More actions · Answer 10

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: 7639 More actions · Answer 11

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: 1004406 More actions · Answer 12

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.

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

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 13

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

Cheers,

Iulian