SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


space allocation to table object


space allocation to table object

Author
Message
Ellen-477471
Ellen-477471
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 314
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.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39097 Visits: 38518
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86791 Visits: 45254
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
Ellen-477471
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 314
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.]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search