Post reply

Table partition and data compression

  • May 8, 2014 at 4:44 am

    #288546

    Hi Team,

    I have large table(60 million rows). I have partitioned that table year wise.

    The problem here is, there are hourly(40000 rows) bulk uploads on same table.

    I just checked that the all the partitions are page compressed.

    Is it possible to remove page compression for particular partition in the table as the CPU is getting very high at the time of bulk upload.

    Thanks

  • May 8, 2014 at 5:11 am

    #1711803

    Yes, it can be done.

    Each partition can be rebuilt using a different compression settings.

    ALTER TABLE table_name

    REBUILD PARTITION = partition_number

    WITH (DATA_COMPRESSION = NONE|ROW|PAGE)

    -- Gianluca Sartori

  • May 8, 2014 at 5:15 am

    #1711805

    Thanks For the reply.

    But when I remove compression how much space it will consume.

    Anyway to calculate the space?

  • May 8, 2014 at 5:18 am

    #1711806

    You can use sp_estimate_data_compression_savings

    -- Gianluca Sartori

  • May 8, 2014 at 5:27 am

    #1711809

    Thanks for the quick help.

    🙂

  • May 8, 2014 at 5:46 am

    #1711814

    You're welcome.

    Glad I could help

    -- Gianluca Sartori

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

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