SentryOne Database Maintenance

Overview

It's important that regular maintenance activities be performed on the SQL Sentry 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

SQL Sentry 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 SQL Sentry 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 SQL Sentry 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 SQL Sentry 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

Important:  If you are using the scalability pack, then there are partitioned tables with clustered columnstore indexes in the SQL Sentry database that must be excluded from regular index maintenance. 

Additional Information: For a complete list of tables to exclude from index and statistics maintenance if you are using the scalability pack, see the SentryOne Scalability Pack Tables to Exclude from Index Maintenance.sql query on GitHub.

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.

SentryOne Report Viewer Index Fragmentation By Database
Index Fragmentation By Database

SentryOne Report Viewer Index Fragmentation and Usage
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 SQL Sentry database. See the Fragmentation Manager article for additional information.

High Availability / Disaster Recovery

The SQL Sentry 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 SQL Sentry 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 SQL Sentry database for increased performance. See the Apply SQL Server Data Compression article for full details and scripts.