Audit Per-Database MAXDOP, CE and Sniffing Config Overrides

calendar Oct 27, 2026 / · 7 min read · sql server sys.database_scoped_configurations sys.configurations maxdop cardinality estimation parameter sniffing query optimizer availability groups plan cache database configuration  ·
Share on: linkedin copy

A database restored from another server, or built by a vendor's install script, can carry its own MAXDOP, cardinality estimation model or parameter sniffing setting, and that setting overrides the server-wide default. The query below lists every database scoped option in the current database, one row per configuration, with the value the primary uses next to the value its secondaries use. Run it once in each database you are checking.

Purpose and Overview

One database starts producing serial plans, or plans built on the old cardinality estimator. Every other database on the instance behaves, and the server-level max degree of parallelism is where it should be. The cause sits one level down. Database scoped options show up only in sys.database_scoped_configurations, as overrides to server-wide default settings, and sys.configurations shows server-wide settings only.

The view exists in SQL Server 2016 (13.x) and later. The query sticks to the 2016 column set. It leaves out is_value_default, which was added in SQL Server 2017, so the same text runs on 2016 and on every later version.

Code Breakdown

The query reads every row of sys.database_scoped_configurations for the current database and carries a commented-out plan-cache flush.

 1-- Get database scoped configuration values for current database (Query 55) (Database-scoped Configurations)
 2SELECT configuration_id, name, [value] AS [value_for_primary], value_for_secondary
 3FROM sys.database_scoped_configurations WITH (NOLOCK) OPTION (RECOMPILE);
 4------
 5
 6-- This lets you see the value of these new properties for the current database
 7
 8-- Clear plan cache for current database
 9-- ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
10
11-- ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
12-- https://bit.ly/2sOH7nb

The column list and the value_for_primary alias

configuration_id is an int ID for the option and name is nvarchar(60). Both value and value_for_secondary are sql_variant. That type lets one column hold a MAXDOP count, a 1 or 0 flag, and the 1,440-minute default of PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES.

The alias [value] AS [value_for_primary] only renames the column. value holds the setting for the primary replica, and the new name puts it side by side with value_for_secondary. That way nobody reads value as the only setting in play.

The number of rows depends on the version. IDENTITY_CACHE applies to SQL Server 2017 (14.x) and later, so a 2016 result does not include it. Some names also changed starting with SQL Server 2019: DISABLE_INTERLEAVED_EXECUTION_TVF became INTERLEAVED_EXECUTION_TVF. If you compare results across instances by matching on name, map renamed options between 2017 and 2019 or they will show up as missing on one side.

WITH (NOLOCK) and OPTION (RECOMPILE)

WITH (NOLOCK) reads the view without waiting on shared locks. OPTION (RECOMPILE) compiles a fresh plan on each run instead of reusing a cached one.

Reading MAXDOP, CE, sniffing and hotfix values against the server default

For MAXDOP, a value of 0 is the default and means the server configuration is used. Any other number overrides the max degree of parallelism set by sp_configure. Two layers sit above the database value: a query hint overrides both the server and the database setting, and Resource Governor can cap all other MAXDOP settings. The limit applies per task, not per request or per query.

Azure SQL Database starts from a different baseline. New single and elastic pool databases start at MAXDOP 8, so on those databases the value to compare against is 8, not 0.

On/off options read as 1 for enabled and 0 for disabled. For the three optimizer options, the default value and the override value map like this:

OptionDefaultOverride reads asEquivalent trace flag
LEGACY_CARDINALITY_ESTIMATION0 (OFF)19481
PARAMETER_SNIFFING1 (ON)04136
QUERY_OPTIMIZER_HOTFIXES0 (OFF)14199

With LEGACY_CARDINALITY_ESTIMATION OFF, the CE model follows the database's compatibility level. A 1 pins the database to the legacy model whatever its compatibility level. PARAMETER_SNIFFING defaults to ON, so a 0 is the override here, the reverse of the other two. QUERY_OPTIMIZER_HOTFIXES OFF disables optimizer hotfixes released after the highest compatibility level available for that version.

The result has no single rule for spotting an override. PARAMETER_SNIFFING defaults to 1, MAXDOP to 0, and PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES to 1,440. Read each row against its own default.

value_for_secondary and NULL

A NULL in value_for_secondary means the secondary is set to PRIMARY and follows the primary's value. PRIMARY is the default setting for the secondaries, so a column full of NULLs is normal. Any value other than NULL was set for the secondaries with FOR SECONDARY. All secondaries use the same configuration, and individual secondaries cannot have different settings.

To see the value a secondary runs with, replace the NULL with the primary's value:

1select *, isnull(value_for_secondary,value) as RealValueForSecondary from sys.database_scoped_configurations;

This ISNULL form turns the column into one you can read directly.

The commented CLEAR PROCEDURE_CACHE line

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE clears the plan cache for the current database. The script leaves it commented out so that running the audit never flushes plans. After a SET change you do not need it, because every ALTER DATABASE SCOPED CONFIGURATION statement already clears that database's procedure cache.

Key Benefits and Use Cases

  • Vendor database intake — a vendor install that switches on legacy CE or switches off sniffing shows up in one query, before anyone opens a ticket about slow plans.
  • Trace flag audit — flags 9481, 4136 and 4199 each have a scoped equivalent, so the result shows which databases already carry the per-database version of a flag.
  • Down-level export check — a DACPAC or BACPAC from a database that uses scoped configurations can't be imported into SQL Server 2014 (12.x) or earlier, so check the database before planning that move.
  • Cross-database query triage — a three-part name query runs under the settings of the connection's current database, except SQL modules compiled in another database, so audit the database the application connects to as well as the one holding the tables.
  • Low-privilege monitoring — reading the view requires only the public role, so a monitoring login can run it without sysadmin.

Performance Considerations

Practical Tips

  • Sweep the instance first — the "Non-Default Database Scoped Config" check in sp_Blitz covers every database, and this query then gives the row-level detail in the databases it flags.
  • Check one option directly — SELECT name, value, value_for_secondary FROM sys.database_scoped_configurations WHERE [name] = 'LEGACY_CARDINALITY_ESTIMATION'; returns the single row when you only need to confirm one setting.
  • Log every change — the ALTER_DATABASE_SCOPED_CONFIGURATION DDL event can fire a DDL trigger that records who changed which option and when.
  • Reset a secondary override — ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY; puts the secondaries back on the primary's value.
  • Grant the narrow permission — changes need ALTER ANY DATABASE SCOPED CONFIGURATION, which a user with CONTROL on the database can grant: GRANT ALTER ANY DATABASE SCOPED CONFIGURATION TO [Joe];.

Conclusion

The query is a first check for per-database overrides that sys.configurations and sp_configure never show. Run it after every restore or migration and before tuning a database that behaves differently from its neighbours. Use the sp_Blitz check when you need an instance-wide sweep.

References

Posts in this series