SQL SERVER – Server Configurations Options – Catalog View – sys.configurations

The sys.configuration catalog view allow us to see the same values than sp_configure with “show advanced options” enabled. Even this Catalog view show us extra information because is possible to know if an option is or is not an advanced option, or if is or is not dynamic option.

Important Columns,

Value: Configured Value for the option

Minimum: Minimum Value for the option

Maximum: Maximum Value for the option

Value_in_use: current running value for the option.

Is_dynamic: it column allow us to know if the option is dynamic or not. 1 = the value takes effect when the RECONFIGURE statement is executed. 0 = the value takes effect when the SQL Server is restarted.

Is_advanced: it column allow us to know if the option is advanced. 1 = advanced option, it means that the option is displayed and can be changed just when “show advanced options” is set through sp_configure.

Now we are going to see statements using sys.configurations that are equivalent than sp_configure.

Normal View:

Using sys.configurations

select * from sys.configurations where is_advanced = 0

Using sp_configure:

sp_configure 'show advanced options', 0

GO

RECONFIGURE with override

GO

sp_configure

GO

Advanced Option View:

Using sys.configurations

select * from sys.configurations where is_advanced = 1

Using sp_configure:

sp_configure 'show advanced options', 1

GO

RECONFIGURE with override

GO

sp_configure

GO

Dynamic Option View

Using sys.configurations

To see all the dynamic options:

select * from sys.configurations where is_dynamic = 1

To see all the non dynamic options:

select * from sys.configurations where is_dynamic = 0

Using sp_configure is impossible to know if an option is dynamic.

Conclusion.

Using sys.configurations to see the server configuration is better than sp_configure because with a single select statement is possible to view all the server options with extra information as is_dynamic and is_advanced values, this extra information is impossible to obtain with sp_configure. Any changed necessary on the server configuration has to be performed with sp_configure.