Listing Non-Default Server Settings From sys.configurations

Inherited configuration choices need an inventory before they need another change. Non-default server settings reveal differences from a chosen baseline, but they do not explain why those differences exist. Start with a read-only inventory and the exact engine version.

Record the Server and Baseline Version

The catalog exposes current configuration values, not a complete factory-default dictionary for every option and version. Supply a reviewed baseline explicitly. Record which engine versions it covers, which options it includes, and when its defaults were verified.

I keep the baseline narrow enough to explain every row. A long copied list with no version context looks complete while hiding outdated assumptions. The baseline should distinguish documented option defaults from organization-approved settings. Those are different comparisons and should have different report labels.

SELECT @@SERVERNAME AS ServerName,SYSDATETIMEOFFSET() AS CapturedAt,
 SERVERPROPERTY('Edition') AS Edition,
 SERVERPROPERTY('ProductVersion') AS ProductVersion,
 SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion;
SELECT name,value,value_in_use,minimum,maximum,is_dynamic,is_advanced
FROM sys.configurations ORDER BY name;

Preserve the full snapshot even when the first comparison covers only a few options. That lets a later reviewer see what was outside the baseline. A missing baseline entry means unreviewed, not default. Do not make absence look like a passed configuration check.

Define a Baseline for Non-Default Server Settings

The example covers SQL Server major versions fifteen, sixteen, and seventeen. It compares three common documented option defaults. MAXDOP zero is the option's default representation, while setup can choose a hardware-based setting on current installations. A difference therefore does not prove somebody changed it after installation.

DECLARE @Major int=CONVERT(int,SERVERPROPERTY('ProductMajorVersion'));
WITH Baseline AS
(
 SELECT v.MajorVersion,d.OptionName,d.DefaultValue
 FROM (VALUES(15),(16),(17)) v(MajorVersion)
 CROSS JOIN
 (VALUES(N'max degree of parallelism',0),
        (N'cost threshold for parallelism',5),
        (N'optimize for ad hoc workloads',0)) d(OptionName,DefaultValue)
)
SELECT c.name,b.DefaultValue,
 CONVERT(int,c.value) AS ConfiguredValue,
 CONVERT(int,c.value_in_use) AS ActiveValue,c.is_dynamic
FROM Baseline b JOIN sys.configurations c ON c.name=b.OptionName
WHERE b.MajorVersion=@Major
 AND CONVERT(int,c.value_in_use)<>b.DefaultValue
ORDER BY c.name;

Maintain that list when adding another option or engine version. Do not assume every default is unchanged because these three are familiar. Some options also use special values whose effective interpretation differs from a simple integer comparison. Document such normalization instead of silently treating it as a mismatch.

The cross join is a concise representation of the reviewed baseline for these versions. In a larger inventory, store version, option name, default value, and reference details in an approved administrative table. Keep its maintenance ownership clear.

Non-Default Server Settings Are Not Recommendations

A non-default MAXDOP can reflect deliberate topology-aware tuning. A changed cost threshold can reflect a reviewed workload decision. Optimize for ad hoc workloads can address a specific plan-cache pattern. This inventory does not decide whether those choices still help.

Read the existing change records before proposing a reset. The default is a reference point, not automatically the best production setting. A server configured entirely at defaults can still have serious performance problems. A server with several deviations can be operating as designed.

I ask for the reason and measurement behind important differences. Which workload problem justified the value, and has that workload changed? That makes the review actionable without turning every non-default row into a warning label.

Inspect Configured and Active Differences

Value is the configured setting. Value_in_use describes the active setting. A mismatch can indicate that a change has not been applied successfully or that a nondynamic setting awaits the required restart process. It is not correct to label every mismatch restart required.

SELECT name,value AS ConfiguredValue,value_in_use AS ActiveValue,
 is_dynamic,
 CASE WHEN is_dynamic=0 THEN 'Review non-dynamic activation and restart requirements'
      ELSE 'Review whether the dynamic change was applied successfully' END
 AS ReviewAction
FROM sys.configurations
WHERE value<>value_in_use ORDER BY name;

On my test instance, this query returned min server memory (MB) with 0 configured and 16 active. That pair needs the option-specific reading described below before anyone calls it pending. This query generates descriptive review actions only. It executes no RECONFIGURE statement and changes no values. Check administrative history before doing anything with a pending setting. Applying it could activate somebody else's unfinished change, which deserves its own review and approval.

Some effective-value representations need option-specific interpretation. Check the documented meaning before treating a difference as pending. Keep the original catalog values in the report so another administrator can verify that interpretation rather than trusting a normalized label alone.

Expose Options Outside the Baseline

A three-option report is intentionally incomplete. Show that boundary explicitly. The next query lists the remaining options without classifying them as default or non-default. This prevents a compact reviewed list from being mistaken for a complete instance audit.

SELECT name,value,value_in_use,is_dynamic
FROM sys.configurations
WHERE name NOT IN
 (N'max degree of parallelism',N'cost threshold for parallelism',
  N'optimize for ad hoc workloads')
ORDER BY name;

Expanding the baseline should be deliberate. Verify each new option's default, available versions, type, and special-value semantics. Do not generate a default from its minimum or maximum. Those limits describe allowed ranges, not the installed default.

The catalog also cannot reconstruct who changed a value or when. Audit and administrative records can provide that history when available. Mark the origin unknown if no evidence exists. Guessing that a previous administrator tuned it adds a story, not a fact.

Check Database-Scoped Configuration Separately

Database-scoped options can override or supplement instance behavior. Query them within each reviewed database. Current releases expose a default-state flag alongside the values. Preserve secondary-replica values because their interpretation can differ from the primary setting.

SELECT DB_NAME() AS DatabaseName,name,value,value_for_secondary,is_value_default
FROM sys.database_scoped_configurations
ORDER BY name;

A server MAXDOP value alone does not describe every statement's parallelism policy. Database settings, workload controls, and query hints can contribute. Keep those layers separate while explaining their combined effect on a specific query.

Repeat the database inventory with explicit coverage when reviewing an entire instance. Inaccessible or offline databases remain unreviewed. A successful server-level query does not make that database-level coverage complete.

Preserve the Non-Default Server Settings Review

Save capture time, engine version, baseline identity, compared values, and the owner's explanation. Record approved exceptions separately from unresolved differences. If a change is later proposed, it should name the specific option, expected effect, test evidence, and rollback setting.

Do not combine inventory collection with automatic resetting. The controls are connected to a running workload, not a showroom demonstration. A configuration baseline is a ruler, not a remote control. The useful result is a clear account of what differs, what is pending, and which differences still need evidence.

When comparing two instances, use the same baseline version and capture method for both. Differences in edition or setup choices can explain different values without either server being misconfigured. Preserve those explanations beside the values. If the report later becomes an organizational drift check, compare against the approved organizational target separately from the documented option default. That keeps a policy violation from being confused with an intentional tuning exception. Non-default server settings need a reviewed baseline and an identified owner. Keep pending activation separate when explaining non-default server settings to the next administrator.

Related reading on this blog: Which Settings Change in sp_configure Needs Restart to Take Effect? and SQL SERVER 2019: New Values in Sys.Configurations.

A non-default setting is not proof of a configuration mistake, it is a difference whose origin and workload purpose need review.

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