Apply SQL Server Data Compression

SQL Server data compression is used to save space on disk storage, as well as in memory. The CPU overhead is negligible in certain cases, and we recommend applying the following compression scripts as the CPU overhead has been analyzed for the SentryOne application. It's also recommended that you do the following when applying data compression: 

  • Decide if you'll apply compression all at once or over a couple of days.
  • Consider running sp_spaceused before and after applying compression to see the impact in your database.
  • Use sp_estimate_data_compression_savings, SSMS reports, or SQL Sentry Performance Advisor to know the sizes of these indexes in your database.

Sort_in_tempdb may be On or Off, but On is the recommended setting. Indexes may be compressed online or offline, while offline locks the table. Set this to the option that best suits your environment. 

Note:  Consider applying compression to the smaller objects before the larger ones. This is recommended to reduce the need for additional disk space during the index changes.

For more information, see the Applying Data Compression to the SQL Sentry Database Part 2 and Part 5 articles. 

Important:  If you have the SentryOne Scalability Pack implemented in your SentryOne database, most of these tables are already using the columnstore compression. You may, however, apply the data compression scripts for the EventSourceHistory and PerformanceAnalysisTraceData tables.

See the Installation Recommendations article for more information about the SentryOne Scalability Pack.

Compression Scripts

-- 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);
-- 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);
-- 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);
-- PerformanceAnalysisDataRollup8

-- estimate compression savings 
EXEC sys.sp_estimate_data_compression_savings 
@schema_name = N'dbo', 
@object_name = N'PerformanceAnalysisDataRollup8',
@index_id = NULL,
@partition_number = NULL, 
@data_compression = N'PAGE'; 

-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisDataRollup ON dbo.PerformanceAnalysisDataRollup8 
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
-- PerformanceAnalysisDataRollup11

-- estimate compression savings 
EXEC sys.sp_estimate_data_compression_savings 
@schema_name = N'dbo', 
@object_name = N'PerformanceAnalysisDataRollup11',
@index_id = NULL,
@partition_number = NULL, 
@data_compression = N'PAGE'; 

-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisDataRollup ON dbo.PerformanceAnalysisDataRollup11 
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
-- PerformanceAnalysisDataRollup12

-- estimate compression savings 
EXEC sys.sp_estimate_data_compression_savings 
@schema_name = N'dbo', 
@object_name = N'PerformanceAnalysisDataRollup12',
@index_id = NULL,
@partition_number = NULL, 
@data_compression = N'PAGE'; 

-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisDataRollup ON dbo.PerformanceAnalysisDataRollup12 
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
-- PerformanceAnalysisDataRollup13

-- estimate compression savings 
EXEC sys.sp_estimate_data_compression_savings 
@schema_name = N'dbo', 
@object_name = N'PerformanceAnalysisDataRollup13',
@index_id = NULL,
@partition_number = NULL, 
@data_compression = N'PAGE'; 

-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisDataRollup ON dbo.PerformanceAnalysisDataRollup13 
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
-- 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);

-- 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);

-- PerformanceAnalysisDatabaseCounter

-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings 
 @schema_name = N'dbo',  
 @object_name = N'PerformanceAnalysisDatabaseCounter',
 @index_id = NULL,
 @partition_number = NULL,   
 @data_compression = N'PAGE';
 
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisData_Wide ON dbo.PerformanceAnalysisDatabaseCounter 
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

-- 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);

-- PerformanceAnalysisSQLDBCounter

-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings 
 @schema_name = N'dbo',  
 @object_name = N'PerformanceAnalysisSQLDBCounter',
 @index_id = NULL,
 @partition_number = NULL,   
 @data_compression = N'PAGE';
 
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisData_Wide ON dbo.PerformanceAnalysisSQLDBCounter 
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

-- PerformanceAnalysisTableAndIndexCounter

-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings 
 @schema_name = N'dbo',  
 @object_name = N'PerformanceAnalysisTableAndIndexCounter',
 @index_id = NULL,
 @partition_number = NULL,   
 @data_compression = N'PAGE';
 
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisData_Wide ON dbo.PerformanceAnalysisTableAndIndexCounter 
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

-- PerformanceAnalysisTintriCounter

-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings 
 @schema_name = N'dbo',  
 @object_name = N'PerformanceAnalysisTintriCounter',
 @index_id = NULL,
 @partition_number = NULL,   
 @data_compression = N'PAGE';
 
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisData_Wide ON dbo.PerformanceAnalysisTintriCounter 
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

-- PerformanceAnalysisVMCounter

-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings 
 @schema_name = N'dbo',  
 @object_name = N'PerformanceAnalysisVMCounter',
 @index_id = NULL,
 @partition_number = NULL,   
 @data_compression = N'PAGE';
 
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisData_Wide ON dbo.PerformanceAnalysisVMCounter 
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

-- 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);

-- 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);