One thing that SQL Server does very well is come pre-configured in a lot of ways. These pre-configured settings would be called defaults. Having default settings is not a bad thing nor is it necessarily a good thing.
For me, the defaults lie somewhere in the middle ground and they are kind of just there. You see, having defaults can be good for a horde of people. On the other hand, the default settings can be far from optimal for your specific conditions.
The real key with default settings is to understand what they are and how to get to them. This article is going to go through some of the basics around one group of these defaults. That group of settings will be accessible via the sp_configure system stored procedure. You may already know some of these basics, and that is ok.
I do hope that there is something you will be able to learn from this basics article. If you are curious, there are more basics articles on my blog – here.
Some Assembly Required…
Three dreaded words we all love to despise but have learned to deal with over the past several years – some assembly required. More and more we find ourselves needing to assemble our own furniture, bookcases, barbecue grills, and bathroom sinks. We do occasionally want some form of set and forget it.
The problem with set it and forget it type of settings (or defaults) is as I mentioned – they don’t always work for every environment. We do occasionally need to manually adjust settings for what is optimal for that 
database, server, and/or environment.
When we fail to reconfigure the defaults, we could end up with a constant firefight that we just don’t ever seem to be able to win.
So how do we find some of these settings that can help us customize our environment for the better (or worse)? Let’s start taking a crack at this cool procedure called sp_configure! Ok, so maybe I oversold that a bit – but there is some coolness to it.
Looking at msdn about sp_configure I can see that it is a procedure to display or change global configuration settings for the current server.
If I run sp_configure without any parameters, I will get a complete result set of the configurable options via this procedure. Let’s look at how easy that is:
Ok, so that was exceptionally easy. I can see that the procedure returns the configurable settings, the max value for the setting, configured value, and the running value for each setting. That is basic information, right? If I want a little more detailed information, guess what? I can query a catalog view to learn even more about the configurations – sys.configurations.
SELECT * FROM sys.configurations c ORDER BY c.name;
That query will also show me (in addition to what I already know from sp_configure) a description for each setting, if the setting is a dynamic setting and whether or not the setting is an advanced configuration (and thus requires “show advanced options” to be enabled). Pro-tip: The procedure just queries the catalog view anyway. Here is a snippet from the proc text.
-- If no option name is given, the procedure will just print out all the -- options and their values. if @configname is NULL begin select name, convert(int, minimum) as minimum, convert(int, maximum) as maximum, convert(int, isnull(value, value_in_use)) as config_value, convert(int, value_in_use) as run_value from sys.configurations where (is_advanced = 0 or @show_advance = 1) order by lower(name) return (0) end
Seeing that we have some configurations that are advanced and there is this option called “show advanced options”, let’s play a little bit with how to enable or disable that setting.
sp_configure 'show advanced options',1
With the result (on my system) being:
We can see there that the configuration had no effect because I already had the setting enabled. Nonetheless, the attempt to change still succeeded. Let’s try it a different way.
I ran a whole bunch of variations there for giggles. Notice how I continue to try different words or length of words until it finally errors? All of them have the same net effect (except the attempt that failed) they will change the configuration “show advanced options”. This is because all that is required (as portrayed in the failure message) is that the term provided is enough to make it unique. The uniqueness requirement (shortcut) is illustrated by this code block from sp_configure.
See the use of the wildcards and the “like” term? This is allowing us to shortcut the configuration name – as long as we use a unique term. If I select a term that is not unique, then the proc will output every configuration option that matches the term I used. From the example I used, I would get this output as duplicates to the term I used.
Ah, I can see the option I need! I can now just copy and paste that option (for the sake of simplicity) into my query and just proceed along my merry way. This is a great shortcut if you can’t remember the exact full config name or if you happen to be really bad at spelling.
The Wrap
In this article I have introduced you to some basics in regards to default server settings and how to quickly see or change those settings. Not every environment is able to rely on set-it and forget-it type of defaults. Adopting the mentality that “some assembly is required” with your environments is a good approach. It will help keep you on top of your configurations at the bare minimum. This article will help serve a decent foundation for some near future articles. Stay tuned!
This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.
