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 OFFDisable auto create stats
SET AUTO_CREATE_STATISTICS OFFUpdate 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:
|
|
|
|
|
|
|
|
|
|
|
|
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.
 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 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.
