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]:

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

Advertisements