Interview Question of the Week #061 – How to Retrieve SQL Server Configuration?

Question: How can I retrieve SQL Server configuration programmatically? Query sys.configurations for the configured and running values, or use sp_configure to display its available server options.

When this came up in an interview, the common answer was to open the server’s Properties dialog. That is useful, but the question asked for a script and the detail behind the current configuration.

-- Read-only, including advanced options, without changing server settings.
SELECT name,value AS ConfiguredValue,value_in_use AS RunningValue,
       is_dynamic,is_advanced,description
FROM sys.configurations ORDER BY name;
EXEC sys.sp_configure;
SELECT name,value,value_in_use,is_dynamic,is_advanced
FROM sys.configurations WHERE value<>value_in_use ORDER BY name;

-- Illustrative change syntax, not a recommendation for this server:
-- EXEC sys.sp_configure N'show advanced options',1;
-- RECONFIGURE;
-- EXEC sys.sp_configure N'max server memory (MB)',2048;
-- RECONFIGURE;
-- Restore the previous show-advanced-options value and RECONFIGURE.
-- Do not blindly restore0 if the prior value was1.

Start with a read-only inventory

sys.configurations returns advanced options without changing show advanced options. A bare sp_configure displays the options currently visible through that procedure. The original script enabled advanced options just to inspect them, then attempted to turn them off without the final RECONFIGURE.

Changing a global setting merely to list values is unnecessary when the catalog supplies them. The SQL above leaves all configuration unchanged. SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE to read this catalog; use an appropriately authorized connection.

Configured and running values are different questions

value is the configured value, while value_in_use is what the server currently uses. A difference can reflect an unapplied change, a failed RECONFIGURE or a setting needing restart. Some options also have documented normal differences, so do not label every differing row as a forgotten change.

is_dynamic indicates whether the option can take effect after RECONFIGURE without restarting the Database Engine. Read that option’s own documentation before deciding the action.

A memory example is not a memory recommendation

The original 2048 MB command illustrates change syntax. It is not a suitable maximum for every server. Size memory with the operating system, other processes, other SQL instances and the actual workload in mind. Preserve the previous advanced-options setting and apply any approved restoration correctly.

This catalog describes server configuration options, not every setting in SQL Server. Database-scoped configuration, session SET options and other subsystems have their own interfaces.

Microsoft’s sys.configurations reference explains the values, permissions and expected differences. The sp_configure reference documents displaying and changing options.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.