Post reply

Compression

  • 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!

  • June 26, 2018 at 10:19 am

    #1995323

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

  • June 26, 2018 at 10:47 am

    #1995328

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • June 26, 2018 at 11:41 am

    #1995336

    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!

  • June 26, 2018 at 12:35 pm

    #1995352

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • June 26, 2018 at 2:04 pm

    #1995369

    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.

  • June 26, 2018 at 2:13 pm

    #1995373

    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.

  • June 27, 2018 at 8:30 am

    #1995458

    Hi Joe,

    Thank you for a very deep/detailed response.

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

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