Is My SQL Server Configured Properly?

Installing SQL Server is surprisingly easy. Download the media, double-click the setup file, answer a few configuration questions, and in less than 15 minutes you have a fully functional database server.

And the server performs well. For a while. Then it slows down and maybe even a database corrupts. You recover by restoring the prior night’s backup. You lost some data but not too much. At least, you’re running again. So, you add some memory and a couple of processors to the virtual machine in hopes that it’ll help. But you begin to wonder if your data is safe, really safe. And if it goes down, can you recover?

Unfortunately, this is a story that happens all too often.

SQL Configuration Matters

Microsoft SQL Server is a robust, well-designed database engine that performs remarkably well even when neglected. But knowing that critical information is not being actively cared for often leads to angst, especially in those who are responsible for the business.

If you have a SQL Server, you also probably have people who depend on it and the information it contains. They need SQL Server to be performant and reliable. Throwing hardware at problems can overcome a multitude of issues but at a cost – a real cost in terms of additional licenses and an emotional cost in terms of uncertainty.

The thing is: the way SQL Server is configured after it is installed can have a dramatic effect on how it performs. We’ve found that most SQL Servers are not configured with industry best practices. In fact, we find that 95% of the SQL Servers we’ve assessed depart from best practices in many areas. And that puts them at risk for poor performance or even data loss. You can download a free pdf of the most common issues we find.

So, we can you do?

Assessing Your SQL Configuration

A review of your SQL Server configuration settings is a great first step toward ensuring your SQL Server can perform well. Confirming that your SQL Servers are up to date and that the settings are consistent with industry-established best practices will give you confidence in knowing that your SQL Server has the foundation for solid execution.

There are several ways to do this.

1. Do You Own Configuration Review

Whether you have 5 or 500 SQL Servers, identify each instance in your SQL Server environment and prioritize the list based on key factors: impact on the business, frequency of issues, and complexity of setup. Review the list and begin assessing the configuration of the highest priority instances. Compare their settings to what they should be.

For example, what are their maximum and minimum memory settings? What values are configured for Maximum Degree of Parallelism and Cost Threshold for Parallelism? How many tempdb files are defined? Is the instance on the latest supported Cumulative Update? Do you have the appropriate maintenance plans defined? What about file growth settings? Etc.

Here are a few links that may help.

• Operating System Best Practice Configurations for SQL Server
• Performance guidelines for SQL Server in Azure Virtual Machines
• Recommended updates and configuration options for SQL Server 2017 and 2016 with high-performance workloads

Doing this by hand can be quite time-consuming, especially when your SQL Server landscape is broad, so automating the approach would be helpful. You can create scripts to check each value of interest. A quick internet search will provide some scripts you can use as a starting point. Brent Ozar has some free scripts; so does Glenn Berry. And there are others. Of course, be cautious with everything you get from the internet.

2. Microsoft SQL Assessment API

Microsoft has recently made assessing your SQL Server instances a bit easier with it’s free SQL Assessment API.  The API employees SQL Server Management Objects (SMO) and PowerShell to compare your SQL Server with a ruleset published in the samples repository. You and even add your own SQL Server configuration parameters to the ruleset to customize the settings as appropriate for your environment.

This API is new enough that I haven’t had a chance to explore it as much as I’d like. However, it looks promising. If you have more than a few SQL Servers to examine, I’d encourage you to see if the API is a good alternative for you.

3. Get Some Outside Help

The first two options are great for organizations that have the time and the internal skill set to evaluate what is a good configuration for their environment and what should be changed. Not all companies have this luxury.

If you don’t have both the time and the knowledge, you can look outside your organization for an external expert to review your settings and provide an unbiased assessment of your SQL Server configurations.

For example, we frequently provide SQL Assessments for our clients. For a fixed price, we examine up to three of SQL Servers and deliver a 40 to 60-page document that describes our findings. It details and prioritizes each setting and provides links to additional information. During a one-hour follow-up call, we provide specific and actionable recommendations to bring these servers into alignment with industry-established best practices.

In the end, our clients are equipped to make the changes themselves, or we assist in the remediation as needed. In either case, they can rest assured that they’ve provided the best configuration possible for their database servers.

Next Steps

Don’t wait until the next “Oh No!” moment to think about your database servers. Get the peace of mind that comes from understanding exactly how your servers are configured. Begin assessing your SQL Server soon.

Not sure where to start? We’ll be glad to help. Let’s talk.