Configuration Drift Between SQL Server Instances: Comparing sys.configurations to a Saved Baseline

2026-11-01 · by Hannah Vernon · in configuration

Two instances are supposed to be built the same way. One of them runs a query with a parallel plan and the other doesn’t. Someone asks whether the servers are configured the same, and the honest answer is “they were when we built them.” Since then, someone may have changed max degree of parallelism during an incident, turned on xp_cmdshell for a one-off job, or run sp_configure and never followed it with RECONFIGURE.

A DBA holds a template card against a server panel and finds two dials and a switch set differently from the baseline.

That difference between what you built and what’s running now is configuration drift. The server-wide part of it is easy to measure, because every sp_configure option is a row in one catalog view. This post saves those rows to a baseline table and compares later captures, or other instances, against it.

Where the Settings Live

sys.configurations returns one row for each server-wide configuration option. Each row has the configured value (value), the running value (value_in_use), the minimum and maximum, and two flags: is_dynamic and is_advanced.^[1] sp_configure with no parameters shows a subset of the same data, without is_dynamic or is_advanced. Its config_value is sys.configurations.value, and its run_value is value_in_use.^[2]

I prefer the catalog view for a baseline. You can filter it, join it and insert it into a table, which is harder to do with a procedure’s result set. sp_configure also hides the advanced options until show advanced options is set to 1.^[2] Both of my instances already had that option set to 1, and sp_configure returned the same number of rows as the catalog view: 86 on SQL Server 2019 and 107 on SQL Server 2025. I didn’t change any settings on these instances, so I didn’t turn it off to check.

The view covers server-wide options only. The sp_configure documentation says database-level settings are changed with ALTER DATABASE, and that some server options are only available through ALTER SERVER CONFIGURATION.^[2] Trace flags have no rows in the view either.

The Test

I used two instances on one workstation: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer). In the output below I’ve called them SQL2019LAB and SQL2025LAB. I created a scratch database on the 2025 instance to hold the baseline, and I only read from sys.configurations on each instance.

The baseline needs two tables in a utility database (mine is sat_config_drift): one row per capture, and one row per option per capture.

USE [sat_config_drift];
GO

CREATE TABLE [dbo].[config_capture]
(
      [capture_id]      int            IDENTITY(1, 1) NOT NULL
    , [capture_label]   nvarchar(128)  NOT NULL
    , [server_name]     nvarchar(128)  NOT NULL
    , [product_version] nvarchar(128)  NOT NULL
    , [captured_at]     datetime2(0)   NOT NULL
        CONSTRAINT [df_config_capture_captured_at] DEFAULT (SYSDATETIME())
    , CONSTRAINT [pk_config_capture] PRIMARY KEY CLUSTERED ([capture_id])
);

CREATE TABLE [dbo].[config_value]
(
      [capture_id]       int           NOT NULL
    , [configuration_id] int           NOT NULL
    , [name]             nvarchar(35)  NOT NULL
    , [value]            sql_variant   NULL
    , [value_in_use]     sql_variant   NULL
    , [is_dynamic]       bit           NOT NULL
    , [is_advanced]      bit           NOT NULL
    , CONSTRAINT [pk_config_value] PRIMARY KEY CLUSTERED ([capture_id], [configuration_id])
    , CONSTRAINT [fk_config_value_capture] FOREIGN KEY ([capture_id])
        REFERENCES [dbo].[config_capture] ([capture_id])
);

USE [sat_config_drift];

CREATE TABLE [dbo].[config_capture]

(

[capture_id] int IDENTITY(1, 1) NOT NULL

, [capture_label] nvarchar(128) NOT NULL

, [server_name] nvarchar(128) NOT NULL

, [product_version] nvarchar(128) NOT NULL

, [captured_at] datetime2(0) NOT NULL

CONSTRAINT [df_config_capture_captured_at] DEFAULT (SYSDATETIME())

, CONSTRAINT [pk_config_capture] PRIMARY KEY CLUSTERED ([capture_id])

);

CREATE TABLE [dbo].[config_value]

(

[capture_id] int NOT NULL

, [configuration_id] int NOT NULL

, [name] nvarchar(35) NOT NULL

, [value] sql_variant NULL

, [value_in_use] sql_variant NULL

, [is_dynamic] bit NOT NULL

, [is_advanced] bit NOT NULL

, CONSTRAINT [pk_config_value] PRIMARY KEY CLUSTERED ([capture_id], [configuration_id])

, CONSTRAINT [fk_config_value_capture] FOREIGN KEY ([capture_id])

REFERENCES [dbo].[config_capture] ([capture_id])

);

I kept value and value_in_use as sql_variant, which is the type the catalog view returns.^[1] Every row on both of my instances had a base type of int, so comparing them with <> behaved as expected.

To load a capture from any instance into the central table, I used a short PowerShell script with SqlBulkCopy. It runs in Windows PowerShell 5.1 with no extra modules. The instance names here are examples. I trust the server certificate because these are lab instances. Drop TrustServerCertificate=True where you have proper certificates.

param
(
    [Parameter(Mandatory)] [string] $SourceInstance,
    [Parameter(Mandatory)] [string] $CaptureLabel,
    [string] $RepositoryInstance = 'SQLADMIN01',
    [string] $RepositoryDatabase = 'sat_config_drift'
)

$ErrorActionPreference = 'Stop'
$csOptions = 'Integrated Security=SSPI;Encrypt=True;TrustServerCertificate=True'

$repo = New-Object System.Data.SqlClient.SqlConnection "Server=$RepositoryInstance;Database=$RepositoryDatabase;$csOptions"
$src  = New-Object System.Data.SqlClient.SqlConnection "Server=$SourceInstance;Database=master;$csOptions"
$repo.Open()
$src.Open()
try
{
    $cmd = $src.CreateCommand()
    $cmd.CommandText = 'SELECT @@SERVERNAME, CONVERT(nvarchar(128), SERVERPROPERTY(N''ProductVersion''));'
    $rdr = $cmd.ExecuteReader()
    [void]$rdr.Read()
    $serverName = $rdr.GetString(0)
    $productVersion = $rdr.GetString(1)
    $rdr.Close()

    $ins = $repo.CreateCommand()
    $ins.CommandText = 'INSERT INTO [dbo].[config_capture] ([capture_label], [server_name], [product_version]) OUTPUT [inserted].[capture_id] VALUES (@label, @server, @version);'
    [void]$ins.Parameters.AddWithValue('@label', $CaptureLabel)
    [void]$ins.Parameters.AddWithValue('@server', $serverName)
    [void]$ins.Parameters.AddWithValue('@version', $productVersion)
    $captureId = [int]$ins.ExecuteScalar()

    $cmd.CommandText = 'SELECT @capture_id, [configuration_id], [name], [value], [value_in_use], [is_dynamic], [is_advanced] FROM [sys].[configurations];'
    [void]$cmd.Parameters.AddWithValue('@capture_id', $captureId)
    $rdr = $cmd.ExecuteReader()
    $bulk = New-Object System.Data.SqlClient.SqlBulkCopy $repo
    $bulk.DestinationTableName = '[dbo].[config_value]'
    $bulk.WriteToServer($rdr)
    $rdr.Close()

    "capture_id $captureId : $CaptureLabel ($productVersion)"
}
finally
{
    $src.Close()
    $repo.Close()
}

param

(

[Parameter(Mandatory)] [string] $SourceInstance,

[Parameter(Mandatory)] [string] $CaptureLabel,

[string] $RepositoryInstance = 'SQLADMIN01',

[string] $RepositoryDatabase = 'sat_config_drift'

)

$ErrorActionPreference = 'Stop'

$csOptions = 'Integrated Security=SSPI;Encrypt=True;TrustServerCertificate=True'

$repo = New-Object System.Data.SqlClient.SqlConnection "Server=$RepositoryInstance;Database=$RepositoryDatabase;$csOptions"

$src = New-Object System.Data.SqlClient.SqlConnection "Server=$SourceInstance;Database=master;$csOptions"

$repo.Open()

$src.Open()

try

{

$cmd = $src.CreateCommand()

$cmd.CommandText = 'SELECT @@SERVERNAME, CONVERT(nvarchar(128), SERVERPROPERTY(N''ProductVersion''));'

$rdr = $cmd.ExecuteReader()

[void]$rdr.Read()

$serverName = $rdr.GetString(0)

$productVersion = $rdr.GetString(1)

$rdr.Close()

$ins = $repo.CreateCommand()

$ins.CommandText = 'INSERT INTO [dbo].[config_capture] ([capture_label], [server_name], [product_version]) OUTPUT [inserted].[capture_id] VALUES (@label, @server, @version);'

[void]$ins.Parameters.AddWithValue('@label', $CaptureLabel)

[void]$ins.Parameters.AddWithValue('@server', $serverName)

[void]$ins.Parameters.AddWithValue('@version', $productVersion)

$captureId = [int]$ins.ExecuteScalar()

$cmd.CommandText = 'SELECT @capture_id, [configuration_id], [name], [value], [value_in_use], [is_dynamic], [is_advanced] FROM [sys].[configurations];'

[void]$cmd.Parameters.AddWithValue('@capture_id', $captureId)

$rdr = $cmd.ExecuteReader()

$bulk = New-Object System.Data.SqlClient.SqlBulkCopy $repo

$bulk.DestinationTableName = '[dbo].[config_value]'

$bulk.WriteToServer($rdr)

$rdr.Close()

"capture_id $captureId : $CaptureLabel ($productVersion)"

}

finally

{

$src.Close()

$repo.Close()

}

I saved three captures: the 2025 instance as the baseline (capture 1), the 2019 instance (capture 2), and the 2025 instance again about 30 seconds later (capture 3).

The Comparison Query

The comparison takes two capture IDs and joins them on configuration_id with a full outer join. A full outer join keeps the options that exist in only one capture. The query also checks the current capture for a configured value that isn’t running yet.

USE [sat_config_drift];

DECLARE @baseline_capture_id int = 1;
DECLARE @current_capture_id  int = 2;

SELECT
      COALESCE([c].[name], [b].[name]) AS [option_name]
    , CASE
          WHEN [c].[configuration_id] IS NULL THEN N'missing from current'
          WHEN [b].[configuration_id] IS NULL THEN N'not in baseline'
          WHEN [c].[value] <> [b].[value] THEN N'value differs'
          WHEN [c].[value_in_use] <> [b].[value_in_use] THEN N'value_in_use differs'
          ELSE N'pending only'
      END AS [drift_type]
    , [b].[value] AS [baseline_value]
    , [c].[value] AS [current_value]
    , [c].[value_in_use] AS [current_value_in_use]
    , CASE
          WHEN [c].[value] <> [c].[value_in_use] AND [c].[is_dynamic] = 1 THEN N'RECONFIGURE pending'
          WHEN [c].[value] <> [c].[value_in_use] AND [c].[is_dynamic] = 0 THEN N'restart pending'
          ELSE N''
      END AS [pending]
    , COALESCE([c].[is_dynamic], [b].[is_dynamic]) AS [is_dynamic]
    , COALESCE([c].[is_advanced], [b].[is_advanced]) AS [is_advanced]
FROM
    (
        SELECT
              [v].[configuration_id]
            , [v].[name]
            , [v].[value]
            , [v].[value_in_use]
            , [v].[is_dynamic]
            , [v].[is_advanced]
        FROM
            [dbo].[config_value] AS [v]
        WHERE
            [v].[capture_id] = @baseline_capture_id
    ) AS [b]
FULL OUTER JOIN
    (
        SELECT
              [v].[configuration_id]
            , [v].[name]
            , [v].[value]
            , [v].[value_in_use]
            , [v].[is_dynamic]
            , [v].[is_advanced]
        FROM
            [dbo].[config_value] AS [v]
        WHERE
            [v].[capture_id] = @current_capture_id
    ) AS [c]
        ON [c].[configuration_id] = [b].[configuration_id]
WHERE
    [b].[configuration_id] IS NULL
    OR [c].[configuration_id] IS NULL
    OR [c].[value] <> [b].[value]
    OR [c].[value_in_use] <> [b].[value_in_use]
    OR [c].[value] <> [c].[value_in_use]
ORDER BY
      [drift_type]
    , [option_name];

USE [sat_config_drift];

DECLARE @baseline_capture_id int = 1;

DECLARE @current_capture_id int = 2;

SELECT

COALESCE([c].[name], [b].[name]) AS [option_name]

, CASE

WHEN [c].[configuration_id] IS NULL THEN N'missing from current'

WHEN [b].[configuration_id] IS NULL THEN N'not in baseline'

WHEN [c].[value] <> [b].[value] THEN N'value differs'

WHEN [c].[value_in_use] <> [b].[value_in_use] THEN N'value_in_use differs'

ELSE N'pending only'

END AS [drift_type]

, [b].[value] AS [baseline_value]

, [c].[value] AS [current_value]

, [c].[value_in_use] AS [current_value_in_use]

, CASE

WHEN [c].[value] <> [c].[value_in_use] AND [c].[is_dynamic] = 1 THEN N'RECONFIGURE pending'

WHEN [c].[value] <> [c].[value_in_use] AND [c].[is_dynamic] = 0 THEN N'restart pending'

ELSE N''

END AS [pending]

, COALESCE([c].[is_dynamic], [b].[is_dynamic]) AS [is_dynamic]

, COALESCE([c].[is_advanced], [b].[is_advanced]) AS [is_advanced]

FROM

(

SELECT

[v].[configuration_id]

, [v].[name]

, [v].[value]

, [v].[value_in_use]

, [v].[is_dynamic]

, [v].[is_advanced]

FROM

[dbo].[config_value] AS [v]

WHERE

[v].[capture_id] = @baseline_capture_id

) AS [b]

FULL OUTER JOIN

(

SELECT

[v].[configuration_id]

, [v].[name]

, [v].[value]

, [v].[value_in_use]

, [v].[is_dynamic]

, [v].[is_advanced]

FROM

[dbo].[config_value] AS [v]

WHERE

[v].[capture_id] = @current_capture_id

) AS [c]

ON [c].[configuration_id] = [b].[configuration_id]

WHERE

[b].[configuration_id] IS NULL

OR [c].[configuration_id] IS NULL

OR [c].[value] <> [b].[value]

OR [c].[value_in_use] <> [b].[value_in_use]

OR [c].[value] <> [c].[value_in_use]

ORDER BY

[drift_type]

, [option_name];

I joined on configuration_id rather than name. The IDs matched for all 86 options the two versions share, so either key would have worked here.

Results

Comparing the 2025 instance with itself (capture 1 against capture 3) returned zero rows. That only shows nothing changed in those 30 seconds, but it confirms the query doesn’t report noise.

Comparing the 2019 instance against the 2025 baseline returned 29 rows. I’ve trimmed the output to the name, drift type, both values and is_dynamic.

option_name                          drift_type            baseline  current  is_dynamic
ADR cleaner lock timeout (s)         missing from current           5     NULL    1
ADR Cleaner Thread Count             missing from current           1     NULL    1
allow server scoped db credentials   missing from current           0     NULL    1
availability group commit time (ms)  missing from current           0     NULL    1
backup compression algorithm         missing from current           0     NULL    1
Data processed daily limit in TB     missing from current  2147483647     NULL    1
Data processed monthly limit in TB   missing from current  2147483647     NULL    1
Data processed weekly limit in TB    missing from current  2147483647     NULL    1
external AI runtimes enabled         missing from current           0     NULL    1
external rest endpoint enabled       missing from current           0     NULL    1
external xtp dll gen util enabled    missing from current           0     NULL    1
hardware offload config              missing from current           0     NULL    0
hardware offload enabled             missing from current           0     NULL    0
hardware offload mode                missing from current           0     NULL    0
max lock manager cache memory (%)    missing from current          20     NULL    0
max server tiered memory (MB)        missing from current  2147483647     NULL    0
max UCS send boxcars                 missing from current         256     NULL    0
openrowset auto_create_statistics    missing from current           1     NULL    1
SLOG memory quota (%)                missing from current          75     NULL    1
suppress recovery model errors       missing from current           0     NULL    1
tiered memory enabled                missing from current           0     NULL    0
Ad Hoc Distributed Queries           value differs                  0        1    1
ADR cleaner retry timeout (min)      value differs                 15        0    1
ADR Preallocation Factor             value differs                  4        0    1
filestream access level              value differs                  2        1    1
max degree of parallelism            value differs                  4        8    1
version high part of SQL Server      value differs            1114112        0    1
version low part of SQL Server       value differs          328663044        0    1
xp_cmdshell                          value differs                  0        1    1

option_name drift_type baseline current is_dynamic

ADR cleaner lock timeout (s) missing from current 5 NULL 1

ADR Cleaner Thread Count missing from current 1 NULL 1

allow server scoped db credentials missing from current 0 NULL 1

availability group commit time (ms) missing from current 0 NULL 1

backup compression algorithm missing from current 0 NULL 1

Data processed daily limit in TB missing from current 2147483647 NULL 1

Data processed monthly limit in TB missing from current 2147483647 NULL 1

Data processed weekly limit in TB missing from current 2147483647 NULL 1

external AI runtimes enabled missing from current 0 NULL 1

external rest endpoint enabled missing from current 0 NULL 1

external xtp dll gen util enabled missing from current 0 NULL 1

hardware offload config missing from current 0 NULL 0

hardware offload enabled missing from current 0 NULL 0

hardware offload mode missing from current 0 NULL 0

max lock manager cache memory (%) missing from current 20 NULL 0

max server tiered memory (MB) missing from current 2147483647 NULL 0

max UCS send boxcars missing from current 256 NULL 0

openrowset auto_create_statistics missing from current 1 NULL 1

SLOG memory quota (%) missing from current 75 NULL 1

suppress recovery model errors missing from current 0 NULL 1

tiered memory enabled missing from current 0 NULL 0

Ad Hoc Distributed Queries value differs 0 1 1

ADR cleaner retry timeout (min) value differs 15 0 1

ADR Preallocation Factor value differs 4 0 1

filestream access level value differs 2 1 1

max degree of parallelism value differs 4 8 1

version high part of SQL Server value differs 1114112 0 1

version low part of SQL Server value differs 328663044 0 1

xp_cmdshell value differs 0 1 1

The 21 “missing from current” rows are options that exist on SQL Server 2025 and not on 2019. They come from the version difference, so I don’t count them as drift. I’ve written about those options before. The 2022 additions are in The 11 Configuration Options Added in SQL Server 2022, and Their Documentation. The 2025 additions are in SQL Server 2025 Added 10 Configuration Options, and One Appeared After RTM. For drift checks, I’d keep one baseline per major version and compare each instance only with the baseline for its version.

The eight “value differs” rows fall into three groups.

Real configuration differences. max degree of parallelism, xp_cmdshell, Ad Hoc Distributed Queries and filestream access level are set differently on my two lab instances. On production servers that are supposed to match, these are the rows I’d want a report to show. xp_cmdshell is also on the list in Surface Area Configuration Audit.

Values I can’t explain. ADR cleaner retry timeout (min) is 0 on my 2019 instance and 15 on my 2025 instance. ADR Preallocation Factor is 0 and 4. The server configuration options page lists defaults of 120 and 4 for these options.^[3] Neither instance matches the documented 120. I can’t tell from the view whether someone set these values or whether each build reports its own default. My 2019 instance reports both against the 2025 baseline. I don’t know whether every 2019 build does.

Values that describe the build. version high part of SQL Server and version low part of SQL Server are 0 on my 2019 instance. On my 2025 instance they’re 1114112 and 328663044. In hexadecimal those are 0x00110000 and 0x13970004, which read as 17.0 and 5015.4, the build number of that instance. The documentation lists a default of 0 for both and doesn’t explain the values.^[3] If those two options change whenever the build changes, every cumulative update would show up as drift. I didn’t patch an instance to check that, so I’d exclude them by name from the report and track the build with SERVERPROPERTY(N'ProductVersion'), which the capture table already stores.

Pending Changes and is_dynamic

value and value_in_use can differ on one instance. After sp_configure sets a new value, the running value doesn’t change until someone runs RECONFIGURE or RECONFIGURE WITH OVERRIDE.^[2] For an option where is_dynamic is 0, the new value doesn’t take effect until the Database Engine restarts, even after RECONFIGURE.^[1] That’s why the query labels a pending row “RECONFIGURE pending” or “restart pending” based on is_dynamic.

None of my captures had a pending change. Every row on both instances had value equal to value_in_use, so the pending column was empty. I didn’t create a pending change to test the labels, because that would mean changing a setting on the instance.

The labels have two gaps. For a restart-only option, the view can’t tell you whether RECONFIGURE already ran, so I run it before the restart anyway.^[1] Some options also differ by design. The sys.configurations page says a configured max server memory (MB) of 0 shows a value_in_use of 2147483647.^[1] The server configuration options page gives the default as 2147483647, not 0, so I’m not sure when you’d see a 0.^[3] Both of my instances had it set to 2048. If you do see a 0, that row lands in the pending list.

The number of restart-only options also changed between versions. My 2019 instance had 17 options with is_dynamic = 0, and my 2025 instance had 23. Seven of the 21 options that my 2025 instance has and my 2019 instance doesn’t are restart-only: the three hardware offload options, max lock manager cache memory (%), max server tiered memory (MB), max UCS send boxcars and tiered memory enabled. The flag also changed for one existing option. hadoop connectivity has is_dynamic = 0 on my 2019 instance and 1 on my 2025 instance. So I store is_dynamic with every capture, rather than keeping one list of restart-only options for all versions.

Limits of This Test

I tested one build of each version, on one workstation.
Both instances had show advanced options set to 1, so I didn’t see sp_configure hide anything.
I didn’t create a pending change, so the pending labels are untested here.
The baseline covers server-wide options only. It doesn’t cover database scoped configurations, trace flags, startup parameters or ALTER SERVER CONFIGURATION settings.

Try It Yourself

Create the two tables in a utility database, run the capture script against each instance, and save the first capture for each version as the baseline. Schedule the capture, run the comparison against the baseline for each instance’s version, and remove the version rows from the report. The capture only reads sys.configurations, but on SQL Server 2022 and later the login needs VIEW SERVER PERFORMANCE STATE.^[1]

Related: Reviewing Max Server Memory and Lock Pages in Memory (available 2026-11-11).

If you track configuration drift a different way, or you know why the version rows are 0 on one build and populated on another, I’d like to hear about it. Find me on Bluesky or LinkedIn.

References

sys.configurations (Transact-SQL) - Microsoft Learn. The columns of the view, how is_dynamic relates to RECONFIGURE and restarts, the max server memory exception, and the VIEW SERVER PERFORMANCE STATE permission. ↩
sp_configure (Transact-SQL) - Microsoft Learn. How config_value and run_value map to sys.configurations, the RECONFIGURE requirement, the show advanced options setting, and options set only through ALTER SERVER CONFIGURATION. ↩
Server configuration options - Microsoft Learn. The table of options with their documented minimum, maximum and default values. ↩

Tags: Baseline, configuration, configuration-drift, sp-configure, SQL Server, SQL Server 2025, sql-server-2019, sys-configurations

Configuration Drift Between SQL Server Instances: Comparing sys.configurations to a Saved Baseline

Where the Settings Live

The Test

The Comparison Query

Results

Pending Changes and is_dynamic

Limits of This Test

Try It Yourself

References

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta

Configuration Drift Between SQL Server Instances: Comparing sys.configurations to a Saved Baseline

Where the Settings Live

The Test

The Comparison Query

Results

Pending Changes and is_dynamic

Limits of This Test

Try It Yourself

References

Share this:

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta