DBAtlas · Database operations atlas

General sp_configure setting and RECONFIGURE

SQL ServerConfiguration

Command

EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Example: cost threshold for parallelism and MAXDOP:
EXEC sys.sp_configure 'cost threshold for parallelism', 50;
EXEC sys.sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

-- Show all configured/running values:
SELECT name, value AS configured, value_in_use AS running, is_dynamic, is_advanced
FROM   sys.configurations
ORDER BY name;

What it does

Sets server-level configuration values; RECONFIGURE applies them to the running value.

When to use it

When changing server settings such as MAXDOP, the parallelism threshold, or optimize for ad hoc workloads.

Caution

Settings with is_dynamic = 0 require a SQL Server restart. RECONFIGURE WITH OVERRIDE skips validation for some settings; use it carefully. Note value_in_use before changing anything.

Open in DBAtlas →

Related