Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

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.


Comments

Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...