Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

space allocation to table object Expand / Collapse
Author
Message
Posted Wednesday, April 10, 2013 9:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 6:32 AM
Points: 136, 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.
Post #1440889
Posted Wednesday, April 10, 2013 12:21 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:44 PM
Points: 22,525, Visits: 30,290
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.



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)
Post #1440974
Posted Wednesday, April 10, 2013 3:30 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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

Post #1441042
Posted Wednesday, April 10, 2013 3:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 6:32 AM
Points: 136, 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.]
Post #1441057
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse