sp_configure, Server Properties, Startup Parameters, Trace Flags, and Configuration Governance

Learning outcomes

The ServiceHub team inherits a script called best_practices.sql. It changes twenty server settings, enables several trace flags, and tells the operator to restart SQL Server. Nobody knows which values were defaults, which changes are dynamic, which are persistent, or which old trace flags are obsolete in SQL Server 2025. Configuration without provenance is operational debt. This lesson replaces “run the tuning script” with a governed configuration model.

1. Scope is the first question for every setting

SQL Server exposes configuration at several layers. sp_configure and sys.configurations describe Database Engine instance settings. ALTER DATABASE options and ALTER DATABASE SCOPED CONFIGURATION apply to one database. Session SET options apply to a connection and can affect semantics or plan reuse. Query hints apply to a statement. Startup parameters are read when the engine process starts. Trace flags can be query, session, or global depending on the documented flag.

When an incident report says “MAXDOP is 4,” ask: server MAXDOP, database-scoped MAXDOP, Resource Governor/workload behavior, or a query hint? A value without scope is not a usable fact.

2. sp_configure: configured value is not always effective value

sp_configure changes instance-level configuration and RECONFIGURE applies the configured values. sys.configurations exposes both value (configured) and value_in_use (effective), plus metadata such as is_dynamic and is_advanced. Some settings take effect immediately; others require restart. Therefore a screenshot of one property page is less useful than a scripted evidence record containing both values and the engine build.

SELECT    name,    value AS configured_value,    value_in_use,    minimum,    maximum,    is_dynamic,    is_advanced,    descriptionFROM sys.configurationsORDER BY name;GOEXEC sys.sp_configure;GO

Advanced options can be hidden from the default sp_configure display unless show advanced options is enabled, but sys.configurations remains a useful inventory. Changing configuration requires ALTER SETTINGS permission (held implicitly by roles such as sysadmin/serveradmin), which is another reason application logins should not possess arbitrary instance configuration rights.

3. RECONFIGURE is not the same as restart

After sp_configure changes a value, RECONFIGURE causes SQL Server to update effective configuration where possible. A dynamic option can normally take effect without restarting the engine; a non-dynamic option can keep a different value and value_in_use until restart. That difference is visible and should be part of change verification.

4. Startup parameters are process-start behavior

Startup parameters such as -d, -l, and -e identify master data/log and error-log paths on Windows. Optional parameters can start special modes or enable documented global trace flags through -T<number>. A typo in a required file path can prevent the Database Engine from starting. This is a much higher-risk change surface than a session SET statement.

Windows uses SQL Server Configuration Manager for supported service/startup configuration. Linux uses mssql-conf and Linux service configuration rather than SQL Server Configuration Manager. Never edit service registry/settings blindly across platforms.

5. Trace flags have query, session, and global scope

Trace flags alter specific SQL Server behaviors for diagnostics or compatibility. Some can be enabled only globally; some support session scope; selected optimizer trace flags can be invoked at query scope through QUERYTRACEON. A global flag enabled with DBCC TRACEON(...,-1) is not automatically persistent across a restart. A supported startup -T configuration is the persistent mechanism for a global startup flag where current documentation recommends it.

DBCC TRACESTATUS (-1) WITH NO_INFOMSGS;GO

SQL Server 2025 changes engine behavior in areas that older trace flags historically influenced. Always re-check whether a flag is relevant, superseded by a database-scoped option/compatibility level, or harmful under the current engine. Avoid treating a 2016-era trace-flag list as an evergreen tuning baseline.

6. Database-scoped configuration separates workload behavior from the instance

Modern SQL Server exposes many query-processing controls per database. This reduces the need to apply instance-wide changes when only one database needs a compatibility behavior or diagnostic override. The exact set of rows changes by version and feature state, so inventory the server you actually operate.

USE ServiceHubLab;GOSELECT    name,    value,    value_for_secondary,    is_value_defaultFROM sys.database_scoped_configurationsORDER BY name;GOSELECT    name,    compatibility_level,    is_query_store_on,    snapshot_isolation_state_desc,    is_read_committed_snapshot_onFROM sys.databasesWHERE name = N'ServiceHubLab';GO

A database-scoped setting can interact with compatibility level and engine version. SQL Server 2025 features such as Optional Parameter Plan Optimization, for example, have compatibility-level and scoped-configuration prerequisites. The existence of a setting does not prove the optimizer feature executed for a particular query.

7. Deliberately wrong approach: a universal “best-practice” config script

The inherited script sets max server memory, MAXDOP, cost threshold for parallelism, and multiple trace flags to hard-coded values. On a developer laptop the values seem harmless; on a shared host they can starve the OS or neighboring instances, alter plan choices, or introduce behavior that no one remembers during an incident.

8. Hands-on lab: build a configuration provenance report

SELECT    SYSDATETIMEOFFSET() AS observed_at,    SERVERPROPERTY('ServerName') AS server_name,    SERVERPROPERTY('Edition') AS edition,    SERVERPROPERTY('ProductVersion') AS product_version,    SERVERPROPERTY('ProductUpdateLevel') AS product_update_level;GOSELECT name, value, value_in_use, is_dynamic, is_advancedFROM sys.configurationsWHERE name IN(    N'max server memory (MB)',    N'min server memory (MB)',    N'max degree of parallelism',    N'cost threshold for parallelism',    N'optimize for ad hoc workloads',    N'show advanced options')ORDER BY name;GODBCC TRACESTATUS (-1) WITH NO_INFOMSGS;GOUSE ServiceHubLab;GOSELECT name, value, value_for_secondary, is_value_defaultFROM sys.database_scoped_configurationsORDER BY name;GO

Do not “correct” values to match another machine. Instead, write a one-page change proposal for exactly one setting you think might need attention. Include the evidence you would collect before and after changing it and the command needed to restore the original value. The lab is successful even if your conclusion is “no change justified.”

Verification checklist

• Your report distinguishes value from value_in_use.
• You identify whether each instance option is dynamic and/or advanced.
• You list active trace flags without enabling any.
• You record database-scoped configuration separately from instance configuration.
• Your proposed change includes rollback and restart implications.
• You do not copy an undocumented trace flag or universal tuning value into the lab.

9. Production judgment and next bridge

Configuration governance is a provenance problem. The goal is not to maximize the number of non-default settings; it is to know why every intentional difference exists and how to verify it after restart, failover, migration, patching, or rebuild. Prefer documented modern controls and database-scoped settings when they fit the problem. Keep emergency/support flags time-bounded and reviewed.

The next lesson applies this scope discipline to database properties that developers often confuse: collation, compatibility level, recovery model, and database options. Those properties can change query semantics, recovery guarantees, and optimizer behavior even though the executable build remains unchanged.

Authoritative references

• Server configuration options — sp_configure-managed instance settings
• View or change server properties — configured/effective values, RECONFIGURE, permissions, and restart behavior
• SQL Server startup parameters — required/optional startup parameters and undocumented-parameter warning
• Trace flags — query/session/global scope and persistence rules
• ALTER DATABASE SCOPED CONFIGURATION — database-level query-processing configuration