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

Question

Post reply

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

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

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

sturner SSC-Insane Points: 22471 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: 22471 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: 22471 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. 🙂