Partitioning tables has made them much larger (triple in size)

Question

Post reply

Partitioning tables has made them much larger (triple in size)

ahenderson

SSCommitted

Points: 1759
More actions
September 20, 2013 at 10:43 am

#298124

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).

Viewing 7 posts - 1 through 6 (of 6 total)

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

What was you fill factor for the clustered and non-clustered indexes?

The probability of survival is inversely proportional to the angle of arrival.

ahenderson SSCommitted Points: 1759 More actions · Answer 2

Fill factor went from 90% to 80% along with change. Not nearly enough to account for increase.

sturner SSC-Insane Points: 22411 More actions · Answer 3

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.

ahenderson SSCommitted Points: 1759 More actions · Answer 4

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:

bigintNULL

varchar100

datetimeNULL

decimalNULL

varchar100

datetimeNULL

varchar100

floatNULL

smallintNULL

intNULL

smallintNULL

datetimeNULL

intNULL

bigintNULL

tinyintNULL

bigintNULL

smallintNULL

bigintNULL

smallintNULL

intNULL

datetimeNULL

intNULL

smallintNULL

varchar256

varchar100

intNULL

smallintNULL

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!

sturner SSC-Insane Points: 22411 More actions · Answer 5

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.

ahenderson SSCommitted Points: 1759 More actions · Answer 6

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. 🙂