Why are the sizes so different?
-
I'm going through a process which requires me to make copies of 14 large tables in a database. The original tables have a clustered primary key on them, whereas the copies do not (as yet).
I suspected that the originals could be fragmented to some degree, and so expected the size of the copies to be smaller. I also expected them to be smaller because they had no clustered indexes, and therefore no fillfactor on the data pages (they've never had a clustered index, either, so there is no legacy fillfactor still in place).
Much to my surprise, the copy version of the table was bigger then the original, and by some considerable amount - almost 2 Gb. Here's the output from sp_spaceused with @updateusage='TRUE':
Name pf_data_store_2005_05
Rows 233994537
Reserved 23921560 KB
Data 23698960 KB
Index_Size 219432 KB
Unused 3168 KB
Name pf_data_store_2005_05_COPY
Rows 233994531
Reserved 25643336 KB
Data 25643248 KB
Index_Size 64 KB
Unused 24 KB
The copy was created using SELECT * INTO ... FROM ....
All column datatypes are identical between the two
Note that the original table actually has 6 more rows than the copy, and has 3Mb of unused space.
The copy has no unused space (well, less than 1 extent), so is as compact as a table can be.
Why, why, why is the Data value so much bigger for the copy table? Even the total Reserved size for the original table is less than for the copy, even though the original has a clustered index (which will include at least the default fillfactor and 219 Mb of index pages).
Any offers?
-
Are there any char/varchar columns in the table?
If you generate the create table scripts for both tables, it is an exact match (except for the indexes)?
-
Yes, the CREATE scripts are both identical. I'd have expected that since the copy table was created using SELECT * INTO ....
-
how recent are your statistics ?
run sp_updatestats and dbcc updateusage ('yourdb') with count_rows.
This may interfere with ongoing operations !
Then check your results again.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Already did that - specified @updateusage with the sp_spaceused command.
-
Something I ommitted to mention -
As part of the copy, I actually specify an additional identity column (bigint) on the new table. I then update the adjacent column so that each row equals the identity column, and then I DROP the identity column again. (The column has already been dropped when I generate the figures at the top of this thread)
Now, I can imagine that 240 million additional bigint attributes could add 2 Gb to a table, but why is it that the table doesn't appear to free-up that space after I've dropped the column?
-
Perhaps to save on the amount of I/O that has to be done to reclaim the unused space.
New records will be probably created using the new size.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply