Post reply

SSISDB ( data compression)

  • December 22, 2017 at 9:11 am

    #341407

    Dear all,

    As part of maintaining my SSISDB I am thinking in compress some tables (data compression).

    I saw in some foruns that the tables normally compressed in this database are the below ones:

          ALTER TABLE [internal].[event_messages] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

    ALTER TABLE [internal].[operation_messages] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
    ALTER TABLE [internal].[execution_component_phases] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
    ALTER TABLE [internal].[execution_data_statistics] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE

    But most of my data inside this DB is in other tables, like internal event message context.

    What are you guys using in terms of compression for this particular DB?

    Thank you 

  • December 23, 2017 at 3:12 am

    #1973322

    I also would like to optimize it and think if some such tables should actually get a columnstore 🙂

  • January 4, 2018 at 8:54 am

    #1974489

    Hello,

    For the bigger tables inside SSISDB I have executed the below procedure (just replace the table name) to see what was the expected results:

    EXEC sp_estimate_data_compression_savings

    @schema_name = '',

    @object_name = 'TransactionHistory',

    @index_id = 2,

    @partition_number = NULL,

    @data_compression = 'PAGE' ;

    This will show you what do you have to benefit from doing data compression. Then, for all the tables that have big advantages and because SSIDB is a database with not much updates, you can do the page compression to gain space and to get your i/O and disk latency better.

    Hope this helps.

Viewing 3 posts - 1 through 2 (of 2 total)

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