September 20, 2013 at 10:43 am
Hi, we recently partitioned some large tables in a database and now the table sizes are 3 times what they used to be. It is rougly the same number of rows as before. we lost a terabyte of space after partitioning them with basically no new data to account for it.
I analyzed one of the smaller tables that we partitioned, and the table that had occupied 80,000 pages now occupies over 200,000 pages at the leaf level on the clustered index for the same number of rows. I think we were getting 60 rows per page and now we are getting about 23 rows per 8kb page on average. There are only 4 new columns to this 45 column table and they are small like ints (no large new columns).
The tables are clustered and have several non-clustered indexes. I checked fragmentation levels and they are defragmented, and over 80% page space is utilized per page. At most I would expect a 20% size increase due to the fullness of the pages. I rebulit the clustered index and the number of pages actually increased a bit, so its definitely not fragmentation.
Is there anything else I can check? Is the table size expected to grow this big after partitioning for some reason?
P.S. the table has about 40 partitions (1 per month of data).
September 20, 2013 at 11:31 am
What was you fill factor for the clustered and non-clustered indexes?
The probability of survival is inversely proportional to the angle of arrival.
September 20, 2013 at 12:08 pm
Fill factor went from 90% to 80% along with change. Not nearly enough to account for increase.
September 20, 2013 at 12:22 pm
well if you are getting 23 rows per page and you were getting 60, the reason has to be a combination of fill factor and adding more columns. Post the DDL for the before and after table and index definitions then we may be able to drill down further.
The probability of survival is inversely proportional to the angle of arrival.
September 20, 2013 at 12:58 pm
Ok, so checking I did not give you the exact details in the first post with the column count, here are the exact details, the old table has 53 columns and the new table has 56. I am also getting different average space used per page now, but its not that different than the old table, not enough to account for the difference in size.
There are 3 new columns, a datetime, bigint, and smallint.
Other than that we have the following in the old and new SCHEMA:
bigint NULL
bigint NULL
bigint NULL
varchar 100
datetime NULL
decimal NULL
varchar 100
varchar 100
varchar 100
varchar 100
varchar 100
varchar 100
varchar 100
datetime NULL
varchar 100
varchar 100
varchar 100
varchar 100
float NULL
smallint NULL
int NULL
smallint NULL
smallint NULL
smallint NULL
datetime NULL
datetime NULL
int NULL
bigint NULL
bigint NULL
bigint NULL
bigint NULL
tinyint NULL
bigint NULL
bigint NULL
smallint NULL
bigint NULL
bigint NULL
bigint NULL
smallint NULL
smallint NULL
int NULL
int NULL
int NULL
int NULL
datetime NULL
datetime NULL
int NULL
smallint NULL
smallint NULL
varchar 256
varchar 100
int NULL
smallint NULL
OLD TABLE Clusterd index:
db_table_index_name: pkPayment
sum_record_count: 4,927,386
size_mb: 660.34
avg_record_size_in_bytes: 92
avg_fragmentation_in_percent: 18.8
avg_page_space_used_in_percent: 61.9
NEW TABLE Clustered index
db_table_index_name: pkPayment
sum_record_count: 5,047,140
size_mb: 2032.52
avg_record_size_in_bytes: 115
avg_fragmentation_in_percent: 0.0
avg_page_space_used_in_percent: 53.2
Looking at the above stats I queried from dm_db_index_physical_stats, the average record size did not increase nearly enough to account for growing from 660mb to 2032mb. It doesn't make sense!
September 20, 2013 at 1:38 pm
the row size appears to have increased commensurate with the size of the three nullable columns you added so that sounds consistent. Do me a favor and run DBCC SHOWCONTIG('YourTable') and lets see what it says about the extent allocations.
The probability of survival is inversely proportional to the angle of arrival.
September 20, 2013 at 2:07 pm
Sorry everyone, I overlooked the fact that we had PAGE level compression on these tables on the old version and they new partitioned tables were not compressed!
That explains it! Thanks for your help anyway. 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply