How to use SP_CONFIGURE in SQL Server

November 2013 Update: Over at the Linchpin People blog, I’ve created and shared a script that I use to look at configuration values in SQL Server. Script is free to download and currently works in SQL Server 2008, 2008R2 and 2012. Update soon to follow with support for 2005 and 2000. You can see the post about and a link to the SQL Server Configuration Check Script here.

 

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. I recently learned a tip that I had never known from a Kalen Delaney course (that tip is below) and I’ve saved time and memory using it.

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 my RSS feed to get updated with content like this in the future.)

What is SP_CONFIGURE?

Simply put – it’s a tool to display and change SQL Server settings. It seems to me that a lot of reluctant and newer DBAs are well versed in the GUI based configuration options but not always changing them through a query (or even viewing them). 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?

Well it depends on what you are trying to do. Here are some tips for each use:

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.

Tags: , , , ,

10 Comments

Leave a comment
  1. LauraV October 26, 2009 at 16:25 #

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

  2. Mike Walsh October 26, 2009 at 17:01 #

    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.

  3. Ken Lee October 26, 2009 at 23:58 #

    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.

  4. Ken Lee October 27, 2009 at 00:00 #

    By the way, I am not asking you "what the heck…".

  5. Mike Walsh October 27, 2009 at 00:39 #

    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.

Trackbacks/Pingbacks

  1. Get the Server level default connection properties for SQL Server using T-SQL | Sankar Reddy, SQL Server Developer/DBA - February 8, 2010

    [...] How to use SP_CONFIGURE in SQL Server T-SQL Bitwise Operations February 8th, 2010 | Category: SQL Server, SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication [...]

  2. Nitin Chandra Salgar - March 2, 2010

    @mike_walsh RE: http://bit.ly/c8CXZw Is there any way of reconfiguring without restarting the instance?

  3. SQL SERVER – SQL Server Configuration Checking – A Must Do for Every DBA – Notes from the Field #004 | Journey to SQL Authority with Pinal Dave - November 25, 2013

    […] 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 […]

  4. Check Your SQL Server Configurations | Straight Path Solutions, a SQL Server ConsultancyStraight Path Solutions, a SQL Server Consultancy - November 27, 2013

    […] blogged about sp_configure and sys.configurations and how you can look at them. But to be honest, I’ve always just sort of selected all of […]

  5. Check Your SQL Server Configuration - SQL Server - SQL Server - Toad World - November 27, 2013

    […] blogged about sp_configure and sys.configurations and how you can look at them. But to be honest, I’ve always just sort of selected all of […]

Leave a Reply