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
SQL SERVER   Which Settings Change in sp_configure Needs Restart to Take Effect ...

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 )


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

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:SQL SERVER Which Settings Change in sp_configure Needs Restart to Take Effect ...
本站链接:https://www.codesec.net/view/628091.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(186)