Do I Still Need a SQL Server Health Check?
“My SQL Server hasn’t had any problems. It just works. Do I still need a SQL Server Health Check?”
The TL;DR answer is yes, it’s good to check on your SQL Server regularly.
SQL Server just works, but…
The SQL Server development team has done a great job. They’ve created a database platform that is rich in features, adapts to different types of workloads, and provides a stable database backend for many applications.
Companies of all sizes and in most every industry rely on SQL Server. Some have a fully staffed IT team to administer their SQL estate. Others rely on another IT professional such as a developer or sysadmin to take care of their SQL Servers in their spare time. Many work with companies like The SERO Group that provide a DBA Team as Service.
Regardless of company size, all should regularly review the health of their SQL Servers. No one wants an unpleasant surprise from a SQL Server they depend on.
The better you take care of your SQL Servers, the better they’ll take care of you.
What does a SQL Server Health Check check?
A SQL Server Health Check typically checks factors that influence performance, reliability, and security.
When we perform a SQL Server Health Check, we examine a broad set of configuration parameters and performance metrics. We specifically look for indications that there may be performance issues, security vulnerabilities, reliabilities concerns, recoverability problems, and alerting/monitoring omissions, among other things. The image below shows our SEROShield Insights Health Check dashboard.
The results of a SQL Server Health Check is a list of departures from best practices, things that should be reviewed and possibly remediated.
We prioritize the findings from P1 to P4. P1’s are the greatest concerns. They can affect security, reliability, and integrity. P2’s can affect these as well as performance. P3’s are things that you may want to address at some point depending on the circumstances and applications. And P4’s are generally informational.
Then we build a remediation plan to address the P1’s and P2’s. We classify changes that can be made online, and those that should be made during a maintenance window.
What can a Health Check reveal?
Of course every situation, every business, and every SQL Server is different. But as we’ve examined hundreds of SQL Servers, we find that there are some common finding that frequently pop up. Here are a few.
- Windows Power Plan set incorrectly – see SQL Server Performance and Windows Power Plan
- SQL Server cannot use all processors
- Too few or too many tempdb files – See How to Configure SQL Server tempdb?
- Jobs without failure notifications
- Missing patches/updates – see Is There an Update for My SQL Server?
- Missing maintenance jobs – see When Was the Last Known Good DBCC CHECKDB Integrity Check?
- Default settings that affect performance or recoverability
- Backup issues – see Using VM Snapshots to Backup SQL Server?
- Configurations that don’t support the organization’s RTOs and RPOs – see High Availability and Disaster Recovery in SQL Server
Download our free 5 Common Issues That May Be Putting Your Data at Risk PDF for more common findings.
How often should I have a SQL Server Health Check?
Annually. And daily.
“Wait. What?”
Annual Health Checks
Yes, we recommend an annual comprehensive SQL Server health check for the SQL Servers in your environment. This helps to ensure they are configured with best practices in mind, and affirms that they provide a good foundation for your database applications.
Why? Things can change over time. Maybe infrastructure added additional memory or CPUs but the SQL Server configurations weren’t adjusted? Maybe additional drives were added but no one thought to check the block size? Perhaps employees were given elevated permissions but no longer need them?
Like an annual physical with your doctor, a regular comprehensive SQL Server Health Check can be helpful in spotting potential issues or needed changes.
Daily
Additionally, we recommend checking on the health of your SQL Servers daily.
Review the SQL Server error logs, the agent logs, and the Windows event logs. You can find a wealth of information in there that may identify potential issues before they become disruptive events. Look for unexpected restarts and low storage space availability. Ensure jobs completed successfully failed login attempts.
We actually take it a step further and look for historical trends such as how quickly databases and tables are growing, the frequency at which jobs are failing, and index utilization.
Our SEROShield Insights dashboard provides that curated view of the health of the SQL Servers we monitor.
But, I don’t have time to do that
Yes, we get that. It sounds like a lot of work. But keeping a SQL Server healthy and monitored is far better than having to react to an untimely emergency situation. The old adage, an ounce of prevention is worth a pound of cure holds true for SQL Server.
That’s why we created our SEROShield program. It’s designed to help companies assess their SQL Server estate, and then keep their SQL Servers healthy and performing well through proactive monitoring, daily health checks, and regular maintenance.
Want to learn more about how SERO Group helps organizations manage their SQL Servers? Let’s talk. There’s no obligation.