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

Table size swell after altering a column Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 12:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:13 AM
Points: 33, Visits: 129
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
Post #1446161
Posted Wednesday, April 24, 2013 12:12 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:49 PM
Points: 866, Visits: 7,478
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
Post #1446167
Posted Wednesday, April 24, 2013 12:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:13 AM
Points: 33, Visits: 129
Thanks for the response , thats whats strange test was a copy back from live so should be an exact copy...

Thanks again
Frant101
Post #1446175
Posted Wednesday, April 24, 2013 2:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:30 AM
Points: 7,135, Visits: 12,747
How are you checking the table size?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1446237
Posted Wednesday, April 24, 2013 3:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:13 AM
Points: 33, Visits: 129
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
Post #1446246
Posted Thursday, April 25, 2013 10:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:30 AM
Points: 7,135, Visits: 12,747
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
Post #1446789
Posted Monday, April 29, 2013 1:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:13 AM
Points: 33, Visits: 129
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
Post #1447730
Posted Monday, April 29, 2013 1:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:30 AM
Points: 7,135, Visits: 12,747
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
Post #1447744
Posted Monday, April 29, 2013 1:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:13 AM
Points: 33, Visits: 129
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
Post #1447748
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse