Configuration Values

Overview

sp_configure is where somebody’s opinion from years ago is still in force. The Configuration Values report puts all of it on one page: every setting the instance exposes, what it is set to, and what it is actually running with.

Two things make it more useful than running sp_configure yourself.

  • It always shows the advanced options. sp_configure hides them until you turn show advanced options on, and the settings people get wrong are almost all in the hidden half.
  • It shows configured and in use side by side. A value that was changed without a RECONFIGURE is invisible in most places and obvious here.
The Configuration Values report
Every sp_configure setting on one page, sorted by name, with the three checked settings coloured.

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.configurations exists on every build this product supports.
  • VIEW SERVER STATE is not required. Reading sys.configurations needs no special permission, which is why this page works on instances where most of the others do not.

Nothing on this page changes anything. It is a read of the catalog view, and there is no action to run a RECONFIGURE from here.

Reading the grid

Column What it is
Name The setting, exactly as sp_configure names it. Sorted alphabetically.
Value What the setting is configured to.
In Use What the instance is actually running with.
Min/Max The legal range for the setting, written as min / max.
Dynamic 1 when a change takes effect on RECONFIGURE, 0 when it needs a service restart.
Advanced 1 when sp_configure hides the setting behind show advanced options.

Value against In Use

When those two columns disagree, the change has not taken effect. Either somebody ran sp_configure and never ran RECONFIGURE, or the setting is not dynamic and the service has not been restarted since. The Dynamic column tells you which of the two it is.

That is the single most useful thing on this page, and it is why the two columns are next to each other rather than one being shown and the other being assumed.

The coloured rows

Three settings are judged, and only three. Everything else is reported without an opinion.

Setting Colour Rule
max degree of parallelism Green Configured or in use between 1 and 8.
max degree of parallelism Red Configured or in use below 1 or above 8.
fill factor (%) Red Configured or in use between 1 and 84.
priority boost Red Set to 1.

MAXDOP of 0 is coloured red. That is deliberate and it is a starting point rather than a verdict: 0 means every parallel plan may use every scheduler, which is the default and is the wrong answer on most modern hardware. It is not automatically wrong on a small instance. Parallelism Calibration is the page that works out what the number should actually be here, against the schedulers, the NUMA layout and the parallel waits this instance is actually recording.

A fill factor below 85 set instance wide is almost always a mistake. It costs that percentage of every page in every index in every database, forever, to solve a fragmentation problem that usually belonged to one index. A fill factor of 0 or 100 is not coloured, because both mean pages are filled.

priority boost should be 0. Microsoft deprecated it, it does not do what its name suggests, and on a busy instance it can starve the operating system enough to break cluster health checks. There is no case for leaving it on.

The Configuration Values grid
Value and In Use side by side. When they differ, the change has not taken effect yet.

How to read the report

  1. Scan for a coloured row. Three settings, and they are the three worth a look on any instance you have not seen before.
  2. Scan the Value and In Use columns for a disagreement. A pending change is a change somebody thought they had already made.
  3. Read max server memory (MB). It is not coloured, because the right answer depends on what else is on the box, but a value left at its default of 2147483647 means SQL Server will take everything. Memory is the page that shows what it has actually taken.
  4. Read cost threshold for parallelism. The default of 5 dates from hardware that no longer exists. It travels with MAXDOP, and Parallelism Calibration covers both.
  5. Read backup compression default. Off by default, and worth turning on almost everywhere. Backup Status shows what that is costing in backup duration and size.

Common patterns

MAXDOP red at 0, cost threshold for parallelism 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. Waits will confirm it and Parallelism Calibration will give you the numbers.

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.

Value and In Use differ on a dynamic setting. Somebody ran sp_configure and forgot the RECONFIGURE. One statement fixes it.

Value and In Use differ on a non-dynamic setting. The change is queued for the next restart. Worth knowing before an unplanned one happens and the instance comes back configured differently from how it went down.

show advanced options at 0. Normal, and irrelevant here. This page reads the catalog view directly and shows the advanced settings regardless.

Where the data comes from

  • sys.configurations, read with NOLOCK and OPTION (RECOMPILE), ordered by name.

The minimum and maximum columns are concatenated into the Min/Max column so the legal range sits beside the value rather than in two columns of numbers that only matter together.

Nothing is stored by this page.

Report Why you would go there
Parallelism Calibration What MAXDOP and the cost threshold should actually be on this instance.
Trace Flags The other half of instance configuration, and where several of these settings have trace flag equivalents.
Memory What the instance has actually taken, against what max server memory allows it.
Quick Scan Report The broader configuration review, with these settings included.
Migration Planner What a replacement server needs, including the settings worth carrying across.
Security Posture Where settings like xp_cmdshell and Ole Automation Procedures belong in a hardening conversation.

Frequently asked questions

Why are only three settings coloured? Because only three have an answer that is right on nearly every instance. Colouring max server memory would mean guessing what else is on the machine, and a wrong colour is worse than no colour.

Why is MAXDOP 0 red when 0 is the default? Because the default is the wrong answer on most modern hardware, not because 0 is invalid. Parallelism Calibration is where the real recommendation lives.

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.

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 where Dynamic is 0.

Can I change a setting from here? No. This page reads. Configuration changes belong in a change window with a RECONFIGURE you ran deliberately.

Why is fill factor 0 not coloured 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, which is the range where somebody has deliberately left space on every page in the instance. 85 to 99 is left alone as a defensible choice.