In order to query the configuration settings of a server you can run this sproc:
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:
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.