Compression question RRS feed

  • Question

  • In our data warehouse we have a table that is replicated from Oracle that has 1.2 billion rows. We would like to page compress this table. If we set compression on for this table then...

    Will existing data be compressed or only new filled pages?

    If the entire table goes into compression mode will the whole table be locked or only the pages being compressed?

    Monday, May 24, 2010 6:56 PM


  • When using data compression on a populated table, all existing pages or rows must be compressed. While a table is being compressed, both the uncompressed table and the compressed table exist together until the compression is successful and committed. After the table or the index is compressed, the uncompressed table is dropped, and the space is released to the filegroup. To estimate the size of the compressed table, use the output of sp_estimate_data_compression_savings

    There are two options available while enabling data compression:Online vs. Offline. Whether to set the value of ONLINE to ON or OFF depends upon what else is running on the database at the same time. OFF is faster and requires less resources than ON, but the table is locked for the duration of the compression operation

    For more information, refer this whitepaper : http://msdn.microsoft.com/en-us/library/dd894051.aspx


    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties and confers no rights.
    Monday, May 24, 2010 8:31 PM