See Also: Architecture & Configuration - Server-Level Configuration

What is SP_Configure?

SP_Configure is a system stored procedure that you can modify most SQL Server configuration options with. In fact there are more options to configure here than exists through the GUI based tools.

While the GUI based configuration tools (SQL Server Management Studio's options, SQL Server Configuration Manager, etc.) provide an easy interface, using SP_Configure is preferred by many DBAs. It allows more control and can give a DBA the opportunity to script out a change to apply to multiple environments.

How Do I Use Sp_Configure?

That depends on what you are trying to do. You can use it to view or modify server configuration options (Though there is a better way to view those options in 2005/2008, described below)

View Settings

Want to 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', described below.

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.

Alright, now that the disclaimer is done... You use sp_configure to change settings as well as view. You simply type sp_configure 'setting name' (*) followed by the value. Execute that and you either have to restart the instance or execute the statement "RECONFIGURE" depending on if the setting is dynamic or not (explained below)

For example - if you wanted to show advanced options you would 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.

A Shortcut Tip

You don't have to type all of the value when using SP_Configure. You can type an unambiguous portion of a configuration option and the server will understand what you mean. For example SP_Configure 'Degree' would display the configuration settings for "Max Degree of Parallelism."

Output Columns of SP_Configure

• 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 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.

SYS.Configurations

The is a view only twin of sp_configure that comes with SQL Server 2005 and 2008. 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 - (see that is why I said Seriously? A blog post on sp_configure?) obviously the twin of run_value above.
• description - Very nice, a brief description that is 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. A dynamic value is one that simply needs to be changed with a "RECONFIGURE". If it is not dynamic, it requires a restart of the SQL Server Service. 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, "no boss, we can change it on the fly."
• 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.

Author Credits

Mike Walsh

This wiki article was adapted from a blog post by Mike Walsh.

Mike is an experienced SQL Server professional who has worked with SQL Server in various capacities for ten years. He has performed the duties of DBA, developer, architect and performance team lead but always leans on his DBA experience in each role. Most recently he is the Principal Database Administrator and SQL Server SME for a global insurance company. He also helps companies experiencing SQL Server challenges through his small consultancy, Straight Path Solutions.

His online presence includes:

• Blog: http://www.StraightPathSQL.com/blog
• Twitter: http://twitter.com/mike_walsh