Schedule Preventative Maintenance for SQL Server: Key Steps to Protect Your Database
“This SQL Server is critical. It’s used 24/7. So, we’re behind on patching, and we disabled our scheduled preventative maintenance jobs because they interfered with production. Our database is just too important to do that. We know that maintenance is important, but we just can’t.”
Does that sound familiar? I hope not. It’s not a good place to be. Scheduled downtime is always better than unscheduled downtime.
So, what can you do about it?
What is preventative maintenance?
So, what do we mean by “preventative maintenance?”
Broadly speaking, preventative maintenance can be thought of as a set of tasks that are performed regularly to keep that SQL Server and the databases it hosts healthy, secure, and reliable. These tasks help optimize performance, ensure data integrity, and prevent potential issues before they become disruptive events.
Examples of scheduled preventative maintenance
Scheduled preventative maintenance frequently includes these tasks.
- Index maintenance: Rebuilding or reorganizing indexes to reduce fragmentation and improve query performance
- Statistics update: Updating database statistics to ensure the query optimizer makes efficient execution plans based on current data distribution
- Example: Update Statistics
- Backup (and restore tests): Performing regular database backups to safeguard data against potential corruption or accidental deletion
- Examples: Backup Overview and Restore and Recovery Overview
- Database integrity checks: Running checks to identify any data inconsistencies or corruption
- Example: DBCC CheckDB
- Monitoring and alerting: Setting up monitoring tools to identify potential problems early through performance metrics and alerts
- Example: Alerts
Of course, this is just a starting point. It’s the base set that we use for our clients. Depending on the workload and scenario for each SQL Server, we adjust what we schedule and how often we schedule it.
What can we do about the “too important SQL Server?”
So, if you have a “friend” who works in an environment where a SQL Server is just too important to schedule preventative maintenance tasks, what can you tell them? What are their options?
Do nothing
You could do nothing and leave things as they are. But, as you can probably guess, that’s not a good option. It will likely get worse—either things will continue to deteriorate, or they may suffer a disruptive event.
Get a SQL Health Check
Is the SQL Server configured to provide a good foundation for performance and reliability? Often, the answer is no. If that’s the case and your configuration settings are set incorrectly for the workload, a SQL Health Check can identify those issues before they become bigger problems.
Perform a baseline
Review the metrics. Is the system I/O bound? Does it have enough memory? Would it benefit from additional cores?
Tune performance
Review the most resource-intensive queries and look for ways to improve them. Can you add a non-clustered covering index? Is there significant blocking occurring? Would some procedures benefit from being rewritten?
Off-load reporting workloads
Transactional systems are typically normalized for performance. Unfortunately, however, that often means that complex reports significantly hinder performance. Moving the reporting workload to a separate system could improve performance.
Implement high availability
If the applications are too important for preventative maintenance, perhaps HA will help. Implementing a failover cluster instance or even an availability group could allow for patching and other changes to be made with minimal disruption.
Separate workloads
If this is a multi-use SQL Server, perhaps some databases may be moved to another SQL Server.
Something to keep in mind
Your friend may not have all of those options available to them. For example, tuning performance for a purchased application often requires close coordination with the vendor. However, trying to do something to maintain your SQL Server is better than doing nothing.
Additional information and assistance
Want to learn more? Here are some other posts that may be helpful.
- Why Is My SQL Server Slow? 14 Common Reasons
- SQL Server Maintenance: 10 Things to Keep Your SQL Server Running Well
- 10 Unseen Things Successful DBAs Do
If you have an important SQL Server that’s not being maintained, we can help. Schedule a no-obligation discovery call and let’s talk.