SQL Server Maintenance: 10 Things to Keep Your SQL Server Running Well

I’m not really a car guy. My daily driver is 10 years old but it serves me well. It’s reliable. It gets me where I want to go and back again. And I want to keep it that way so I maintain it. I change the oil regularly, I rotate the tires almost as often, and I even replaced timing belt when the odometer reached 125,000 miles, just as the manufacturer recommended. To keep my car running well requires regular maintenance.

The same is true for SQL Server. A well maintained SQL Server is more reliable, performs better, and is more secure than one that’s neglected.

Required maintenance to keep your SQL Server running well

Like a car, SQL Server generally starts off running well. But to keep it running at peak performance, and to prevent an untimely and disruptive issue, you’ve got to maintain it.

Here are ten maintenance activities we recommend that will help.

1. Regularly backup your databases. Backups are crucial for protecting your data and ensuring that you can recover should a crisis happen.
2. Verify your backups. Regularly perform test restores of key databases. Run integrity checks on the restored databases. Document the process. See How Often Should I Test My SQL Server Backups?
3. Maintain indexes. Keeping your indexes optimized helps with database performance. We recommend Ola Hallengren’s SQL Server Backup, Integrity Check, Index and Statistics Maintenance
4. Update the statistics. Regularly updating statistics helps the SQL Server query optimizer generate better query plans. Bad statistics = bad query plans = bad performance.
5. Proactively monitor your SQL Servers. SQL Server has some alerting and monitoring capabilities. Use them. Third-party tools provide far greater insight into what’s going on under the hood.
6. Implement security best practices. Enforce security best practices such as separation of duties, the principle of least privilege, SQL Auditing, etc. See Who Has sysadmin Access to your SQL Servers?
7. Apply SQL Server patches. Keep your SQL Servers up to date with the latest security patches and Cumulative Updates to ensure that it is running efficiently and securely. See Should I Upgrade SQL Servers that Are Out of Support?
8. Daily health checks. Perform regular health checks to identify potential problems. Don’t rely on alerting and monitoring. Review the SQL Server logs daily. See Do I Still Need a SQL Server Health Check?
9. Monitor server resources. Monitor resources such as CPU, memory, and disk usage to ensure that the server can handle the load. Compile trends over time such as database growth, etc, so you can predict when additional resources will be required.
10. Performance tuning. Although this may not strictly fall under maintenance, regularly reviewing the most resource intensive operations and looking for ways to optimize them will help to keep your SQL Server running. See SQL Server Performance Tuning and the OODA Loop

That sounds like a lot

These ten maintenance activities will help. They will help improve your SQL Server’s performance, reliability, and security. That’s good. No one wants to be stranded by a car or by SQL Server.

However, the list above is not comprehensive. It’s a start. There are many other activities that can be regularly done to help your SQL Server, things an experienced DBA does on a regular basis.

• Translating and implementing the organization’s Recovery Point Objectives (RPOs) and Recovery Time Objectives (RTOs) in the organizations SQL Server estate
• Planning, testing, and documenting Disaster Recovery scenarios
• Implementing High Availability requirements
• Benchmarking performance and predicting when additional resources will be required
• Coordinating workload placement within the SQL estate
• Evaluating cloud options and comparing to colocation sites
• Reviewing problematic code with application developers
• Providing insights to and coordinating with venders to improve performance
• Documenting the SQL Server estate

We do these things and more for our SEROShield DBA Team as a Service clients.

If you’d like to learn more, contact us to schedule a short introductory call.