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.
EmoticonEmoticon