Which configuration changes in SQL Server require a restart?

SQL Server is a complex beast, with many configuration options that can range from recommended to completely avoided.

Since the release of SQL Server 2016, several options that were recommended post-install have been rolled into the default installation options and no longer need to be done, and similar changes were made with SQL Server 2017. Even so, there are configuration changes we data professionals need to make after installation, during maintenance windows, and sometimes even during operating hours, so here’s a handy list of changes that do and don’t require a restart of your operating system or SQL Server instance.

Configuration settings that do not require a restart

These are the configuration options you can change in SQL Server without needing to restart the service or the operating system. They will take immediate effect. In some cases, they may flush the plan cache and/or the buffer pool.

Note: If you need to change any of these settings, it is better to script them using Transact-SQL (T-SQL), so that you can script a reversal of the change as well.

• Instance-level settings:
  • Minimum Server Memory (in MB)
  • Maximum Server Memory (in MB)
  • Index creation in memory (in KB)
  • Minimum memory per query (in KB)
  • Processor affinity mask
  • Server authentication
  • Login auditing
  • Default connection options
  • Remote server connections
  • Backup compression
  • Backup checksum
  • Recovery interval
• Database-level settings:
  • Enabling or disabling the Query Store
  • Enabling or disabling Accelerated Database Recovery¹Requires exclusive access to the database
  • Modifying database files and file groups
  • Auto-close database
  • Auto-shrink database
  • Auto create and update statistics
  • Database scoped configurations
  • FILESTREAM settings
  • ANSI settings
  • Snapshot isolation and Read-committed snapshot isolation (RCSI)
  • Delayed durability
  • Parameterization
  • Page verification
  • Service broker
  • Read-only and single-user access
• Non-persisted trace flag options using DBCC TRACEON and DBCC TRACEOFF

Even though the previous settings appear in SQL Server Management Studio as server and database properties, many of them are controlled using sp_configure with 'show advanced options' enabled, while others are SET options. All of these settings can be modified without restarting the SQL Server service, but some may require you to run RECONFIGURE WITH OVERRIDE to override the running value with the configured value.

• Advanced sp_configure options (may require RECONFIGURE WITH OVERRIDE)
  • locks
  • nested triggers
  • default language
  • network packet size (B)
  • default full-text language
  • two digit year cutoff
  • priority boost²Do not adjust this setting
  • remote login timeout (s)
  • cursor threshold
  • max text repl size (B)
  • cost threshold for parallelism
  • max degree of parallelism
  • query wait (s)
  • lightweight pooling³Do not adjust this setting
  • scan for startup procs
  • blocked process threshold (s)
  • filestream access level
  • optimize for ad hoc workloads
  • contained database authentication

Settings that require a SQL Server service restart

Configuration options requiring a restart of the SQL Server service are related to database locations and persisted trace flags:

• Instance-level settings:
  • Database default locations
  • Instant file initialization
• Moving the physical location of system databases⁴You can add and remove database files without a service restart
  • master
  • msdb
  • tempdb
• Persisted trace flags using SQL Server Configuration Manager or mssql-conf on Linux⁵Trace flags can be modified at the instance level without requiring a service restart, but for them to be a permanent change, they must be set in Configuration Manager or mssql-conf

Settings that require an operating system restart

These are the configuration options that require a restart of the operating system:

• Modifying power saving settings in the BIOS

Summary

These lists are not exhaustive. Feel free to ask about a setting in the comments and I’ll update this page.

Photo by Caspar Camille Rubin on Unsplash.