Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table size swell after altering a column


Table size swell after altering a column

Author
Message
ft55
ft55
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 231
Hi

I recently ran some code that altered some columns in a 38 million row table from varchar to nvarchar. Before the code the 38 million rows consumed 7Gb of space. After i can the code to alter the column to nvarchar the table now takes up 13gb of space.

The reason this concerns me is that i ran the same process on a test environment and the 38 million rows only consumes 8gb of space.

I have run dbcc checktable and it returns no errors. Is there a way i can check the table page usage to see what might be going on ( i have run shrinkfile but this only reclaimed the free space) im concerned that table had doubled in size and might have duplicated / wasted data causing slowness in the application.

Im using sql server 2008 r2 and running the alter table xxx alter column xxx command

Any thoughts would be great
Frant101
David Webb-CDS
David Webb-CDS
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 8584
That expansion doesn't sound unreasonable, given the type of alteration you are doing, particularly if the columns you were altering made up the lion's share of the row size. Did the test table also have the same indexes as the production table? If any of those columns are indexed, that would have some impact.



And then again, I might be wrong ...
David Webb
ft55
ft55
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 231
Thanks for the response , thats whats strange test was a copy back from live so should be an exact copy...

Thanks again
Frant101
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8261 Visits: 14368
How are you checking the table size?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
ft55
ft55
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 231
Im right clicking the table in ssms and selecting properties then storage

Im also looking at the standard database report top tables by usage ( i think thats what it was called ) it shows the top tables by size

Is there a better more acurate way to check ?

Thanks again
Frant101
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8261 Visits: 14368
All things being equal, going from VARCHAR to NVARCHAR should roughly double the size of your table. You might check for fragmentation in your clustered index (assuming the table is clustered).

PS expanding to NVARCHAR could cause page splits if data is stored in-row so maybe check fragmentation before and after the change, if you can run another test with a clean DB

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
ft55
ft55
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 231
Hi,

Just as an update the server restarted last night due to windows patching and has now returned all the table swell that I was seeing to free space in the database. It is now the same size as our test database.

I'm bit confused as to why this might be but maybe the restart kicked off some sort of tidy up process ?

Thanks for your help
frant101
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8261 Visits: 14368
Is there a weekend "index rebuild" job? That could cause a reduction in space if fragmentation was causing the bloat.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
ft55
ft55
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 231
Ahh yes there is, its an in house script that rebuilds / reorganises the top most n indexes every sunday. We record which ones have been rebuilt / reorganised in an audit table so will check tomorrow.

Thanks again
Frant101
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