SQL SERVER – SQL Server Configuration Checking – A Must Do for Every DBA – Notes from the Field #004

[Note from Pinal]: This is a fourth episode of Notes from the Field series. While installing SQL Server many DBA takes pride that they were able to install SQL Server without any special configuration or MS has built such a fantastic installer that all the default values worked since installation. For some specific configuration that worked well, however, every application and business has their own suitable settings and configuration. If you are a DBA who has business running on default configuration you must read this post as it talks about how to understand your business and how to optimally configure your SQL Server.

In this episode of the Notes from the Field series database expert Mike Walsh explains a very crucial issue DBAs and Developer faces in their career – how you perform a configuration checking for your database. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.

As a SQL Server Consultant, I have often performed a WellDBA™ Exam for a client who has installed SQL Server using the “Next-Next-Next-Finish” approach. They’ve accepted all of the defaults, thrown their production databases on the server and just assumed that all of their instance configuration options were happy and safe. I often rant about independent software vendors for various reasons. One of my biggest pet peeves is when they seem to allow their customers to just install with all defaults and not even consider ever changing them. Whether you are deploying your own databases, or those created by a vendor, you should look at your SQL Server configuration options for your instance.

How Do I Do Check My Configuration Options?

SP_Configure is the SQL Server system stored procedure used to set your instance configurations. I’ve blogged about this and the sys.configurations table before here – In that post I go through the ways to use sp_configure and what the columns in sys.configurations mean. The options set by sp_configure control various instance level run-time parameters. Options like the maximum memory for SQL Server to use, or whether to compress backups by default and many other options. You can also change many of these through the GUI in SSMS. Each configuration option is well documented in books online.

You should check your configurations and review to see where they differ from the default settings. You should also review to see where you are using a problematic default setting, as discussed below. Every environment is different, but as you look at these options more frequently and learn about them, you’ll be well on your way to properly managing your environment into the future.

The only problem with just selecting everything out of sys.configuraitons as many scripts do is that you are likely to miss a setting that is different from the default because not too many people have a memory for the default setting of all that this table returns!

A Script To Help

At Linchpin People, we’ve recently released a SQL Server configuration checking script to help look through your configurations. This free download helps you quickly get to the bottom-line of your configuration options. Instead of having to look at all of them, and review each one, our script shows you where you have a setting that is different than the default. It also shows you where you have kept a default that we have seen customers suffering from not changing. This script helps you zero in on your problem areas more quickly.

Summary

Rather than use this post to get into all of the configuration options you should change and why (there are plenty of posts here on Pinal’s blog and other blogs about that as well as Microsoft resources) – let me use these remaining few words as a reminder – Check your configurations, get to know them, understand them and consider changing them. There have been many engagements where simply changing common defaults like Max Server Memory or Cost Threshold for Parallelism have made noticeable differences for our clients. Check out that script in the blog post on our site, review your environment and investigate the “Potentially Bad Defaults” section. Review each setting, research the best answer for your environment, and get your environment on the right path.

If you want to get started with performance analytics and triage of virtualized SQL Servers with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)