Quick tip: sp_configure and sys.configurations

In order to query the configuration settings of a server you can run this sproc:

exec sp_configure

On a default install of sql server this will return 16 rows. In order to see all rows you need to run this:

exec sp_configure 'show advanced options',1
reconfigure

Now when you run the sproc it returns 70 rows (SQL Server 2008 R2).

If you get curious you can have a look under the hood by running:

sp_helptext 'sp_configure'

This will return the actual code run by the sproc. Including this query:

select name,  
	convert(int, minimum) as minimum,  
	convert(int, maximum) as maximum,  
	convert(int, isnull(value, value_in_use)) as config_value,
	convert(int, value_in_use) as run_value  
from sys.configurations

This gives you all the options all the time – without having to enable the ‘show advanced options’ option.

The other cool thing about querying sys.configurations is that you get some extra columns:

select * from sys.configurations

Of most interest is the column is_dynamic. If this is a 1 then a change to the option will take effect when the reconfigure statement is run. If it is 0 then a SQL Server restart will be required.

The other useful feature is the ability to filter with a where clause. For example – are there any values that have been changed but not yet taken effect?

select * from sys.configurations where value_in_use <> value

Or you might have a subset of values that you like to check on a server:

select name,  
	convert(int, minimum) as minimum,  
	convert(int, maximum) as maximum,  
	convert(int, isnull(value, value_in_use)) as config_value,
	convert(int, value_in_use) as run_value   
from sys.configurations 
where name in (
	'max degree of parallelism'
	,'max server memory (MB)'
	,'backup compression default'
	,'remote admin connections'
)

Of course, as of SQL 2005, direct updates to system tables are not supported so sp_configure must be used to change any values.

Advertisements
This entry was posted in SQL Server, T-SQL. Bookmark the permalink.

4 Responses to Quick tip: sp_configure and sys.configurations

  1. Babuni says:

    Is it required to keep a copy of the configuration details before master rebuilding or Sql Server Fresh installation..If so then how..?

    • matt.bowler says:

      That is a good idea. The results of this query could simple be pasted in to a spreadsheet, Because the options still need to be changed via the sproc sp_configure – resetting these after a rebuild will be a manual process….

  2. raj says:

    hi could someone answer me , when we do
    exec sp_configure ‘show advanced options’,1
    reconfigure

    why we have to do (why we have to turn back to 0 again )

    exec sp_configure ‘show advanced options’,0
    reconfigure

    • matt.bowler says:

      I don’t think you “have to” reset this option to 0. I suppose that would be up to a particular environment and if you had any security or safety concerns around exposing the advanced options so readily.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s