How to use sp_configure in SQL Server

2018 Update – Straight Up SQL Server Tips posts  – this is an edit as part of our SQL Server tips when we referenced SP_Configure there –> Not much has changed with sp_configure in SQL Server. The below post is still quite current as far as how to use sp_configure or sys.configurations to check and change your SQL Server settings. If you head to our , you’ll see many posts about things such as max memory settings, cost threshold for parallelism, etc.

2016:  I created and shared a script a few years ago that I use to look at configuration values in SQL Server. Script is free to download and currently works in SQL Server 2008, 2008 R2 and 2012. With the many great tools that have come out since, there hasn’t been an update, but one of these days, I may do a 2014 and 2016 version, the same principles apply for the key findings. If you aren’t sure about your SP_Configure best practices? Look into our SQL Server Health Assessment service, we can guide you to the best practices. You can download that quick script to get a sense of some of our recommendations here, though.

A Blog Post About Using SP_CONFIGURE ??

I use SP_Configure a lot. I use it to quickly view server settings or to make config changes. It is quicker and, in my mind, safer than using the GUI in SQL Server Management Studio because you have to type and review before implementing. There are still things to learn here even! I just learned a tip in a Kalen Delaney class about using sp_configure.

That got me thinking about writing a post about SP_CONFIGURE. What it is, what it does and what that tip from Kalen is. So… This post is for you if you find yourself asking, “How do I use SP_Configure?”.

So yes, really, a post about sp_configure.

Want more SQL Server Tips? Subscribe to the RSS feed here and get content like this in the future. Sign up for the spam-free list to the right.

What is SP_CONFIGURE?

Simply put – it’s a tool to display and change SQL Server settings. A lot of reluctant and newer DBAs are well versed in the GUI based configuration options. But not always so knowledgeable about changing them through a query. And even there, not everyone knows which defaults are good and which aren’t. Not all options are changed through a GUI, some are only changeable through sp_configure – read on to learn some tips to using it better.

How do I use SP_CONFIGURE?

It depends on what you are trying to do. Here are some tips for the various uses cases.

View Settings

Take a look at your SQL Settings? Open a query window and just type and run sp_configure; you’ll likely get a partial list of settings. To see them all you have to enable an option called ‘Show Advanced Options’

Change Settings

Be careful.. Changing settings affects your instance, a lack of understanding of a result could dramatically affect your instance, performance, availability, etc. Look up a setting in books online and understand it first! This post is not about the settings, it is about the tool used to change the settings. Books Online is a great, free, resource when you have SQL Server installed, you can also get Books Online on the web (2005,2008). No excuses here about making a setting change without understanding it and testing it (Remember, I like to see empirical evidence before trying something)

Alright, now that my conscience is clear. You use sp_configure to change settings as well. You simply type sp_configure ‘setting name’ (*) followed by the value. Execute that and you either have to restart the instance (if the option is not dynamic – able to be changed on the fly while the instance is running) or execute the statement “RECONFIGURE” if the setting is dynamic.

For example – if you wanted to show advanced options you would have to type:

sp_configure

‘show advanced options’, 1

GO

reconfigure

GO

Then when you run sp_configure again, you will see all of the available options.

* The Tip I learned from Kalen

You don’t have to type the entire option text in! At least in SQL Server 2005 and above. I never knew this. It’s right there in books online but I never went there for sp_configure because I’ve been using it all this time. So maybe this isn’t as exciting to you as it is me. I mean you can copy and paste the value. Perhaps you don’t mix up your L’s when typing Parallelism (see…  I had to pause while typing it…) but I do. You just have to type in an unambiguous portion of the text.. Try it.. Don’t make a change but run: sp_configure ‘degree’ and you will see the setting for max degree of parallelism. This is great!

Output Columns of SP_CONFIGURE

Pretty self explanatory but:

name – The name of the value to be changed, again you can look up the definition for these values in books online.

minimum – The minimum value setting that is allowed

maximum – Yea, the maximum value that is allowed

config_value – What value is currently configured?

run_value – What value is currently running?

Difference Between Config_Value and Run_Value

The difference may already be clear but it’s important to take note. When you first make a change by running exec SP_CONFIGURE (‘parameter’),newvalue the Config_Value has changed only. It is not until you either run reconfigure (if the setting is dynamic – that is no restart of SQL or reboot required) or restart your SQL Server instance (if the setting is not dynamic) that the run_value changes.

SYS.CONFIGURATIONS

The view only twin of sp_configure. You can select from this catalog view to see configuration options. No changing of settings necessary to see all options here since you can’t change them, it is a way to view your options and you can filter with a where clause.

This catalog view has several columns, the ones that are an exact match to a column in sp_configure will be ignored but the rest:

configuration_id – Just an internal ID of the configuration setting. Good for trivia? I don’t use it for anything…

value – same as config_value above

value_in_use – the twin of run_value above.

description – nice. A little less cryptic then the name column here or in sp_configure. I was going to say it might save you a trip to books online but if you didn’t know what it meant before reading a couple more words, you should still visit books online and understand the setting.

is_dynamic – This very helpful tells you if a value is dynamic or not. 1 = Dynamic, just run reconfigure after changing and it changes “on the fly”. 0 = not dynamic – need to stop and start SQL Server service.

is_advanced – Like the above, ever wonder if you have to change the show advanced option to display a value? Well you can find out here. It’s a flag, 1 is yes, 0 is no, like the is_dynamic flag.

Does This Change Require A Restart?

Just running SP_CONFIGURE, you can’t tell, can you? That’s why it’s important to pay attention above to SYS.CONFIGURATIONS (SELECT * FROM sys.Configurations). That is_dynamic value will come in handy. The Next time your manager asks you, “Does changing the max memory in SQL Server require a reboot?” you can look here and see the dynamic flag of 1 meaning, yes it’s dynamic so “no boss, we can change it on the fly.” Or see a value of 0 meaning, “Sorry boss, need to have an outage while we restart SQL Server).

But SP_CONFIGURE flushes the procedure cache!

No, it doesn’t. At least not in SQL 2005 and above. I don’t have access to SQL 2000 to verify that, but I believe it doesn’t there either. Something some people still say that used to be true in 6.5 and below (maybe 7.0 also, and possibly 2000 but I am pretty sure not.. test and see.. test and see..)

There, I gave you a few other basic knowledge items around sp_configure all to justify my shock, awe and desire to tell everyone that you don’t have to type the entire configuration name into sp_configure! Whoever said it’s the simple things in life had me in mind…

Lesson learned: Visit books online on occassion. Even if you think you know it all and it is a simple feature. Always something new to learn.

Also Check Out

I have posted some other tips and tricks of items I wish I knew when starting out or earlier in my career. Some are technical how-to’s, some are DBA career related.

• Advice to beginning DBAs wondering, “Where do I start?!?!”
• Shrinking – several posts on the topic.
• Troubleshooting Methodology Woes (I also wrote an article for SQL Server Central on this topic)
• Empirical Evidence
• Documentation (or how I learned to stop procrastinating and love tolerate a chore necessary task)
• A little Paranoia and Control Freak attitude can help a DBA
• Checklists, Recipes and Algorithms – Learning about these great tools from other professions (Pilots, Chefs and Doctors)
• How do I find all of my SQL Servers? – Maybe after listening to us you’ll want to spend more time with the instances at your network. Check this link out to learn about a tool that I use to find all the SQL Servers in my network.
• Questions to ask a software vendor – Working with a new vendor with their own database? Here are some questions I ask them.
• Benchmarking… Who needs it? – The answer, I hope you will agree, is everyone. Here I talk about how easy it is to do with tools you already have and a tool you can download free from codeplex.