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.
sys.configurations (Transact-SQL)
It has a little-detailed output as compared to sp_configure. Here are the interesting columns.
- 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.
- 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.
