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.

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

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.