|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Huge amounts of unused space...why?of the space is unused space...take for instance: Table 1 rows = 161,000 reserved = 2,542,064 KB unused = 2,162,784 KB data = 357,952 KB index = 21,328 KB Because of this, my database is now 22G and 17G of that is unused space. Here's the unusual catch...there are NO deletes from this table EVER, only inserts and updates. Why, in that situation would the unused space grow so large? By the way, these tables have a couple other things in common, 1) They each have 1 text field 2) They are all heaps (no clustered index) (we use GUIDs so there is no way to add one) Can someone help me understand the mechanics behind how and why they are growing so big? Thanks for your help When a row containing NULL'able or variable length (ex: varchar or text)
columns is updated in a way that increases it's length, the data must be moved to a new location. Read up on page splitting and fill factors: http://msdn2.microsoft.com/en-us/library/ms189858(SQL.90).aspx http://msdn2.microsoft.com/en-us/library/ms177459.aspx Microsoft SQL Server 2000 Index Defragmentation Best Practices: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx Show quote "Robby" <robby.robert***@gmail.com> wrote in message news:1142621074.685455.31050@e56g2000cwe.googlegroups.com... > I've got some tables that are growing very large and the vast majority > of the space is unused space...take for instance: > > Table 1 > rows = 161,000 > reserved = 2,542,064 KB > unused = 2,162,784 KB > data = 357,952 KB > index = 21,328 KB > > Because of this, my database is now 22G and 17G of that is unused > space. > > Here's the unusual catch...there are NO deletes from this table EVER, > only inserts and updates. Why, in that situation would the unused space > grow so large? > > By the way, these tables have a couple other things in common, > > 1) They each have 1 text field > 2) They are all heaps (no clustered index) (we use GUIDs so there is no > way to add one) > > Can someone help me understand the mechanics behind how and why they > are growing so big? > > Thanks for your help > Hi JT
NULLable columns are not handled any different than their non-nullable counterparts in SQL Server 7 and 2000. Also, page splitting and most issues regarding fragmentation only apply to data pages when there is a clustered index, which the OP says he did not have. Show quote "JT" <some***@microsoft.com> wrote in message news:u0uGphfSGHA.5156@TK2MSFTNGP10.phx.gbl... > > When a row containing NULL'able or variable length (ex: varchar or text) > columns is updated in a way that increases it's length, the data must be > moved to a new location. > > Read up on page splitting and fill factors: > http://msdn2.microsoft.com/en-us/library/ms189858(SQL.90).aspx > http://msdn2.microsoft.com/en-us/library/ms177459.aspx > Microsoft SQL Server 2000 Index Defragmentation Best Practices: > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx > > "Robby" <robby.robert***@gmail.com> wrote in message > news:1142621074.685455.31050@e56g2000cwe.googlegroups.com... >> I've got some tables that are growing very large and the vast majority >> of the space is unused space...take for instance: >> >> Table 1 >> rows = 161,000 >> reserved = 2,542,064 KB >> unused = 2,162,784 KB >> data = 357,952 KB >> index = 21,328 KB >> >> Because of this, my database is now 22G and 17G of that is unused >> space. >> >> Here's the unusual catch...there are NO deletes from this table EVER, >> only inserts and updates. Why, in that situation would the unused space >> grow so large? >> >> By the way, these tables have a couple other things in common, >> >> 1) They each have 1 text field >> 2) They are all heaps (no clustered index) (we use GUIDs so there is no >> way to add one) >> >> Can someone help me understand the mechanics behind how and why they >> are growing so big? >> >> Thanks for your help >> > > > That's what I was thinking, that fillfactor doesn't really mean
anything for these tables since they are heaps and that, while lookups may be more costly, inserts and updates should be very efficient and easy on the space. Kalen, we can't add a clustered index to the guid because it would cause major performance issues with our app. I could add one temporarily just to force a reindex and free up some space, but I get even better results in a much faster way by copying the data out, truncating the table, then reinserting it all. The size of the table is only a couple hundred Meg after that. But my worry is that, even after doing this and shrinking the db, the db is still going to just pick up where it left off and begin growing abnormally again. So my real concern is what might be causing this to happen in the first place. Do you think it is all due to having a text field? It does sound hard to believe that you'd have this much extra space just
from text fields. What version are you running? How much data is in each text column on average? How are you getting the data into the text columns? What kinds of updates are you doing? Show quote "Robby" <robby.robert***@gmail.com> wrote in message news:1142625797.955076.270210@z34g2000cwc.googlegroups.com... > That's what I was thinking, that fillfactor doesn't really mean > anything for these tables since they are heaps and that, while lookups > may be more costly, inserts and updates should be very efficient and > easy on the space. > > Kalen, we can't add a clustered index to the guid because it would > cause major performance issues with our app. I could add one > temporarily just to force a reindex and free up some space, but I get > even better results in a much faster way by copying the data out, > truncating the table, then reinserting it all. The size of the table is > only a couple hundred Meg after that. But my worry is that, even after > doing this and shrinking the db, the db is still going to just pick up > where it left off and begin growing abnormally again. So my real > concern is what might be causing this to happen in the first place. Do > you think it is all due to having a text field? > > I'm confused; I can understand the desire to not have a clustered index
on a uniqueidentifier, but do you not have another column that's a candidate for a clustered index (date of load, insert, etc)? If not, then can you not add an IDENTITY column that you use strictly for indexing purposes? Stu Hi Robby
Updates sometimes happen as a delete followed by an insert, so that can leave unused space. I don't understand why the use of GUIDs will keep you from adding a clustered index. Creating a clustered index could potentially clean up a lot of this space. If you don't want the index, you can drop it afterward. If you create the clustered index and it still hasn't affected your unused space that much, you might need to copy all your text data out, drop the text column, run dbcc cleantable, and re-add your text column. Show quote "Robby" <robby.robert***@gmail.com> wrote in message news:1142621074.685455.31050@e56g2000cwe.googlegroups.com... > I've got some tables that are growing very large and the vast majority > of the space is unused space...take for instance: > > Table 1 > rows = 161,000 > reserved = 2,542,064 KB > unused = 2,162,784 KB > data = 357,952 KB > index = 21,328 KB > > Because of this, my database is now 22G and 17G of that is unused > space. > > Here's the unusual catch...there are NO deletes from this table EVER, > only inserts and updates. Why, in that situation would the unused space > grow so large? > > By the way, these tables have a couple other things in common, > > 1) They each have 1 text field > 2) They are all heaps (no clustered index) (we use GUIDs so there is no > way to add one) > > Can someone help me understand the mechanics behind how and why they > are growing so big? > > Thanks for your help > >  |
|||||||||||||||||||||||
Other interesting topics