Which Configuration Changes in SQL Server Require a Restart?
A great cheatsheet to save you some time.
Join the DZone community and get the full member experience.Join For Free
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
- 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
- Page verification
- Service broker
- Read-only and single-user access
- Non-persisted trace flag options using
Even though the previous settings appear in SQL Server Management Studio as server and database properties, many of them are controlled using
'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.
sp_configureoptions (may require
RECONFIGURE WITH OVERRIDE)
network packet size (B)
default full-text language
two digit year cutoff
remote login timeout (s)
max text repl size (B)
cost threshold for parallelism
max degree of parallelism
query wait (s)
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
- Persisted trace flags using SQL Server Configuration Manager or
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
These lists are not exhaustive.
Published at DZone with permission of Randolph West, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.