SSISDB 2012 – Data Compression

A tip to decrease the storage size of SSISDB is to apply data compression to the 4 largest tables:
(this feature is only available in the Enterprise-edition of SQL Server)


USE [SSISDB]

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)


In my SSISDB it saved me approximately -85% on [internal].[event_messages]:

SSISDB-event-messages-data-compression-page-estimation


Don’t forget to create indexes on your SSISDB:
https://clinthuijbers.wordpress.com/2014/08/14/ssisdb-2012-catalog-indexing-recommendations/








Advertisements

3 thoughts on “SSISDB 2012 – Data Compression

  1. Pingback: SSISDB 2012 – Catalog Indexing Recommendations | Clint Huijbers' Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s