Table size swell after altering a column

Question

Table size swell after altering a column

ft55

Ten Centuries

Points: 1210
More actions
April 24, 2013 at 12:07 pm

#276885

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

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 Ten Centuries Points: 1210 More actions · Answer 2

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 SSC Guru Points: 182279 More actions · Answer 3

How are you checking the table size?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

ft55 Ten Centuries Points: 1210 More actions · Answer 4

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 SSC Guru Points: 182279 More actions · Answer 5

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 Ten Centuries Points: 1210 More actions · Answer 6

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 SSC Guru Points: 182279 More actions · Answer 7

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 Ten Centuries Points: 1210 More actions · Answer 8

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