The question usually starts with, “Why does this query behave differently over there?” Comparing server settings across instances gives you a quick way to rule configuration in or out.
Start With a Specific Question
Comparing all server settings at once can drown a simple problem in irrelevant differences. Name the symptom first. For plan behavior, compare compatibility level, cardinality estimator related settings, MAXDOP, and cost threshold. For memory pressure, compare max server memory against available RAM and other services. For backup differences, compare compression defaults and schedules. The question defines the shortlist.
I usually begin with settings that can explain the reported behavior, then widen the search. That order keeps the investigation fast. A different server name is obvious and harmless; a changed value_in_use is not. Which difference would actually change the workload you are investigating? Keep that test in mind as you read the output.
Read Configured and Running Values
sys.configurations exposes both value and value_in_use. The first is the stored setting. The second is the setting the engine is using now. If they differ, a change can be pending or incomplete. Collect both on every server. Label each row with the instance so copied results remain understandable after someone pastes them into a report.
The query deliberately includes all configuration rows. Export or save the result for comparison, then filter the differences by relevance. Do not assume every visible setting is safe to change. Some options are advanced, version dependent, or controlled by a feature you do not use.
SELECT @@SERVERNAME AS instance_name,
configuration_id,
name,
value,
value_in_use,
is_dynamic,
is_advanced
FROM sys.configurations
ORDER BY name;Comparing Server Settings Through a Central Connection
If Central Management Servers or registered servers are available, run the same read-only query against a selected group in SSMS. The combined result includes server context when configured appropriately. Check the target list before execution. A query intended for two test instances should not quietly run against every production server.
I keep this query read-only because a multi-server result is easy to copy and review. Changes happen later, one approved instance at a time. If your tooling does not combine results, save each server’s output with a timestamp and compare by setting name. The key is a consistent query, not a particular interface. A spreadsheet can help with comparison, but it should not invent the baseline.
Watch Version and Edition Context When Comparing Server Settings
Two instances on different builds can expose different options and defaults. Record product version and edition beside the settings. Missing rows are not automatically errors. They can reflect version differences. Compare documented behavior for the actual builds rather than assuming an old recommendation still applies to the newer server.
This second query records context for the configuration snapshot. ServerProperty returns the engine’s reported values. Save it in the same file or table as the settings so future readers know what was compared. A setting difference without build context is an unfinished investigation.
SELECT @@SERVERNAME AS instance_name,
SERVERPROPERTY(N'ProductVersion') AS product_version,
SERVERPROPERTY(N'ProductLevel') AS product_level,
SERVERPROPERTY(N'Edition') AS edition,
SERVERPROPERTY(N'EngineEdition') AS engine_edition;
Distinguish Expected From Harmful
Different memory caps are expected when physical memory differs. Different MAXDOP values can be deliberate when workloads and processor topology differ. An unexpected remote access setting or an unreviewed backup compression change deserves a closer look. Document why each accepted difference exists. Otherwise next quarter’s reviewer will have to rediscover the same reasoning.
I have seen teams spend more time making reports identical than understanding what each server does. That is tidy work, but not useful work. Classify differences as expected, approved exception, or investigate. Give each investigation an owner. A comparison report should lead to a decision, not sit in a folder with the name final-final.
Check Database Settings Too
Server options are only part of the story. A restored database can carry a different compatibility level or recovery model. Query sys.databases on both instances if the symptom follows one database. Check scoped configuration as well when query behavior differs. Some settings live with the database and will never appear in sys.configurations.
The database query below is safe to run on each instance. Compare the same database names and note any that are intentionally absent. A test environment can use a different recovery model by design. Record that choice rather than hiding it.
SELECT @@SERVERNAME AS instance_name,
name,
compatibility_level,
recovery_model_desc,
page_verify_option_desc
FROM sys.databases
WHERE database_id > 4
ORDER BY name;Apply Changes Deliberately
Once a difference is linked to the problem, decide whether to change the instance or update the standard. Script the exact option and previous value. Check whether the change is dynamic or needs a restart. Test on the appropriate environment and monitor the workload afterward. Do not run a mass update merely because several cells differ from one template.
If you cannot establish a connection between the setting and symptom, leave the production value alone while you investigate. Configuration is not a collection of cosmetic preferences. One toggle can change plans or availability. The safest way of comparing server settings ends with a short list of justified actions and a longer list of differences that need no action.
Keep a Reusable Snapshot for Comparing Server Settings
Capture the same fields at a regular interval and after planned changes. Include the date, instance, build, setting name, configured value, and running value. Keep a copy of the approved baseline. A new snapshot can then show exactly what changed since the last review. That is much more useful than remembering that two servers looked similar last summer.
A report should surface new or unexplained differences first. Let readers expand the expected ones when needed. This small design choice keeps the comparison readable and makes it more likely somebody will notice the important row. A useful report is the one people actually open.
Keep the Comparison Readable
Show each changed setting with its configured value, effective value, and approved reason on both instances. Put differences relevant to the reported symptom first. I move known environment exceptions to a separate section with an owner and review date so new changes remain visible. Include capture times and builds. Without them, another DBA cannot tell whether the comparison still applies. Ask whether the workloads and data distributions are comparable before blaming a setting. Identical configuration does not guarantee identical execution plans. The report should end with a small number of justified actions, not a long list of cells to make the same color.
Related reading on this blog: SQL SERVER 2019: New Values in Sys.Configurations and Database Scoped Configurations.
A different setting is not automatically a fault, it is a question that needs workload context.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
