As promised, this post contains the T-SQL for applying data compression as recommended in the SQL Sentry database (see the previous segments in this series: Part 1 | Part 2 | Part 3).
I would suggest doing this in a test environment first, especially to give you an idea of how long it will take to apply compression in your database.
Note that compression should be applied to the smaller objects before the larger ones. This is a recommended practice to reduce the need for additional disk space while applying compression. You will have a copy of the existing index as well as the index while it compresses, until compression is complete.
SORT_IN_TEMPDB may be ON or OFF, but ON is the recommended setting; therefore, it is set to ON in the alter index statements. Indexes may be compressed ONLINE or OFFLINE, with the caveats that ONLINE will take longer and utilize more CPU, while OFFLINE will lock the table. You should set these to the option that best suits your environment.
You may wish to apply the compression all at once or over a number of days. Remember to create before and after baselines whenever you make these types of changes.
Consider running sp_spaceused before and after applying compression, for each table or for the total to see the impact in your database. It would be a good idea to use sp_estimate_data_compression_savings, SSMS reports, or SQL Sentry Performance Advisor to know the sizes of these indexes in your database before and after compression, as well as to assist in running them in the best order for your database.
-- Table #1
-- PerformanceAnalysisDataRollup14
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisDataRollup14',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'PAGE';
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisDataRollup ON dbo.PerformanceAnalysisDataRollup14
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);PerformanceAnalysisDataRollup14
-- Table #2
-- PerformanceAnalysisDataRollup6
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisDataRollup6',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'PAGE';
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisDataRollup ON dbo.PerformanceAnalysisDataRollup6
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
T-SQL to apply compression to PerformanceAnalysisDataRollup6
-- Table #3
-- PerformanceAnalysisDataRollup4
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisDataRollup4',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'PAGE';
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisDataRollup ON dbo.PerformanceAnalysisDataRollup4
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
T-SQL to apply compression to PerformanceAnalysisDataRollup4
-- Table #4
-- PerformanceAnalysisDataRollup2
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisDataRollup2',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'PAGE';
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisDataRollup ON dbo.PerformanceAnalysisDataRollup2
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
T-SQL to apply compression to PerformanceAnalysisDataRollup2
-- Table #5
-- EventSourceHistory
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'EventSourceHistory',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'PAGE';
-- alter indexes to use page compression
ALTER INDEX IX_MaxIDs ON dbo.EventSourceHistory
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX IX_IncompleteRecs ON dbo.EventSourceHistory
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX IX_Unique1 ON dbo.EventSourceHistory
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX IX_Unique2 ON dbo.EventSourceHistory
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX IX_FailedObjectsInRange ON dbo.EventSourceHistory
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX IX_GlobalViews ON dbo.EventSourceHistory
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX IX_DetailInserts ON dbo.EventSourceHistory
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX PK_EventHistory ON dbo.EventSourceHistory
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
T-SQL to apply compression to EventSourceHistory
-- Table #6
-- PerformanceAnalysisTraceData
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisTraceData',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'ROW';
-- alter indexes to use page compression
ALTER INDEX IX_MaxIDs ON dbo.PerformanceAnalysisTraceData
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX IX_PerformanceAnalysisTraceData_Wide ON dbo.PerformanceAnalysisTraceData
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
-- alter index to use row compression
ALTER INDEX PK_PerformanceAnalysisTraceData ON dbo.PerformanceAnalysisTraceData
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = ROW);
T-SQL to apply compression to PerformanceAnalysisTraceData
-- Table #7
-- PerformanceAnalysisData
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisData',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'PAGE';
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisData_Wide ON dbo.PerformanceAnalysisData
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
T-SQL to apply compression to PerformanceAnalysisData
-- Table #8
-- PerformanceAnalysisDataDiskCounter
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisDataDiskCounter',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'PAGE';
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisData_Wide ON dbo. PerformanceAnalysisDataDiskCounter
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
T-SQL to apply compression to PerformanceAnalysisDataDiskCounter
-- Table #9
-- EventSourceHistoryDetail
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'EventSourceHistoryDetail',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'ROW';
-- alter indexes to use page compression
ALTER INDEX IX_MaxIDs ON dbo.EventSourceHistoryDetail
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX IX_MasterDetailCorrelationTrigger ON dbo.EventSourceHistoryDetail
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX IX_Unique2 ON dbo.EventSourceHistoryDetail
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX IX_Unique1 ON dbo.EventSourceHistoryDetail
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX IX_IncompleteRecs ON dbo.EventSourceHistoryDetail
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
-- alter index to use row compression
ALTER INDEX PK_EventHistoryDetail ON dbo.EventSourceHistoryDetail
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = ROW);T-SQL to apply compression to EventSourceHistoryDetail
The size on disk of other tables in this database tend to sharply decline after the top indexes listed in the above T-SQL. We may explore the benefits of compressing additional indexes in the future, but for now, this is the list of recommended actions.
Thank you so much for reading this series; I look forward to posting another one soon!
Melissa is the Product Education Manager at SentryOne. Melissa has over a decade of experience with SQL Server through software performance and scalability testing, analysis and research projects, application development, and technical support.
