Configuration Values
Overview
sp_configure is where somebody’s opinion from years ago is still in force. Configuration Values is the page that finds out whose opinion, and whether it is still a good one.
SQL Server 2022 exposes 97 settings. On most instances, five or six of them have ever been touched. The whole job of this page is telling you which five or six, and it does that by carrying something sys.configurations does not: the value each setting ships as.
The page therefore opens on Changed, not on all 97 rows.
Where to find it
An instance level report. Right-click the server → Instance Level Reports → Configuration Values.
The page title reads Configuration Values for <server name>.
Requirements
- No minimum version.
sys.configurationsexists on every build this product supports. VIEW SERVER STATEis not required. Readingsys.configurationsneeds no special permission, which is why this page works on instances where most of the others do not.
The build is read from SERVERPROPERTY('ProductVersion') so the page can name a setting that arrived with this version. If that read fails, the page carries on and simply says nothing about which version brought what.
Nothing on this page changes anything. It reads a catalog view. Every script it writes is commented out, and it never runs one.
The four questions
Each is a button, and each carries its own count.
| Button | What it shows |
|---|---|
| Changed | Settings whose configured value is not what this build ships. The opening view. |
| Pending | Settings that are configured one way and running another. |
| Flagged | Settings this product has an opinion about, worst first. |
| Deprecated | Settings Microsoft has retired, or that stopped doing anything years ago. |
| Advanced | The settings sp_configure hides until show advanced options is on. |
| All | Everything, still sorted worst first. |
Click Setting in the header to sort the whole grid alphabetically instead.
Reading the grid
| Column | What it is |
|---|---|
| Setting | The setting, exactly as sp_configure names it. |
| Value | What the setting is configured to. This is what Changed is measured against. |
| In use | What the instance is actually running with. This is what the verdict is measured against. |
| Default | What this build of SQL Server ships the setting as. |
| Verdict | Act, Review, or See other page. Blank where the product has no opinion. |
| Pending | What the row is waiting for, in words. Blank when there is nothing waiting. |
| What it does | One line on the setting. Falls back to the server’s own description. |
| What to do | The recommendation, plus any deprecation note. |
Double-click any row for everything the page knows about it, including the legal range and the sp_configure script, commented out.
The three verdicts
Only settings with an answer that holds on nearly every instance are judged. A wrong color is worse than no color, so most of the 97 rows carry no verdict at all.
Act is wrong on nearly every instance and the fix is known.
| Setting | When |
|---|---|
| max server memory (MB) | Left at 2147483647. SQL Server will take the whole machine. |
| fill factor (%) | Between 1 and 84 instance wide. 0 and 100 both mean fill the page and neither is flagged. |
| priority boost | Set to 1. Deprecated, and it starves the operating system on a busy box. |
| lightweight pooling | Set to 1. Fiber mode breaks CLR, some linked servers and parts of Database Mail. |
| set working set size | Set to 1. A no-op since SQL Server 2005. |
| allow updates | Set to 1. Does nothing except make the next RECONFIGURE fail. |
Review is defensible, but somebody should be able to say why. xp_cmdshell, Ole Automation Procedures, Ad Hoc Distributed Queries, clr enabled, cross db ownership chaining, contained database authentication, scan for startup procs, c2 audit mode and common criteria compliance enabled on the security side. backup compression default, backup checksum default, optimize for ad hoc workloads, default trace enabled and remote admin connections where the default is one most instances outgrow. max worker threads, user connections, query governor cost limit, recovery interval (min), index create memory, min memory per query, network packet size, user options, locks and the affinity masks where a value has been pinned by hand.
See other page is the one verdict that is not a verdict.
Why MAXDOP has no number here
max degree of parallelism and cost threshold for parallelism are marked See other page and given no opinion of their own. That is deliberate.
The right MAXDOP depends on the core count, the NUMA layout and the parallel waits the instance is actually recording. Parallelism Calibration works all three out. A second opinion from a fixed band on this page would contradict that one on the same server, and two pages of one product disagreeing is worse than one page staying quiet.
The old version of this report did have a fixed band: green between 1 and 8, red outside it. That calls MAXDOP 8 correct on a four core box and calls a considered 12 wrong on a two socket machine.
Value against In use
When those two disagree, the change has not taken effect. The Pending column says which of the two reasons it is:
| Pending reads | What it means |
|---|---|
| Needs a RECONFIGURE | Somebody ran sp_configure and never ran RECONFIGURE. One statement fixes it. |
| Needs a restart | The setting is not dynamic. The change is queued for the next service start. |
| Not in force, see details | Neither of the above is the whole story. Double-click the row. |
Two rows are deliberately not reported as pending, because they differ on every instance:
- min server memory (MB) reads 0 configured against 16 in use. 16 is the engine’s own floor, not a forgotten
RECONFIGURE. - filestream access level carries
is_dynamic = 1and yet cannot take a change without the FILESTREAM switch in SQL Server Configuration Manager and a restart. It is reported as pending when it differs, with the caveat rather than a wrong instruction.
Without those two exclusions the page would report a pending change on every server in the estate, which is the fastest way to teach somebody that a color means nothing.
What has changed on this server
The Changed count is the reason to open the page on an unfamiliar instance.
It compares the configured value against the shipped default, not the running one, so a value the engine settled on by itself is not counted as somebody’s decision. Three cases are excluded:
- Agent XPs. SQL Server Agent turns it on when the service starts. Every instance with a running Agent would otherwise be reported as carrying a change nobody made.
- version high part of SQL Server and version low part of SQL Server. They report the build rather than hold a choice, so they have no default and their Default column reads a dash.
- Settings whose default moved between releases on an instance whose build could not be read. The two accelerated database recovery cleaner settings ship as 0 on SQL Server 2019 and as 15 and 4 on SQL Server 2022, so without knowing the build the page cannot say whether they were set.
Scripts
Nothing is executed. Copy review script puts the whole page’s argument on the clipboard as one commented script: what is pending, what is flagged, what somebody changed, and the query behind the page so the report can be checked rather than believed.
Right-click a row for the sp_configure statements for that one setting. Where the setting is advanced, the script includes the show advanced options step, because that is the one people forget. Where the setting is not dynamic, it says so, because a RECONFIGURE on those records the change and leaves the instance running on the old value.
How to read the report
- Read the Changed list. On a server you have not seen before, that is the whole of what somebody decided, and it is usually short enough to read in a few seconds.
- Check Pending. A pending change is a change somebody thought they had already made.
- Read Flagged. Act rows first.
- Check Deprecated on an instance that has been upgraded a few times. Settings survive upgrades long after they stop doing anything.
Common patterns
max server memory (MB) at 2147483647. Never configured. Fine on a dedicated box with nothing else on it, and a problem the moment anything shares the machine. Memory shows what the instance has actually taken.
MAXDOP at 0 and cost threshold at 5. The default pair, untouched. On any instance with more than a handful of cores this is where CXPACKET and CXCONSUMER waits come from. Parallelism Calibration has the numbers.
Value and In use differ on a dynamic setting. Somebody ran sp_configure and forgot the RECONFIGURE.
Value and In use differ on a non-dynamic setting. Worth knowing before an unplanned restart, after which the instance comes back configured differently from how it went down.
A deprecated setting still set. priority boost, lightweight pooling and the affinity masks all outlive the hardware that justified them.
Where the data comes from
sys.configurations, ordered by name.SERVERPROPERTY('ProductVersion')andSERVERPROPERTY('Edition').
The four sql_variant columns are converted to bigint on the server. Every one of them has an int base type, so nothing can overflow.
The defaults, the verdicts, the deprecation notes and the version each setting arrived in are held in the product, not read from the instance, because sys.configurations carries none of them.
Nothing is stored by this page.
Related reports
| Report | Why you would go there |
|---|---|
| Parallelism Calibration | What MAXDOP and the cost threshold should actually be on this instance. |
| Memory | What the instance has taken, against what max server memory allows it. |
| Trace Flags | The other half of instance configuration. |
| Security Posture | Where xp_cmdshell and Ole Automation Procedures belong in a hardening conversation. |
| Quick Scan Report | The broader configuration review, with these settings included. |
| Migration Planner | What a replacement server needs, including the settings worth carrying across. |
Frequently asked questions
Why does the page not open on all the settings? Because on an unfamiliar instance the question is what somebody changed, and 97 rows is not an answer to it. All is one click away and the count is on the button.
Why is there no chart? The filter does the work a chart would have done. Every other question this page answers is a list.
Why does MAXDOP have no verdict? Because the right answer depends on hardware this page does not read. Parallelism Calibration does read it. See the section above.
What is the difference between Value and In use? Value is what is configured. In use is what the engine is running with. They differ until a RECONFIGURE runs, or until a restart for the settings that are not dynamic.
Why does this show settings sp_configure does not? Because sp_configure hides advanced options until you enable them. This page reads sys.configurations directly, which has no such distinction. That is also why show advanced options itself is not judged: it makes no difference to what you see here.
Why is fill factor 0 not flagged when fill factor 90 is not either? 0 and 100 both mean fill the page, so neither costs anything. The red band is 1 to 84, the range where somebody has deliberately left space on every page in the instance. 85 to 99 is left alone as a defensible choice.
A setting is missing a Default and a verdict. It is not in this product’s catalog. Editions add settings, and so does every release. The page says nothing rather than guessing.
Can I change a setting from here? No. This page reads. Configuration changes belong in a change window with a RECONFIGURE you ran deliberately, which is why every script this page writes is commented out.