SentryOne Database Maintenance

Overview

It's important that regular maintenance activities be performed on the SentryOne database to ensure optimal performance. The following are suggested practices for performing such maintenance.

Maintenance Schedule Summary

Maintenance should be performed on the following schedule:

Nightly

Update Statistics
Database Backups

Weekly

Database integrity checks (some may choose nightly checks, but depending on your hardware, weekly should be enough for most)

Monthly

Defrag Operations (consider weekly if problems arise)

Maintenance Details

SentryOne Database Maintenance Window

The Maintenance Window should also be configured with the above activity scheduled to avoid excessive Failsafe Notifications.

Statistics

Disable auto update stats

SET AUTO_UPDATE_STATISTICS OFF

Disable auto create stats

SET AUTO_CREATE_STATISTICS OFF

Update Statistics

Set daily statistics maintenance jobs. The following boxes list tables that are suggested for statistics updates. If you have the scalability pack installed, you cannot (nor do not need to) update statistics on the PerformanceAnalysisData... tables.

Tables to update statistics on if not using the scalability pack:

PerformanceAnalysisData	PerformanceAnalysisDataRollup2
PerformanceAnalysisDataDatabaseCounter	PerformanceAnalysisDataRollup4
PerformanceAnalysisDataDiskCounter	PerformanceAnalysisDataRollup6
PerformanceAnalysisDataTableAndIndexCounter	PerformanceAnalysisDataRollup8
EventSourceHistory	EventSourceHistoryDetail

Tables to update statistics on if using the scalability pack:

EventSourceHistory

EventSourceHistoryDetail

For a SentryOne database hosted on a version of SQL Server prior to 2016, consider setting trace flag 2317:

DBCC TRACEON (2371,1);

Additional Information: See the Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server article from Microsoft for more information about the trace flag and compatibility level settings for SQL Server 2016 or later.

Database Backups

The SentryOne database Recovery model defaults to Simple. Nightly backups should be sufficient, but be sure to choose the backup strategy that best fits your business needs.

Database Integrity Checks

Database integrity checks should be performed as often as practical for the SentryOne database.

Additional Information: Database integrity checks can be scheduled through SQL Server Agent Jobs with T-SQL or free solutions such as Ola Hallengren's Integrity Check or Maintenance Plans.

Index Maintenance

Index Rebuilds

Weekly Index rebuilds are recommended. Fragmentation Manager can be used to help you make intelligent decisions about index management in your environment. See the Fragmentation Manager article for additional information.

Index Fragmentation By Database

Index Fragmentation and usage

Index Defragmentation

Fragmentation Manager can be used to help you make intelligent decisions about index management in your monitored environment and the SentryOne database. See the Fragmentation Manager article for additional information.

High Availability / Disaster Recovery

The SentryOne database supports all SQL Server HA/DR options available, including log shipping, clustering, and availability groups.

Note: If using availability groups, you will need to update your hardware key to reflect the availability group ID. See the Hardware Key and Hosting the SentryOne Database On An Availability Group sections of the License Management article for full instructions.

Other

SQL Server Agent Alerts

Set alerts on the following SQL Server errors to alert you to potential database corruption:

Additional Information: See the SQL Server Agent Alerts post from Tim Radney on the SQLPerformance blog for more information about configuring SQL Server Agent alerts.

Apply Data Compression

SQL Server data compression may be applied to certain tables in the SentryOne database for increased performance. See the Apply SQL Server Data Compression article for full details and scripts.