How to use sp_configure in SQL Server

How to use sp_configure in SQL Server

2018 Update – Straight Up SQL Server Tips posts 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.

[/et_pb_text][/et_pb_column_inner][/et_pb_row_inner][/et_pb_column][et_pb_column type=”1_3″][et_pb_sidebar orientation=”left” area=”sidebar-1″ background_layout=”light” remove_border=”on”]

[/et_pb_sidebar][/et_pb_column][/et_pb_section]

Subscribe for Updates

Name

13 thoughts on “How to use sp_configure in SQL Server”

  1. Actually, config_value and run_value may be different depending on if the SQLSERVER service has been restarted since the parameter was set….

    Reply
  2. Hey Laura – I just re-read my post and I see why one could be confused but I think we are saying the same thing. The below quote is from the blog post:

    "run_value – What value is currently running? Difference between this and above? You make a change but don’t restart or run reconfigure: The config_value and run_value will be different until that reconfigure (if dynamic) or restart."

    I will clarify that in an update today but what I am saying is what you said. The Config Value and Run Value will be different until you either restart (if the setting is not dynamic) or run reconfigure (if the setting is dynamic) SQL Server.

    Reply
  3. Thanks for the reminder to look up options. Interesting how many options that were introduced in 2005 are depreciated in 2008. (A lesson in "Think before doing"?) Also interesting that Microsoft included a user rant in their books online. (What the heck is "transform noise words".)

    I ran into a situation in SQL 2000 that may be of interest. We had AWE enabled set on and left 2GB available on an 8GB machine, except we had clustered SQL set up. During a maintenance window I asked to fail over one of the servers so I could install the new software and restart the server. That failed. The original server was using its 6GB and the new SQL instance tried to grab 6GB without checking to see if the memory was available.

    IE Make sure you have the memory available to run both instances of SQL on one server when you set up clusters.

    PS Since you can list the options, I prefer options fully spelled out. Just like I don’t like old style joins.

    Reply
  4. Never even noticed the transform noise warning option. 🙂
    Glad you liked the post, Ken. Great point about making sure your settings work on both nodes. I’ve been bitten by similar "oopses" in the past.. Made a change to local policies on active node in a pinch for an issue.. "I’ll get to the passive node when I can." Well sometimes "when I can" doesn’t happen until a failover happens and users call.

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This