Compression

Question

Post reply

Compression

squareoff

Mr or Mrs. 500

Points: 587
More actions
June 26, 2018 at 8:44 am

#406793

Hi All,
If I enable PAGE COMPRESSION on an empty table, when I insert the data (using INSERT INTO command), does the data automatically get compressed?
What if I insert the data via SSIS using a Data Flow Task?
Please advise.
Thank you!

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

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

Joe Torre SSChampion Points: 10248 More actions · Answer 1

Joe Torre

SSChampion

Points: 10248

June 26, 2018 at 10:19 am

#1995323

Yes, and it doesn't mater how it's inserted.

ScottPletcher SSC Guru Points: 100950 More actions · Answer 2

Yes, eventually it will get compressed automatically. Although SQL doesn't actually do any page compression until the page gets full.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

squareoff Mr or Mrs. 500 Points: 587 More actions · Answer 3

Thank you both.

While researching, I read online that unless you are using WITH(TABLOCK) when inserting data into an empty "heap" table the data will be compressed using ROW LEVEL compression (even if the able is created with PAGE COMPRESSION).

If that is true, then the only way to achieve PAGE LEVEL compression (if you are starting out with a new table) is to either insert with WITH(TABLOCK) the 1st set of data or just insert and then COMPRESS the data using PAGE COMPRESSION.

Is this correct or am I misunderstanding something?

Please advise.

Thanks!

ScottPletcher SSC Guru Points: 100950 More actions · Answer 4

squareoff - Tuesday, June 26, 2018 11:41 AM
Thank you both.
While researching, I read online that unless you are using WITH(TABLOCK) when inserting data into an empty "heap" table the data will be compressed using ROW LEVEL compression (even if the able is created with PAGE COMPRESSION).
If that is true, then the only way to achieve PAGE LEVEL compression (if you are starting out with a new table) is to either insert with WITH(TABLOCK) the 1st set of data or just insert and then COMPRESS the data using PAGE COMPRESSION.
Is this correct or am I misunderstanding something?
Please advise.
Thanks!

If what you read is true, I guess that would be your only options. Still, you should have very few heaps, and almost all of those will be for staging tables. Row compression alone should be fine on that type of table anyway.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Joe Torre SSChampion Points: 10248 More actions · Answer 5

When Page Compression Occurs

When a new table is created that has page compression, no compression occurs. However, the metadata for the table indicates that page compression should be used. As data is added to the first data page, data is row-compressed. Because the page is not full, no benefit is gained from page compression. When the page is full, the next row to be added initiates the page compression operation. The whole page is reviewed; each column is evaluated for prefix compression, and then all columns are evaluated for dictionary compression. If page compression has created enough room on the page for an additional row, the row is added, and the data is both row- and page-compressed. If the space gained by page compression minus the space that is required for the CI structure is not significant, page compression is not used for that page. Future rows either fit onto the new page or, if they do not fit, a new page is added to the table. Similar to the first page, the new page is not at first page-compressed.

When an existing table that contains data is converted to page compression, each page is rebuilt and evaluated. Rebuilding all the pages causes the rebuilding of the table, index, or partition.

Joe Torre SSChampion Points: 10248 More actions · Answer 6

Based on that I would change my answer to it depends on it's rate of change and the amount of space savings possible when a new page is added. Rebuilding the table for a page compressed table, based on it's rate of change and the amount of space savings seems advisable to leverage the benefits of compression. There must be a "sweet point". In a Data Warehouse situation that is refreshed periodically, compression would be occurring automatically as large insert, update and delete operations at once would cause changes that a fill some pages while others may split.

squareoff Mr or Mrs. 500 Points: 587 More actions · Answer 7

Hi Joe,

Thank you for a very deep/detailed response.