space allocation to table object

Question

space allocation to table object

Ellen-477471

SSCrazy

Points: 2238
More actions
April 10, 2013 at 9:31 am

#272921

recently a large table [27 Gb] suddenly jumped in size to almost double; there was not an influx of data so I am wondering if it is the way SQL Server 2005 allocates a new extent to a table that needs to grow.
Would it be a percentage of the existing size of the table? If so, is there anyway to control how much space is added to the table object?
Thank you in advance for your insight.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Really not enough information to provide a decent answer, especially since we can't see what you see.

Did you have a recent mass update to the table, does the table have a clustered index, any number of other changes that may have occurred.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Ellen-477471 (4/10/2013)
I am wondering if it is the way SQL Server 2005 allocates a new extent to a table that needs to grow.
Would it be a percentage of the existing size of the table?

Nope. An extent at a time. So if table needs 1 kb more space, it gets an extent (64 kb), providing it's large enough to use uniform extents (a few kb). If the table needs 100kb more space, it gets 2 extents, etc.

As for a large increase in space...

- rebuild index with lower fill factor

- lots of inserts into the 'middle' of the clustered index

- lots of updates that grow the row (eg updating a null value to non-null)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ellen-477471 SSCrazy Points: 2238 More actions · Answer 3

Thank you for the answer. I knew the standard extent size is 64kb but was thinking that maybe there was a mechanism for escalating the size [similar to what Informix does].

At this point in time since there was not a mass insert or update of the table data [or any added columns, changed data types, etc.] I suspect that when the size was recorded a while back a mistake was made. [either we used the data pages and forgot to multiply by 8 or something else happened.]