SQL Server – System Configuration Values

Many times we need to review the SQL Server instance configuration values while troubleshooting performance problems. We have one view in SQL Server 2005 or higher version that contains a row per server-wide configuration option value in the system.

The catalog view that provides details about all the server wide configuration values is called sys.configurations. sys.configurations view have the following columns as

• configuration_id — Unique ID for the configuration value.
• name — Name of the configuration option.
• value — Configured value for this option.
• minimum — Minimum value for the configuration option.
• Maximum — Maximum value for the configuration option.
• value_in_use — Running value currently in effect for this option.
• description —  Description of the configuration option.
• is_dynamic — 1 = The variable that takes effect when the RECONFIGURE statement is executed.
• is_advanced — 1 = The variable is displayed only when the show advanced option is set.

Example

All Values

SELECT *
FROM sys.configurations
ORDER BY name
GO

All Advanced Options Configurations

SELECT *
FROM sys.configurations
WHERE is_advanced = 1
GO

For more refer detail — http://msdn.microsoft.com/en-us/library/ms188345%28v=sql.105%29.aspx