Documentation forSQL Sentry

SQL Sentry 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
    • For more information about updating statistics, see Statistics Maintenance
  • Database Backups

Weekly

  • Index rebuilds
    • For more information about Index rebuilds, see Index Maintenance
  • Database integrity checks (some may choose nightly checks, but depending on your hardware, weekly should be enough for most)

Maintenance Details

SQL Sentry Database Maintenance Window

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

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.
  • If the SQL Sentry database recovery model needs to be set to Full (such as for HA/DR purposes), periodic transaction log backups should be taken.

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

Warning:  Statistics must be maintained in the SQL Sentry database depending on whether the Scalability pack has been installed. The Scalability Pack is enabled by default in SQL Sentry versions 2021.1.13 and later, but may have been installed manually on earlier versions.

Verify that the Scalability Pack has been applied on your SQL Sentry installation by executing the following query on the SQL Sentry database:

SELECT IsPADataWriterBufferingEnabled FROM dbo.ApplicationSettings
Note:  A 1 response means the Scalability Pack has been applied, while a 0 response means the pack has not been applied.

Index Rebuilds

Important:  Starting with versions 2021.1.13, and for any installation with the scalability pack installed, there are partitioned tables with clustered columnstore indexes in the SQL Sentry database that must be excluded from regular 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

SentryOne Report Viewer 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.

Statistics Maintenance

Database Settings

The following database-level settings will be turned on by default in future versions, and should be applied in all environments:

ALTER DATABASE SQLSentry SET AUTO_UPDATE_STATISTICS_ASYNC ON
Important:  This setting is enabled to avoid unnecessary auto statistics calls that could impact user experience.

Update Statistics

SolarWinds recommends that you perform maintenance to your SQL Sentry database daily, preferably during quiet periods.

Warning:  Statistics must be maintained in the SQL Sentry database depending on whether the Scalability pack has been installed. The Scalability Pack is enabled by default in SQL Sentry versions 2021.1.13 and later, but may have been installed manually on earlier versions.

Verify that the Scalability Pack has been applied on your SQL Sentry installation by executing the following query on the SQL Sentry database:

SELECT IsPADataWriterBufferingEnabled FROM dbo.ApplicationSettings
Note:  A 1 response means the Scalability Pack has been applied, while a 0 response means the pack has not been applied.

Scalability Pack Installed

Important:  Starting with version 2021.1.13, and for any installation with the scalability pack installed, there are partitioned tables with clustered columnstore indexes in the SQL Sentry database that must be excluded from regular maintenance.
Additional Information: For a complete list of tables to exclude from index and statistics maintenance if you are using the scalability pack, see theSentryOne Scalability Pack Tables to Exclude from Index Maintenance.sql query on GitHub.

If the Scalability Pack is installed, the tables below should have their statistics maintained daily for optimal performance:

Tables to update statistics on if using the scalability pack:

  • EventSourceHistory

  • EventSourceHistoryDetail

Important:  Tables that aren't listed above should have their statistics maintained at least weekly for optimal performance.

Scalability Pack Not Installed

If the Scalability Pack is not installed, the tables below should have their statistics maintained daily for optimal performance:

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

  • PerformanceAnalysisData

  • PerformanceAnalysisDataRollup2

  • PerformanceAnalysisDataRollup4

  • PerformanceAnalysisDataRollup6

  • PerformanceAnalysisDataRollup8

  • PerformanceAnalysisDataDatabaseCounter

  • PerformanceAnalysisDataDiskCounter

  • PerformanceAnalysisDataTableAndIndexCounter

  • EventSourceHistory

  • EventSourceHistoryDetail

Important:  Tables that aren't listed above should have their statistics maintained at least weekly for optimal performance.

Trace Flags

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.

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.