- Release Notes
- SQL Sentry 2025.1 release notes
- SQL Sentry 2024.4 release notes
- SQL Sentry 2024.3.11 release notes
- SQL Sentry 2024.3.9 release notes
- SQL Sentry 2024.3 release notes
- SQL Sentry 2024.2 release notes
- SQL Sentry 2023.4 release notes
- SQL Sentry 2023.3 release notes
- SQL Sentry 2023.2.9 release notes
- SQL Sentry 2023.2 release notes
- SQL Sentry 2023.1 release notes
- SQL Sentry 2022.4.1 release notes
- SQL Sentry 2022.4 release notes
- SQL Sentry 2022.2.0 release notes
- SQL Sentry 2022.3.2 release notes
- SQL Sentry 2021.18.10 release notes
- SQL Sentry 2021.18.8 release notes
- SQL Sentry 2021.18.2 release notes
- SQL Sentry 2021.12.11 release notes
- SQL Sentry 2021.12.2 release notes
- SQL Sentry 2021.8.20 release notes
- SQL Sentry 2021.1.13 release notes
- SQL Sentry 2021.1.10 release notes
- SQL Sentry 2021.1.9 release notes
- SQL Sentry release history
- SQL Sentry release notes aggregator
- Plan Explorer 2024.3 release notes
- Plan Explorer 2024.1 release notes
- Plan Explorer release history
- Install or Upgrade
- SQL Sentry Installation and Upgrade Guide
- SQL Sentry 2025.1 System Requirements
- SQL Sentry Installation Recommendations
- Installing SQL Sentry
- Setup Wizard Installation
- SQL Sentry Enhanced Platform Installer
- SolarWinds Platform Installer for SQL Sentry
- Plan Explorer Installation & Overview
- SQL Sentry Portal Configuration
- Upgrading SQL Sentry
- SQL Sentry EPI Upgrade
- Upgrading an installation to the SolarWinds Platform Installer
- Upgrading the SolarWinds Platform Installer for SQL Sentry
- Uninstalling SQL Sentry
- Get Started
- SQL Sentry Getting Started Guide
- Getting Started Overview
- SQL Sentry Onboarding
- Monitoring Additional Targets
- Getting Started with SQL Sentry Performance Analysis
- Getting Started with SQL Sentry Event Calendar
- Introducing Conditions, Actions, and Settings
- Building SQL Sentry Advisory Conditions
- Configuring Actions
- Configuring Settings
- General Tasks
- Adding Notes
- Environment Health Overview
- SQL Sentry Client Interface
- Getting Started with SQL Sentry Portal
- Plan Explorer Navigation
- Administer
- SQL Sentry Administrator Guide
- Administration Overview
- SQL Sentry Security Overview
- SQL Sentry Rights Based Security
- SQL Sentry Role Based Security
- SQL Sentry Monitoring Service Security
- SQL Sentry Client Security
- SQL Sentry Watching Targets Across Domains
- SQL Sentry Azure SQL Database and Azure Synapse SQL Pools Security
- SQL Sentry Non-Windows Network Environment Security
- SQL Sentry Pass-through Authentication
- SQL Sentry Performance Analysis Security Requirements
- SQL Sentry Performance Analysis Required Ports
- SQL Sentry Advanced topics overview
- SQL Sentry maintenance overview
- SQL Sentry Components and Architecture
- SQL Sentry Monitoring Service Settings
- Performance Analysis Dashboard
- Performance Analysis Baselining
- Performance Overview
- SQL Sentry Performance Metrics
- Performance Analysis Dashboard Retention & Resolution
- SQL Sentry Overview
- SQL Server Performance Metrics
- Performance Analysis Processes
- SQL Sentry Performance Analysis Disk Activity
- Performance Analysis Disk Space
- Performance Analysis Top SQL
- Top SQL Runtime Stats
- SQL Sentry Indexes
- Performance Analysis AlwaysOn
- SQL Sentry Fragmentation Manager
- Query Plans
- Performance Analysis Blocking SQL
- Performance Analysis Deadlocks
- QuickTrace™
- SSAS Overview for SQL Sentry
- VMware Overview for SQL Sentry
- Windows & Hyper-V Overview for SQL Sentry
- Azure SQL Database Overview
- SQL Sentry Azure Synapse SQL Pool Overview
- Analytics platform system Overview for SQL Sentry
- SQL Sentry Calendar & List Views
- SQL Sentry Custom Event Views
- SQL Sentry Event Architecture
- SQL Sentry Event Objects
- SQL Sentry Event Sources
- SQL Sentry Event Chains
- SQL Sentry Filtering Events
- SQL Sentry Job Queuing
- SQL Sentry Sample Views & Shared Resources
- SQL Sentry Job Step / SSIS Logging
- SQL Sentry Runtime Stats
- SQL Sentry Failed Instances
- SQL Sentry Conflict Viewer
- SQL Sentry Conditions
- SQL Sentry Actions & Alerts Overview
- SQL Sentry Reporting Overview
- SQL Sentry Portal Performance Analysis
- SQL Sentry Portal Distributed Databases
- SQL Sentry Portal Custom Charts
- SQL Sentry Portal Health
- SQL Sentry Portal Storage
- SQL Sentry Portal AlwaysOn
- SQL Sentry Portal Top SQL
- SQL Sentry Portal Alerts Log
- SQL Sentry Portal Blocking
- SQL Sentry Portal Deadlocks
- SQL Sentry Portal TempDB
- SQL Sentry Portal VMware Performance
- Portal Security
- Platform Connect
- Getting Started: SQL Sentry Platform on AWS
- Integrated Plan Explorer Overview
Apply SQL Server Data Compression
Important: If you have the SQL Sentry Scalability Pack implemented in your SQL Sentry database, most of these tables are already using partitioned columnstore compression. Do not apply the data compression scripts to those tables.
You may, however, apply the data compression scripts for the tables under the "Event and Other Tables" tab (EventSourceHistory, EventSourceHistoryDetail, PerformanceAnalysisTraceData, PerformanceAnalysisPlanOpTotals, and PerformanceAnalysisTraceQueryStats).
You may, however, apply the data compression scripts for the tables under the "Event and Other Tables" tab (EventSourceHistory, EventSourceHistoryDetail, PerformanceAnalysisTraceData, PerformanceAnalysisPlanOpTotals, and PerformanceAnalysisTraceQueryStats).
See the Installation Recommendations article for more information about the SQL Sentry Scalability Pack.
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 SQL Sentry 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.
Remember that the indexes will be decompressed during index rebuilds, so you should plan to have sufficient disk space for logs and tempdb to account for the full table size. For more information, see the Applying Data Compression to the SQL Sentry Database Part 2 and Part 5 articles.
Remember that the indexes will be decompressed during index rebuilds, so you should plan to have sufficient disk space for logs and tempdb to account for the full table size. For more information, see the Applying Data Compression to the SQL Sentry Database Part 2 and Part 5 articles.
Compression Scripts
The scripts below are also available via the Apply Data Compression to SentryOne Database.sql file in the sentryone/sentryone-sql-scripts repository on GitHub.
Performance Counter Tables
Warning: Do not apply compression to these tables if you have the SQL Sentry Scalability Pack implemented in your SQL Sentry database, because they are already using columnstore compression.
-- 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);
-- PerformanceAnalysisDataDatabaseCounter
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisDataDatabaseCounter',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'PAGE';
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisData_Wide ON dbo.PerformanceAnalysisDataDatabaseCounter
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);
-- PerformanceAnalysisDataSQLDBCounter
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisDataSQLDBCounter',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'PAGE';
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisData_Wide ON dbo.PerformanceAnalysisDataSQLDBCounter
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
-- PerformanceAnalysisDataTableAndIndexCounter
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisDataTableAndIndexCounter',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'PAGE';
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisData_Wide ON dbo.PerformanceAnalysisDataTableAndIndexCounter
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
-- PerformanceAnalysisDataTintriCounter
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisDataTintriCounter',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'PAGE';
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisData_Wide ON dbo.PerformanceAnalysisDataTintriCounter
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
-- PerformanceAnalysisDataVMCounter
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisDataVMCounter',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'PAGE';
-- alter index to use page compression
ALTER INDEX IX_PerformanceAnalysisData_Wide ON dbo.PerformanceAnalysisDataVMCounter
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
Event and Other Tables
Note: You may apply compression to the following tables with or without the SentryOne Scalability Pack.
-- 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';
SELECT index_id, name FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.EventSourceHistory');
-- 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);
--Consider using ROW compression on the clustered index if index maintenance duration is a concern.
ALTER INDEX PK_EventHistory ON dbo.EventSourceHistory
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
-- 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';
SELECT index_id, name FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.EventSourceHistoryDetail');
-- alter indexes to use row compression
ALTER INDEX PK_EventHistoryDetail ON dbo.EventSourceHistoryDetail
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = ROW);
ALTER INDEX IX_EventSourceHistoryID ON dbo.EventSourceHistoryDetail
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = ROW);
-- alter indexes to use page compression
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_MasterDetailCorrelationTrigger 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 IX_MaxIDs ON dbo.EventSourceHistoryDetail
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';
SELECT index_id, name FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.PerformanceAnalysisTraceData');
-- 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);
ALTER INDEX IX_PurgeProcess ON dbo.PerformanceAnalysisTraceData
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = ROW);
-- dbo.PerformanceAnalysisPlanOpTotals
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisPlanOpTotals',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'ROW';
SELECT index_id, name FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.PerformanceAnalysisPlanOpTotals');
-- alter indexes to use row compression
ALTER INDEX PK_PerformanceAnalysisTracePlanOpTotals ON dbo.PerformanceAnalysisPlanOpTotals
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = ROW);
ALTER INDEX IX_PerformanceAnalysisTracePlanOpTotals_Unique ON dbo.PerformanceAnalysisPlanOpTotals
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = ROW);
-- dbo.PerformanceAnalysisTraceQueryStats
-- estimate compression savings
EXEC sys.sp_estimate_data_compression_savings
@schema_name = N'dbo',
@object_name = N'PerformanceAnalysisTraceQueryStats',
@index_id = NULL,
@partition_number = NULL,
@data_compression = N'ROW';
SELECT index_id, name FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.PerformanceAnalysisTraceQueryStats');
-- alter indexes to use row compression
ALTER INDEX PK_PerformanceAnalysisTraceQueryStats ON dbo.PerformanceAnalysisTraceQueryStats
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = ROW);
ALTER INDEX IX_PurgeProcess ON dbo.PerformanceAnalysisTraceQueryStats
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = ROW);
-- alter indexes to use page compression
ALTER INDEX IX_PerformanceAnalysisTraceQueryStats_ObjectLookup ON dbo.PerformanceAnalysisTraceQueryStats
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
ALTER INDEX IX_PerformanceAnalysisTraceQueryStats_Unique ON dbo.PerformanceAnalysisTraceQueryStats
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);