How to Use SP CONFIGURE
|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)
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.
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.
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
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:
This wiki article was adapted from a blog post by Mike Walsh.