Script to Compress Tables and Indexes

  • JeremyE

    SSCoach

    Points: 15513

    Comments posted to this topic are about the item Script to Compress Tables and Indexes

  • Dennis Fauerbach

    SSC Journeyman

    Points: 89

    Just looking and running this script you need to add a DISTINCT to the select it returns multiple duplicate statements for both ALTER TABLE and ALTER INDEX statements

  • GregoryF

    SSCertifiable

    Points: 6296

    You could improve upon this script by using sys.partitions to only include rebuild statements for tables that are already uncompressed. This script will rebuild all tables, regardless of whether or not they are already compressed.

    /* ----------------------------- */
    Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

  • Soorya

    Ten Centuries

    Points: 1314

    After using row/page/index compression in SQL 2008 DB, I noticed that DB size is increased. Before compression the DB size was 69 GB. After row compression on all tables, size increased to 73GB. Please explain.

  • GregoryF

    SSCertifiable

    Points: 6296

    Soorya (1/15/2012)


    After using row/page/index compression in SQL 2008 DB, I noticed that DB size is increased. Before compression the DB size was 69 GB. After row compression on all tables, size increased to 73GB. Please explain.

    Check in the GUI to make sure that they are compressed. Then check that compression will compress. Compression does not always lead to data savings, especially row compression. It's very dependent on the data that is being compressed.

    /* ----------------------------- */
    Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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