SQL SERVER – Which Settings Change in sp_configure Needs Restart to Take Effect?

Learning one new thing every day keeps me passionate about my job. Recently, I had a very interesting experience with one of my customer while working with them on Comprehensive Database Performance Health Check and found some non-default values in sp_configure.  They asked me – Which settings change in sp_configure needs restart to take effect? Do we need to remember the values?

THE QUESTION

What is an easy way to find out which settings in sp_configure which can be changed without recycling SQL Service?

THE ANSWER

Starting SQL Server 2008, Microsoft has introduced a new catalog view which can be used to see various server-wide configuration option value in the system.

Solarwinds

sys.configurations (Transact-SQL)

It has a little-detailed output as compared to sp_configure. Here are the interesting columns.

  1. Is_dynamic: This column is used to know if the option is dynamic or not. If the value is 1 (one) then the parameter change takes effect when the RECONFIGURE statement is executed. If the value is 0 (zero) the value takes effect when the SQL Server service is restarted.
  2. Is_advanced: This column is used to know if the option is an advanced option or not. If the value for a parameter is 1 then it’s an advanced option and would is displayed or can be changed only when “show advanced options” is set to 1 through sp_configure.

Below is the query which given an answer to our question!

-- these configuration values which need restart
SELECT name ,description
FROM sys.configurations
WHERE is_dynamic = 0

SQL SERVER - Which Settings Change in sp_configure Needs Restart to Take Effect? sp-configue-restart-01

Here is the list as of today in SQL Server 2017 (build 14.0.3045)

  • user connections
  • locks
  • open objects
  • fill factor (%)
  • remote access
  • c2 audit mode
  • priority boost
  • set working set size
  • lightweight pooling
  • scan for startup procs
  • affinity I/O mask
  • affinity64 I/O mask
  • common criteria compliance enabled
  • automatic soft-NUMA disabled
  • external scripts enabled
  • hadoop connectivity
  • polybase network encryption

Please let me know if you know some other tricks. I would be more than happy to publish on my blog with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Installation Failure – Specified Instance Via Transform is Already Installed. MSINEWINSTANCE Requires a New Instance that is not Installed
Next Post
SQL SERVER – FIX: Msg 41105: Failed to Create the Windows Server Failover Clustering (WSFC) Resource With Name and Type ‘SQL Server Availability Group’

Related Posts

Leave a Reply

Menu