Home Forums SQL Server 2008 SQL Server 2008 - General If I compress a primary key, am I compressing the whole table --> P-key with DATA_COMPRESSION RE: If I compress a primary key, am I compressing the whole table --> P-key with DATA_COMPRESSION

  • Estimate space gain with sp_estimate_data_compression_savings procedure:

    exec sp_estimate_data_compression_savings 'myschema', 'mytable', 1, NULL, 'none'

    exec sp_estimate_data_compression_savings 'myschema', 'mytable', 1, NULL, 'row'

    exec sp_estimate_data_compression_savings 'myschema', 'mytable', 1, NULL, 'page'

    With compression = none you check savings because of defragmentation.

    Replay your average load and see how much CPU usage will increase.

    Each time compresses row or page is accessed (e.g. for reading) it must be decomressed using CPU.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths